Re: Simple query executes fast but renders slow...

From: Chris2 (indanthrene.NOTVALID_at_GETRIDOF.yahoo.com)
Date: 04/02/04 

Date: Fri, 02 Apr 2004 03:42:39 GMT

"BradP" <anonymous@discussions.microsoft.com> wrote in message
news:170fd01c41842$c7f35e20$a301280a@phx.gbl...
> 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.

  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 data*** view takes at least one minute
> and quite often longer than that.

  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 data***. 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

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

> spanning of platforms. I would appreciate any
> suggestions for what I can do to address this issue.
>
> Thanks, Brad

Loading