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

[Van T. Dinh]

There are a number of different problems but try the Microsoft KB article:

http://support.microsoft.com/default.aspx?id=302496

 

[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

 

[Van T. Dinh]

Try Tony Toews' article:

http://www.granite.ab.ca/access/performancefaq.htm

If nothing helps, create a new database and import all objects from the
existing database. Make sure you add a extra References as necessary.
Compile and then try your Queries again.

The purpose of the above process is to get rid of possible corruptions in
your database. I have just fixed a client's Front-End with the above
process 2 days ago. Everything worked fine except there were 2 Forms that
open extremely slow. Monitoring the network traffic, I found that the PC
received 6000 packets from the server at above 100 packets at a time. The
same process from another PC received only 2000 packets virtually
instantaneously. Network connection checked out OK so I suspected
corruptions and the above process fixed the problem.

 

[Tony Toews]

I have just fixed a client's Front-End with the above
process 2 days ago. Everything worked fine except there were 2 Forms that
open extremely slow. Monitoring the network traffic, I found that the PC
received 6000 packets from the server at above 100 packets at a time. The
same process from another PC received only 2000 packets virtually
instantaneously. Network connection checked out OK so I suspected
corruptions and the above process fixed the problem.

This is very interesting. That two forms would be corrupt on one
system and not another. Did you try replacing the FE on the bad
system with another?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Van T. Dinh]

I didn't replace the Front-End since this user does a different job from the
rest and he has had a few custom modifications just for him.

However, I did check the Queries that were used as RecordSource for the
Forms. The Queries are exactly the same with other Front-Ends but they
still run a lot slower than the Queries in other Front-Ends (still with
about 3 times the number of packets received compared to normal).

 

[Tony Toews]

I didn't replace the Front-End since this user does a different job from the
rest and he has had a few custom modifications just for him.

However, I did check the Queries that were used as RecordSource for the
Forms. The Queries are exactly the same with other Front-Ends but they
still run a lot slower than the Queries in other Front-Ends (still with
about 3 times the number of packets received compared to normal).

Very interesting indeed. And importing into a new MDB fixed things.
Hmm, maybe there was something screwed up in the various statistics
Access keeps track of on tables.

ACC2000: How to Optimize Queries in Microsoft Access 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;209126
has a little information on this.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm