Simple query executes fast but renders slow...

  • Thread starter Thread starter BradP
  • Start date Start date
B

BradP

I've got a simple two table join where the left table is
an access table joining to an IBM mainframe DB2 table via
a DB2CONNECT DSN, with the joined column being indexed on
both tables.

For a 1000 record resultset the query takes less than 10
seconds to run, but the rendering of the results (4
columns) in the datasheet view takes at least one minute
and quite often longer than that.

If I do joins between access tables only, performance is
fine, so I know my issue has something to do with the
spanning of platforms. I would appreciate any
suggestions for what I can do to address this issue.

Thanks, Brad
 
BradP said:
I've got a simple two table join where the left table is
an access table joining to an IBM mainframe DB2 table via
a DB2CONNECT DSN, with the joined column being indexed on
both tables.
Click to expand...

DSN? So, ODBC, right?

Unless something has changed recently, no database engine can perform
meaningful optimization (including using indexes) when one table is on the
other side of an ODBC connection. The database engine, I believe, assumes a
worst case scenario and does a full table scan of both tables (and across
the link that's a huge slow down).
For a 1000 record resultset the query takes less than 10
seconds to run, but the rendering of the results (4
columns) in the datasheet view takes at least one minute
and quite often longer than that.
Click to expand...

What version of Access are you running? I'm asking because Access 2000
and 97 have never bothered to tell me when they are done executing the
"Query" and begun working on rendering the Query's datasheet. If this is a
feature of a later version of Access, or a third party product, or, maybe,
I've missed a feature of Access over all these years, or even just something
everyone else has always known about and it's just poor me out in left field
wandering around with a dazed look on my face, please let me know.
If I do joins between access tables only, performance is
fine, so I know my issue has something to do with the
Click to expand...

Yup, the ODBC link can impose heavy performance penalties.

I've spent a lot of time on a PC running Access Queries against a DB2
database on an AS/400 (10 months). It's terribly slow (and that was over a
Base-100 Ethernet network to each PC, and a Base-1000 Ethernet line from the
switch to the AS/400. The computers and the network weren't the problem.
JET is up against a wall as far as ODBC is concerned, it just can't figure
out to use it's best data access methods because it has no information about
the DB2 Tables. Again, I believe it _always_ resorts to Table Scans for
Queries across ODBC. I even tried writing Pass-Through Queries, but it
turned out DB2 was in the same situation as JET, unable to optimize due to
the ODBC link (in fact, I think all database engines are in the same boat as
far as optimization and ODBD are concerned).
 
Excellent answer.
I experienced all of these issues many years ago.
You are right on.

My workaround was to create local versions of the tables in Access and take
the "hit" once to download a chunk of the data to them.

Then I wrote queries and reports based on the local versions. They were
bazing fast.
The accountants who need to review monthly data and run 20-30 reports were
VERY happy.
They only had to wait 20 minutes to downlaod the monthly data and then run
the reports as fast as they wanted.
(In the original design it was obviously 20 minutes per report! THAT didn't
last long.)
 
I'm not totally buying into this for a couple of
reasons. (We're on Access 2002 on Windows XP and I have
the latest Jet engine and data access components loaded).

First, if my access table has only one record, the
completion of the query is almost instantaneous, whether
I use a low index value or high index value, match or no
match. If a TS scan were being used, I would expect a
similar response between 1 and 1000 records, or certainly
slower than what I'm getting, since the DB2 table has
750,000 rows. Unless 1000 records meant 1000 separate
tablespace scans, but I would think there would be enough
smarts to know that only a single pass is required.

For how I got my timings, I run a macro that has queries
on the front and back that mark the beginning, end and
elapsed times, with my real query in the middle.

For 1000 rows, the macro always finishes within 10
seconds when the real query is not a make table query,
but takes over a minute when it is a make table query.
For the first scenario, the datasheet from the real query
still takes over a minute before you can see the 1000th
returned row. I'm assuming that for the macro to
continue past the real query, it must consider the result
set to be complete at some level, but maybe someone can
explain otherwise. I'm thinking more along the lines of
buffering limits, but I'm not sure where to check.

Thanks, Brad
 
PMJI.

If you're joining 1000 rows in an Access table with 750,000 rows linked via
ODBC, I believe Access is sending 1000 separate queries to the server - one
for each row in Access.

If you want to see what's really happening, go set this Registry key to 1:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\TraceSQLMode

If the TraceSQLMode key doesn't exist, create a new one as a DWord and set
it to 1. Set it back to 0 when you're done testing.

After you restart Access and run your query, JET will write an extensive log
to a file called SQLOUT.txt in the current directory.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thanks for the tip. I don't think I'll do the trace this
time, but I'm sure I'll try it in the future. I've
already started working on the other folk's suggestions,
where I've downloaded the 300,000 records that I need
from the DB2 table into an Access table. Stored
procedures may be an even better solution in the long
run, but I'm a lot better off than I was a day ago.
 
Back
Top