very, very slow query

Posted on 2003-03-10
Medium Priority
Last Modified: 2010-07-27
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.
Question by:iloveunix
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

malekam earned 375 total points
ID: 8107045
You might try:

docmd.runsql "UPDATE [newdata] SET import_flag = 'IMPORT ERROR - DNC MATCH' WHERE EXISTS (SELECT * FROM [main_contact] WHERE [newdata].phone = [main_contact].phone)"

This should be faster.
LVL 41

Expert Comment

ID: 8107362
Change it to a join:

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 ?

Author Comment

ID: 8107396
Ahh yes the EXISTS keyword. Completely forgot about that one. Works great now. Thanks so much.

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question