Linked Tables and JET Query Optimization

[bdtmike]

I have a query that JET doesn't optimize as wewll as it should. Does
anybody have any ideas how to force JET to handle this situation
better?

I have an Access application that uses linked tables on a SQL Server
2000 database. These are large tables (400,000+ rows).

I have a query screen I've designed for my users. They can enter
criteria for a number of different fields. I construct an SQL statement
and it results in a simple one-table SELECT statement with WHERE
criteria. Here's the rub: Some of the criteria fields result in an SQL
statement with a WHERE clause that involves Access expressions that
must be processed locally. This, itself, isn't a big deal. Jet
correctly passes the criteria that can be processed at the server and
the local processing is evaluated against this smaller result set.

However, there is one criteria they may specify that requires a lookup
into a 2nd table. I have implemented this as an IN clause and a
subquery. When this occurs, JET doesn't send this to SQL server along
with the other criteria that can be processed there. Instead, it
evaluates this subquery for each and every record that is processed
locally. On a 400,000 row table, this makes for one slow-ass query!
I've tried building a querydef that executes the subquery and building
another query that hits on this, but JET "Optimizes" this in the same
slow way. Any ideas on how to get around this behavior?

 

[Dirk Goldgar]

I have a query that JET doesn't optimize as wewll as it should. Does
anybody have any ideas how to force JET to handle this situation
better?

I have an Access application that uses linked tables on a SQL Server
2000 database. These are large tables (400,000+ rows).

I have a query screen I've designed for my users. They can enter
criteria for a number of different fields. I construct an SQL
statement and it results in a simple one-table SELECT statement with
WHERE criteria. Here's the rub: Some of the criteria fields result
in an SQL statement with a WHERE clause that involves Access
expressions that must be processed locally. This, itself, isn't a
big deal. Jet correctly passes the criteria that can be processed at
the server and the local processing is evaluated against this smaller
result set.

However, there is one criteria they may specify that requires a lookup
into a 2nd table. I have implemented this as an IN clause and a
subquery. When this occurs, JET doesn't send this to SQL server along
with the other criteria that can be processed there. Instead, it
evaluates this subquery for each and every record that is processed
locally. On a 400,000 row table, this makes for one slow-ass query!
I've tried building a querydef that executes the subquery and building
another query that hits on this, but JET "Optimizes" this in the same
slow way. Any ideas on how to get around this behavior?

What a pain!

I can't give you an easy answer, I'm afraid. I'd be looking at one of
two approaches.

1. Figure out how to implement those expressions that "must be processed
locally" on the server side instead, so that the whole query can be
offloaded to the server. For example, I've written SQL Server versions
of a number of some of the more commonly used VBA functions. And
references to form controls and such can be handled by modifying the SQL
of a pass-through query on the fly.

2. Try to build a SQL Server query that does everything that can
possibly be done on the server, subquery and all. Maybe build this as a
SQL Server View and link it as a table, or else make it a pass-through
query. Then build a local query that uses that query.

 

[Albert D.Kallal]

Well, were does the resulting data need to be used?

Is it for editing...or for a reprot?

The first and easy could would be to build a query with the 2nd table joined
in. You then build a view..and use that as a linked table. This will be VERY
fast in terms of performance. It is just not sure if you need, or desire to
have the "many" side of the data repeated in this view.

If the data is to only be used for a report, then another approach is to
build the sql..and SAVE it as a pass-through..and thus the report will be
based on this query......

So, build a query. Make it pass-though.

Then, in code...modify the query def......and then launch the report......

 

[bdtmike]

Thanks, Dirk. Either way, it's going to be a lot of work. I avoid SQL
Server specific queries because our application also uses an Oracle
Backend, depending on the specific installation.

 

[bdtmike]

The "Query by Form" screen I created may be used in many different
situations. Fortunately, we never need to update the data, it's only
for output (e.g. a report, an export).

The perplexing thing here is that the SubQuery I use should be sent
over to the server prior to any local processing. Jet sends all the
other "server-side" criteria over first, but not the Subquery. I
checked the Whitepapers about JET ODBC Query optimizations and it makes
no mention of SubQueries.

 

[Dirk Goldgar]

Thanks, Dirk. Either way, it's going to be a lot of work. I avoid
SQL Server specific queries because our application also uses an
Oracle Backend, depending on the specific installation.

Unfortunately, that's going to make optimization difficult. I suppose
you could write a separate set of pass-through queries for each server,
and set up your application to use the appropriate query depending on
the server type.

 

[Albert D.Kallal]

There are cases where JET does a bad job...and your example is one of
them...

However, since this is for a report...then use a sql server view (if that
will work), or
simply use a pass-through query..and you will be fine....