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

From: Joe Fallon (jfallon1_at_nospamtwcny.rr.com)
Date: 04/02/04 

Date: Thu, 1 Apr 2004 23:35:46 -0500

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

-- 
Joe Fallon
Access MVP
"Chris2" <indanthrene.NOTVALID@GETRIDOF.yahoo.com> wrote in message
news:Pi5bc.58799$JO3.36518@attbi_s04...
>
> "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
>
>