UNION queries .... why so slow?

[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?

 

[Pieter Wijnen]

try UNION ALL (with Where statements to avoid dups if any)

Pieter

 

[John W. Vinson]

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?

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]

 

[M Stanley]

Made a world of difference. Thanks.


"Pieter Wijnen"

 

[M Stanley]

Made a world of difference. Thanks.

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]