js*****@comcast.net wrote:
No, it's not a PassThru....it was a query that a user designed with a
simple join in it. I've pretty much cured the problem by packaging
the SQL in a stored procedure with parameters and calling it with a
passthru call now. This is an old Access 97 app that has been in
place for some time now. The tables that this app is looking at have
grown over the years and we're starting to see degradation in
performance with some of the Jet queries on linked tables.
I have a workaround, but would like to know if Access is dragging all
of those records across the network just to do the join in design
mode. Found out yesterday after posting my question that it takes
forever to just open the query in design mode also.
You could use SQL tracing tools to see what exactly is being passed to the
server. Here is an example if what I have seen using SQL Trace when joining
two ODBC linked tables in Access.
Table1:
Field foo
Field bar
Table2:
Field bar
Access query joining on bar with a WHERE clause on foo.
Access pulls all desired fields from Table1 for rows where foo satisfies the
WHERE clause and then sends SQL to server for Table2 for rows where bar is
equal to...
first value of bar from Table1
OR next value of bar from Table1
OR next value of bar from Table1
etc...
So the query still has quite a bit of processing done by the server, but the
actual JOIN is not performed by the server. Rather it is replaced with a
whole bunch of OR criteria to match up Table2 bar to the values in Table1
bar.
Examining this you can see that if the number of rows in Table1 that
satisify the WHERE clause is small then the query can execute pretty
quickly. One would very much expect performance to fall off though as the
number of rows returned from Table1 increases.
So as I examine "standard Access queries" that use ODBC linked tables to see
which ones ought to be changed into Passthroughs or Stored Procedures I
first look at those that already perform slower than I would like. The next
group to look at then are those that have joins between one or more of the
linked tables.
And there is no speed advantage to moving everything into an Access project.
You can certainly go that route, but a Passthrough or Stored Procedure
executed via a Passthrough will perform just as well.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com