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.

 

[John]

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.

Well the number of records is one of the obvious reasons for the
slowness. It does sort of explain why they're separated into different
Access files, but not quite. I'm not sure what the size limit is on
access files, or if there even is one (I know there was, in previous
versions of Windows/Access, but I'm not sure if it's still there).
Anyway, I have a couple of questions:

1) If there is no limit on the size of the file, would it be better to
simply store all those millions of records in one table? I'm not sure
exactly what the records/tables are used for, so I can't really answer
this question. If each of the 10 files contains a certain subset of
records that pertain to 10 different groups, and each of these groups
does not need the other 9 files, then it makes sense to have them
separated because it would improve the performance for each of the groups.

2) Why are there so many duplicates in the first place? Would it be a
better idea to refine whatever process adds the records to those files
so that the duplicates simply aren't there? That would save lots of
drive space, improve performance drastically, remove the complexity, and
save you the trouble of having to do what you're trying to do now.

When you get into dealing with record sets of the size you're talking
about, Access just won't cut it. I have a table that has about 5.2
million records in it, and most operations that I've tried to perform on
it were too slow for my liking. About the only thing that runs
relatively quickly is a lookup of one record, specifying the primary key
since that's the field that's indexed.

 

[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).