Make a tbl qry (union) running very very slow

[sahafi]

I have a union query (out of 10 Access databases .. linked) that I'm using as
a source to make a table so I can link that new table to another Access dB.
My union query has only 2 fields and less than 2,000 records, but it takes
about an hour to make a table. I have tried to run an 'append' qry after
creating a table with those two fields, but that one takes even longer!! Is
that how it supposed to run? I could run an append query from one table to
another with over a million records and takes about a minute or so. Why the
union queries run so slow, or is there a better way of handling this?

Thanks.

 

[John W. Vinson]

I have a union query (out of 10 Access databases .. linked) that I'm using as
a source to make a table so I can link that new table to another Access dB.
My union query has only 2 fields and less than 2,000 records, but it takes
about an hour to make a table. I have tried to run an 'append' qry after
creating a table with those two fields, but that one takes even longer!! Is
that how it supposed to run? I could run an append query from one table to
another with over a million records and takes about a minute or so. Why the
union queries run so slow, or is there a better way of handling this?

Thanks.

It's got to open ten *different database files*, generate a recordset
comprising records from all ten, and eliminate all the duplicates... it's
going to take a while!

If you don't expect duplicates, or can tolerate them (e.g. you could have a
unique index in the target table and eliminate them that way), use UNION ALL
instead of UNION; it's going to be faster. But with ten linked .mdb files it
will never be lightning quick. I hope this is a "one shot" rather than a
routine operation!

 

[sahafi]

Thanks John.
It's a once/month operation for now, but it might change into once a wk ops.
I have tried to use the pass thru query to query the data direct into Oracle
dB, but I keep getting this error: ORA-00911:invalid character(#911). I think
it didn't like my SQL syntax, but the same statement runs fine on the
'middle' tool that I use.

Yes there are lots of duplicates. The 10 tables consist of approximately
25-30 millions records, and my Union query is pulling only 1926 records
(uniques), so using the 'UNION ALL' might choke the system! I'm open to any
idea to help move this process more efficiently. I have been trying to
convice my mgr to get us a SQL Server DBMS so we can pull all the data into
one table (it's not going to happen... at least not now). Each dB file
contains only one tble (about 1.5 GB). I'm using another dB to link all
tables, run Union queries, make tables, then link these new tables to other
databases to run our business!!

Thanks for any help.

 

[sahafi]

Thanks John.
Actually i'm using Access 2002 which has a limit of 2GB per .mdb file. As I
stated previously, each file is about 1.5 GB so I can't consolidate the
tables into one file.
All tables have the same structure and the same exact fields name/type(35
fields), and each table is about 3 million records long.
It will be great to have all the data into one tbl, but Access can't handle
that.
About the duplicates: this is a manufacturing data, so there are lots of
details... duplicates due to time period (weekly data), location (hundreds of
them), product category (many of them), sub category (many many of them),
product codes (thousands of them), etc you get the picture. You need to have
the most granular details in order to model demand/utilization by region, etc.
The idea is to run this process every month to capture unique prod
categories then run an unmatched query agains that data and the data
currently in our modeling tool, then add any new location/product
category/code to the modeling tool (in a nut shell). We have to show the data
on a weekly basis in order to drop any location that didn't receive any order
for a certain number of weeks.
Yes, it's a very tedious process. I'm sure there might be a better way
(other than using SQL Server).