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

From: John Viescas (JohnV_at_nomail.please)
Date: 04/02/04 

Date: Fri, 2 Apr 2004 13:51:26 -0600

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)
"BradP" <anonymous@discussions.microsoft.com> wrote in message
news:22c501c418cd$8d89ff10$3501280a@phx.gbl...
> 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 data*** 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
>
> >-----Original Message-----
> >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
> >>
> >>
> >
> >
> >.
> >