Query very slow with UNION

[Alfred Malleier]

Hallo,

my Access-DB grows over 1.5 GB and therefore I need to archive parts of my
'DocD' (detail-records) in an external DB. But there are situations in my
program where I need to query the entiry table-data. To solve this I create
a query:

Dim qd As QueryDef
Set qd = WinCo.CreateQueryDef("DocX", "TABLE DocD UNION ALL TABLE DocDArc")

So the query 'DocX' contains both tables 'DocD' and 'DocDArc' united by the
UNION-statement.
When I make a query on 'DocX' I have the same result as before when all data
was in one table. But the problem is, that the Query 'DocX' has no indexes
and runs very slowly.

I tried to go around on using the JOIN-Statement, but then in the resultset
I have 'DocD.*' and 'DocDArc.*' fields, abolutely not compatible with the
existing code.

HOW can I solve the problem with speed?

many thanks

Alfred Malleier

 

[John Spencer (MVP)]

The only way I know to try would be to use UNION ALL and apply the criteria to
each section of the UNION query. I BELIEVE that would speed up the
selection/identification of the records (use the indexes), but I GUESS that
would not do anything for speeding up the sorting of the records (no index
available on the set of union-ed records).

SELECT *
FROM DocD
WHERE ....
UNION ALL
SELECT *
FROM DocDArc
WHERE ...

 

[Michel Walsh]

Hi,


Another alternative for some kind of problems is to create a temporary
table with a single "merged field" (not all the field, but just the
"important" field) with the a UNION (rather than a UNION ALL). Since the
data is now in a table, once done, add an index. Next, use that temp table
inside a join, and pump the other fields through outer join from this temp
table and the DocD and DocDArc tables.


SELECT ..., Nz( DocD.f1, DocDArc.f1) , ...
FROM ( temp LEFT JOIN DocD ON ... ) LEFT JOIN DocDArc ON ...


Hoping it may help,
Vanderghast, Access MVP