Append Querry works OK in DesignView, but TOO slow when I press "Run"

Hi there, I have a local table that I use to store production reports, and then copy it's contents to the server. I do such a thing with many other tables, but I am getting specificaly a problem of speed with one table. It has only about 70,000 records, we add about 40 dialy.
When I open de Querry in DesignView and change to DesignView, it runs fast, it takes about 10 seconds to display the data that will be appended. BUT when I press the "Run" botton, it takes about 10 minutes to complete...
Any hints?

Here is the Querry's Code:

INSERT INTO tblFTEMNFCorte ( Id, IdMaquinaCorte, IdOperador, OT, [Unidades Completas], [Piezas x Unidad], Unidad, [Piezas Sobrantes], [Piezas Desperdicio], Observaciones, TipoDeEtiqueta, FechaInicio, FechaFinal, [Pzas Cortadas], [Metros Bobinados], CYDUnique, Turno ) IN '\\Server\datamanager\Seraph 2\Fuente.mdb'
SELECT tblMatrixReportesCYD.Id, tblMatrixReportesCYD.IdMaquinaCorte, tblMatrixReportesCYD.IdOperador, tblMatrixReportesCYD.OT, tblMatrixReportesCYD.[Unidades Completas], tblMatrixReportesCYD.[Piezas x Unidad], tblMatrixReportesCYD.Unidad, tblMatrixReportesCYD.[Piezas Sobrantes], tblMatrixReportesCYD.[Piezas Desperdicio], tblMatrixReportesCYD.Observaciones, tblMatrixReportesCYD.TipoDeEtiqueta, tblMatrixReportesCYD.FechaInicio, tblMatrixReportesCYD.FechaFinal, tblMatrixReportesCYD.[Pzas Cortadas], tblMatrixReportesCYD.[Metros Bobinados], tblMatrixReportesCYD.CYDUnique, tblMatrixReportesCYD.Turno
FROM tblMatrixReportesCYD LEFT JOIN tblFTEMNFCorte ON tblMatrixReportesCYD.CYDUnique = tblFTEMNFCorte.CYDUnique
WHERE (((tblFTEMNFCorte.CYDUnique) Is Null));
VellenawethAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
1. have you tried attaching the table and removing the IN clause?

2. I think your joining expression and record selection conditions are the cause of your performance problem.  You are joining on the CYDUnique fields, but only when the CYDUnique is Null.  Joining on Null values is not generally a good performer.
FROM tblMatrixReportesCYD LEFT JOIN tblFTEMNFCorte ON tblMatrixReportesCYD.CYDUnique = tblFTEMNFCorte.CYDUnique
WHERE (((tblFTEMNFCorte.CYDUnique) Is Null));

Open in new window

3. Are there Indexes on the CYDUnique fields in these two tables?
0
Jeffrey CoachmanMIS LiasonCommented:
My guess is that what aikimark posted should set you on the path to clear up this issue
But a few follow ups...

<When I open de Querry in DesignView and change to DesignView, it runs fast,>

I will presume this was a spelling error, and you really meant:
<When I open de Querry in DesignView and change to DatasheetView, it runs fast,>

With an "Action" query, changing to datasheetview will only show you what will happen.
It will not "run" (actually perform the append operation)

So actually "running" the query, will almost always be slower.


As far as the slowness is concerned...
How far (in terms of the Path) is the source and the destination?
 C:\YourFolder\YourDatabase.mdb, and C:\Yourfolder\updates\Yourstuff.mdb
...are not that far apart.

On the other hand files like this:
C:\Data\Access\2013\Reference\YourDatabase.mdb, and H:\Logs\Backup\Updates\2013\User1\target57.mdb, ...are quite far apart as far as Paths are concerned

Finally remember that speed is relative, sometimes 10 seconds is what it will take...
Consider other things like the time it takes to run a similar append query, or network performance...
0
VellenawethAuthor Commented:
Hi aikimark:
1)No

2)I'm adding the Null, because I want to add ONLY the registers that are not contained in the destiny table.

3)Yes, both CYDUnique fields are Indexed.

I'll try the first one thought, tell you later what happened.
Peace.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

aikimarkCommented:
The simpler solution is to put a unique index on the target table.  Insert all the rows.  The unique index will prevent duplicates.

2 (addendum). I suspect the joining criteria in your FROM clause is incorrect.  You shouldn't be joining by the CYDUnique field.  Alternatively, you should be doing a left/right (outer) join.
0
VellenawethAuthor Commented:
Hi there...... I tried the suggestions above that were not implemented. The result was the same. BUT.... I deleted the target table from the Database, and left a copy of the table with just the definitiions... Now it seems to work just fine and Fast.

It has happened to me sometimes some Querrys Work fine for a while, and usddenly they begin to get sloppy. I copy the SQL code into a new querry, delete the old one, and it works back again.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VellenawethAuthor Commented:
It worked! Although, not sure why
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.