Queries take FOREVER to run - ???

[Bonnie A]

Hi there. Using Access 01 on XP. A coworker sent me a question and other
than saying she needs to index her tables I'm not sure what to advise. Can
someone help?

Her inquiry reads:

I have an Access database hitting DB2 tables via an ODBC connection. These
are very large tables. Some of my queries, let alone the Macros that run one
query after another to complete a new data pull take OVER AN HOUR. This is
ridiculous! Other users have just let if run and hope that the machine
doesn't lockup to the point of no return.

Anyway I have built my machine and it's a Dell optiplex 745 w/ DUAL CORE
processor and 2GB of RAM. Still these queries take forever to run. Do you
have any advice or anything I could check?
##############################

Thanks in advance for any assistance!

 

[Bob Barrows]

Hi there. Using Access 01 on XP. A coworker sent me a question and
other than saying she needs to index her tables I'm not sure what to
advise. Can someone help?

Her inquiry reads:

I have an Access database hitting DB2 tables via an ODBC connection.
These are very large tables. Some of my queries, let alone the Macros
that run one query after another to complete a new data pull take
OVER AN HOUR. This is ridiculous! Other users have just let if run
and hope that the machine doesn't lockup to the point of no return.

Anyway I have built my machine and it's a Dell optiplex 745 w/ DUAL
CORE processor and 2GB of RAM. Still these queries take forever to
run. Do you have any advice or anything I could check?
##############################

"Access 01"?

I've never used DB2, but if SQL400 is indicative, DB2 is very sensitive
to the order of the criteria used in the WHERE clause. If the tables are
indexed, the criteria must be in the order in which the fields appear in
the index in order for the index to be used. Otherwise a table scan will
be done.

If at all possible, passthrough queries should be used - avoid joining
DB2 tables to local Access tables.

As to whether or not an hour is ridiculous, we have no way to judge the
accuracy of that statement. How much data is being "pulled"?

 

[km]

Thanks Bonnie & Bob,

This particular query is a Select Query to post results from the data pull.
The amount of data pulled when exported to an Excel table is 133KB (not
particularly large). I can not view the DB2 table properties to determine
the size. But this query does involve local tables that are "linked" by the
key element in the DB2 table.

Within the query is 22 local tables liked by a value "Reg Code" . The "Reg
Code" is the only valued pulled from the DB2 table. There is no "Where"
statement, just a 1 value linking the tables. The Joint Property is "All
records from the local table and only the 1 Record from the DB2 table
containing the corresponding link.

Currently I'm running a Query Analyzer utility and it's been running for
about 25 minutes.

I did not design this database, it was inherited. But I can do whatever I
want with my copy stored on the network.

 

[Bob Barrows]

Thanks Bonnie & Bob,

This particular query is a Select Query to post results from the data
pull. The amount of data pulled when exported to an Excel table is
133KB (not particularly large). I can not view the DB2 table
properties to determine the size. But this query does involve local
tables that are "linked" by the key element in the DB2 table.

Within the query is 22 local tables liked by a value "Reg Code" . The
"Reg Code" is the only valued pulled from the DB2 table. There is no
"Where" statement, just a 1 value linking the tables.

Well, strictly speaking, since Joins are involved, a WHERE clause is
implicitly involved.
The problem is, in order for the joins to be performed, ALL the data
from the DB2 table needs to be retrieved. Given a large amount of data
in that table, this is probably not recommended.

Bottom line: there is little you can do within Access to improve
performance if joins between linked and local tables must be used. I'm
not really sure where the bottleneck is so it is difficult to make
recommendations. Are you using the Reg Code from DB2 to limit the
records retrieved from the local tables? If so, this implies that there
are fewer records in DB2 than there are in the Access tables. Given
that, you might consider importing the DB2 data into an indexed local
table periodically so that all the joins are local.