Vellenaweth
asked on
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\Sera
SELECT tblMatrixReportesCYD.Id, tblMatrixReportesCYD.IdMaq
FROM tblMatrixReportesCYD LEFT JOIN tblFTEMNFCorte ON tblMatrixReportesCYD.CYDUn
WHERE (((tblFTEMNFCorte.CYDUniqu
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\Sera
SELECT tblMatrixReportesCYD.Id, tblMatrixReportesCYD.IdMaq
FROM tblMatrixReportesCYD LEFT JOIN tblFTEMNFCorte ON tblMatrixReportesCYD.CYDUn
WHERE (((tblFTEMNFCorte.CYDUniqu
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
...are not that far apart.
On the other hand files like this:
C:\Data\Access\2013\Refere
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...
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
...are not that far apart.
On the other hand files like this:
C:\Data\Access\2013\Refere
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...
ASKER
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It worked! Although, not sure why
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.
Open in new window
3. Are there Indexes on the CYDUnique fields in these two tables?