UNION queries .... why so slow?

  • Thread starter Thread starter M Stanley
  • Start date Start date
M

M Stanley

I have 2 tables with 24 fields each. Each table has approx 8,000 - 10,000
records

Plain select queries open almost immediately. UNION queries take FOREVER.

Is there something I can do to optimize UNION queries?
 
I have 2 tables with 24 fields each. Each table has approx 8,000 - 10,000
records

Plain select queries open almost immediately. UNION queries take FOREVER.

Is there something I can do to optimize UNION queries?
Click to expand...

Access will need to retrieve all 16-20,000 records, sort them, and then remove
the duplicates. This can indeed be very slow, especially if your tables aren't
sufficiently indexed.

If you don't expect to have (or don't care about) duplicate records, you can
use UNION ALL - it will skip the expensive de-duplication step.

John W. Vinson [MVP]
 
Made a world of difference. Thanks.


John W. Vinson said:
Access will need to retrieve all 16-20,000 records, sort them, and then
remove
the duplicates. This can indeed be very slow, especially if your tables
aren't
sufficiently indexed.

If you don't expect to have (or don't care about) duplicate records, you
can
use UNION ALL - it will skip the expensive de-duplication step.

John W. Vinson [MVP]
Click to expand...
 
Back
Top