iloveunix
asked on
very, very slow query
Hello all:
Here's the deal. I have an app I made with VBA in Access 97. I recently wrote an import utility for admins to import new marketing data lists into the contact tables. Before doing this the list must be scrubbed against "do not call" and "do not mail" lists. The query I wrote for this is quite simple and ridiculously SLOW when importing large (20,000+) lists (DNC list is about 8,000 records). It goes like this:
docmd.runsql "UPDATE [newdata] set import_flag = 'IMPORT ERROR - DNC MATCH' WHERE phone IN (SELECT phone FROM [main_contact])"
works fine but incredibly slow. It took about 35 minutes to crunch a 20,000 record list.
How can I speed this up?
Would using recordsets be faster?
Any suggestions appreciated.
Here's the deal. I have an app I made with VBA in Access 97. I recently wrote an import utility for admins to import new marketing data lists into the contact tables. Before doing this the list must be scrubbed against "do not call" and "do not mail" lists. The query I wrote for this is quite simple and ridiculously SLOW when importing large (20,000+) lists (DNC list is about 8,000 records). It goes like this:
docmd.runsql "UPDATE [newdata] set import_flag = 'IMPORT ERROR - DNC MATCH' WHERE phone IN (SELECT phone FROM [main_contact])"
works fine but incredibly slow. It took about 35 minutes to crunch a 20,000 record list.
How can I speed this up?
Would using recordsets be faster?
Any suggestions appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ahh yes the EXISTS keyword. Completely forgot about that one. Works great now. Thanks so much.
DoCmd.RunSQL "UPDATE [newdata] INNER JOIN [main_contact] ON [newdata].[phone] = [main_contact].[phone] SET [newdata].[import_flag] = 'IMPORT ERROR - DNC MATCH'"
The IN clause is very, very, very, very slow. Did I mention it was very slow ?