Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1003
  • Last Modified:

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.JobNum
FROM tblFAICharacteristicNums
WHERE (((tblFAICharacteristicNums.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!
0
SeyerIT
Asked:
SeyerIT
  • 5
  • 3
2 Solutions
 
masteripperCommented:
You should use a join instead of the IN Operator if u need speed.
0
 
SeyerITAuthor Commented:
Yeh, that's how I did the Append query but I got an error when I tried to do it in the delete query.. Here is the error:
error
0
 
masteripperCommented:
The SELECT DISTINCTROW should do the job
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SeyerITAuthor Commented:
I'm not familiar with that.. Could you expand on how to accomplish that?
0
 
masteripperCommented:
DELETE DistinctRow
Take a look also here
http://eis.bris.ac.uk/~ccmjs/access2000_distinct.htm
0
 
masteripperCommented:
Just make the Query in the Editor and then switch to SQL and after DELETE use DISTINCTROW
e.g "DELETE tblFAICharacteristicNums.*, tblFAICharacteristicNums.JobNum
FROM tblFAICharacteristicNums INNER JOIN ...."

then
"DELETE DISTINCTROW tblFAICharacteristicNums.*, tblFAICharacteristicNums.JobNum
FROM tblFAICharacteristicNums INNER JOIN ....."
0
 
masteripperCommented:
0
 
Rey Obrero (Capricorn1)Commented:
using the query "qryMoveCrit" might be the one slowinf down your delete query.

since you already have added to the archived table the records you want to delete,

try using this

DELETE tblFAICharacteristicNums.*, tblFAICharacteristicNums.JobNum
FROM tblFAICharacteristicNums
WHERE (((tblFAICharacteristicNums.JobNum) In (Select jpJobNum FROM [ARCHIVEDTABLENAME])))

post back the result..
0
 
SeyerITAuthor Commented:
Thanks folks! This got the job done!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now