improving performance of union query

[JoeA2006]

I need to access data from several large tables approx 1 million records each.
There is a table for each month in a separate database over 300mb each. I
need to create a front end by linking to these tables and creating a union
query to allow users to access the data from all months. Each table is
indexed, but the union query takes a very long time. Is there any way to
improve the performance of this query?

 

[Jerry Whittle]

Instead of UNION use UNION ALL.

Note: UNION ALL allows duplicates whereas UNION takes the time to check for
and exclude dupes before showing the data. Therefore if dupes are a problem,
UNION ALL might not be the best solution.

The next problem is that Access just might not be able to handle all those
records. You might need to upgrade to SQL Server or Oracle to get it to work
at alluch less fast enough.