Jeremy Campbell
asked on
Trying to optimize / speed up a delete query in Access.
I'm working on some append/delete queries that are moving sets of information from one table and putting it into an archive. The information it moves depends on the criteria from another query. I managed to get the append query set up and it runs really quick. Now I just need to delete the specifed data from my live table.
Here is my current delete query that runs WAY TOO slow;
DELETE tblFAICharacteristicNums.* , tblFAICharacteristicNums.J obNum
FROM tblFAICharacteristicNums
WHERE (((tblFAICharacteristicNum s.JobNum) In (Select jpJobNum FROM [qryMoveCrit])));
Let me know if you have any ideas as to how I can speed this up.
Thank in advance!
Here is my current delete query that runs WAY TOO slow;
DELETE tblFAICharacteristicNums.*
FROM tblFAICharacteristicNums
WHERE (((tblFAICharacteristicNum
Let me know if you have any ideas as to how I can speed this up.
Thank in advance!
You should use a join instead of the IN Operator if u need speed.
ASKER
The SELECT DISTINCTROW should do the job
ASKER
I'm not familiar with that.. Could you expand on how to accomplish that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DISTINCTROW takes care of Non-Key Field joins
http://social.msdn.microsoft.com/Forums/da-DK/transactsql/thread/cdc628d7-1e65-4c23-b61c-4d1bd325178a
http://social.msdn.microsoft.com/Forums/da-DK/transactsql/thread/cdc628d7-1e65-4c23-b61c-4d1bd325178a
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks folks! This got the job done!