Extremely slow "Union" Query

E

EagleOne

2003

Using a union on a calculated field (within the union query itself) to compare two tables for
unmatched data:

FROM Table2 LEFT JOIN Table1 ON Table#2.CONCACT=Table#1.CONCACT


Table1 Table2

A1 B2 C3 D4 E5 Concact A1 B2 C3 D4 E5 Concact
En ti re Wo rd EntireWord En ti re Wo rd EntireWord

Therefore, there is a match in record #1 for Table1 vs Table2 in Field "Concact".

The challenge is that calculating the Query-generated datasheet takes 20 munites for 8,000
records..

I tried sorting the Concact fields in both tables to no seeming time-advantage.

Is there a way to increase time-efficiency?

TIA EagleOne
 
A

Arvin Meyer [MVP]

2003
Click to expand...
The challenge is that calculating the Query-generated datasheet takes 20
munites for 8,000
records..

I tried sorting the Concact fields in both tables to no seeming
time-advantage.

Is there a way to increase time-efficiency?
Click to expand...

Indexes will speed your queries. I import over 300,000 dBase records, into
an Access table and run 37 queries to massage the data, and it takes from 28
to 40 seconds depending upon the RAM and processing speed of the computer.
There are 2 union queries, each of which has 6 query elements in the 37.
Once the records are imported for the day, running 9 queries, including the
same 2 union queries, takes 2 to 3 seconds.
 
J

Jeff C

Great suggestion Arvin!

Is there a good resource on indexing or should you just go through your
tables and index the fields with joins? Is there a way to index when you are
working with Linked Data?

Thanks in advance
 
E

EagleOne@microsoftdiscussiongroups

My original goal was to decrease the time Access takes to match records in
two tables. This matching was done in Excel until out record sizes began to
exceed 65536.

In a perfect Access world, I would attempt to use unique fields to generate
indexes which would be considered unique - but I have no control over the
data.

Table1 data and Table2 come from different super-mainframe systems. At the
EOD, both sets of data s/b the same but it is not because, 75% of the data is
OP entered manually in each system.

I have the VBA (SQL) code to create and to remove an index.

What I need is the VBA (SQL) code in a Join query to Open table1 with its
index "indSLOA" and match to Open table2 with its index "indCLOA". I "think"
what I want to do is match on the CONCACT fields (which I guess I must create
in each Table via an update query as I cannot apply an index to query
results) one each in Table1 and Table2.

I am very concerned that without a unique field that a loose-index may gain
no advantage of time savings?

What am I not considering and/or is there a better way?
 
A

Arvin Meyer [MVP]

Jeff C said:
Great suggestion Arvin!

Is there a good resource on indexing or should you just go through your
tables and index the fields with joins? Is there a way to index when you
are
working with Linked Data?
Click to expand...

The tables themselves must be indexed in the back-end. You cannot index
linked tables in the front-end.
 
A

Arvin Meyer [MVP]

"EagleOne@microsoftdiscussiongroups"
I am very concerned that without a unique field that a loose-index may
gain
no advantage of time savings?

What am I not considering and/or is there a better way?
Click to expand...

Unique indexes are of prime importance when specific records are being
requested.
With lots of records, there are 2 main indexing considerations:
1. All fields using joins must be indexed, uniqueness has nothing to do
with it.
2. Indexes are best applied in the query in the same order they are in
the tables.

So, for example, if the tables are indexed LastName and FirstName, do not
run the query FirstName, LastName. That"s an oversimplification, but the SQL
engine takes more time to match or sort values if the data is called in
random order.
 
P

Pieter Wijnen

Not entirely true, you can make "local" indexes by using ddl
ie CREATE INDEX MyIdx ON MyTable (MyFieldlist)
very usefull if you use Oracle & the Primary key is a Function based index

Pieter
 
E

EagleOne

Arvin, Thanks


Arvin Meyer said:
"EagleOne@microsoftdiscussiongroups"


Unique indexes are of prime importance when specific records are being
requested.
With lots of records, there are 2 main indexing considerations:
1. All fields using joins must be indexed, uniqueness has nothing to do
with it.
2. Indexes are best applied in the query in the same order they are in
the tables.

So, for example, if the tables are indexed LastName and FirstName, do not
run the query FirstName, LastName. That"s an oversimplification, but the SQL
engine takes more time to match or sort values if the data is called in
random order.
Click to expand...
 
D

david

Are you using UNION ALL?
That runs much faster than UNION, which sorts before
checking for duplicates. Since you are looking for unmatched
data, duplicates don't matter, it's just a question of if the
sort is slower than the match, or the match is slower than
the sort

(david)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union Query by choice.. 11
Unique data from Union Query 1
Union Query 4
UNION Query 1

Top