Bad performance in queries with Jet4.0 and linked ODBC-tables to SQL-Server 2000

[Andreas Lauffer]

I changed from Access97 to AccessXP and I have immense performance
problems.

Details:

- Access XP MDB with Jet 4.0 ( no ADP-Project )
- Linked Tables to SQL-Server 2000 over ODBC

I used the SQL Profile to watch the T-SQL-Command which Access ( who
creates the commands?) creates and noticed:

1) some Jet-SQL commands with JOINS and Where-Statements are
translated very well, using sp_prepexe and sp_execute, including the
similar SQL-Statement as in JET.

2) other Jet-SQL commands with JOINS and Where-Statements are
translated very bad, because the Join wasn´t sent as a join, Access
collects the data of the individual tables seperately.
Access sends much to much data over the network, it is a disaster!

3) in Access97 the same command was interpreted well

Could it be possible the Access uses a wrong protocol-stack, perhaps
Jet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server or
Jet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead of
Jet to ODBC and ODBC direct to SQL-Server

Does anyone knows anything about:

- Command-Interpreter of JetODBC, Parameters, how to influence the
command-interpreter
- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server application

Thanks , Andreas

[Albert D. Kallal]

I am not aware that changing from a97 to XP will cause the ODBC linked
tables to slow down. Are you really experience a difference in performance,
or when you changed, are you trying new things?

2) other Jet-SQL commands with JOINS and Where-Statements are
translated very bad
In fact, any query you save in the query tab that uses multiple tables tends
to NOT work well via ODBC. Often, JET has to assume that each table is
complete separate,and tries to join them it's self. If you think about this,
it makes sense, since via ODBC, JET does not really know what is at the
other end.

The solution is to simply take any query you have with multiple tables and
change them to a view on the sql side. You then can even use forms, or
reports on those views. In fact, even in-line sql via JET with where clauses
works VERY WELL on these views.

So, most of you saved queries will work just fine via odbc, but when you
start working with joins etc, then via ODBC, JET has a very difficult time,
since JET has to do the data join, and NOT the server.

3) in Access97 the same command was interpreted well
Hum, that is news to me. I did not think that jet 4 got more dumb in this
case. However, even in a97, I did find that relational joins don't work well
when they are used from the query tab. In fact, even worse is any query that
is based on another query. Again, JET has to layer or "stack" this request.

Of course, if you make the query a pass through, then the join will work
just fine.

My general rule so far for linked tables to sql is that when doing JOINS,
you don't want to use a query on the ms-access side. Further, any query that
is based on a query is also very poorly dealt with. (again, this is becomes
obvious, as JET has to setup sql for the query, and then work on that).
Could it be possible the Access uses a wrong protocol-stack, perhaps
Jet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server or
Jet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead of
Jet to ODBC and ODBC direct to SQL-Server

Again, I have not noticed a different between a97 and xp in this regards. It
is certainly possible that the ODBC is JET 4 is more compatible with odbc
(and thus more dumb). I mean, you can always start to use ADO in place of
DAO. However, in my experience, I find that DAO to sql server via linked
tables works VERY good, but you need to avoid using quires with joins. If
you have any joins..then convert that query to a view on the sql side, and
you will find performance just great. Also, don't use any query that is
based on another query..as again JET don't do a good job.
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn