472,805 Members | 1,316 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

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

I changed from Access97 to AccessXP and I have immense performance


- 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 wasnt 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
- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server application

Thanks , Andreas
Nov 12 '05 #1
1 3466
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
Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Dennis Roberts | last post by:
I have a script to parse a dns querylog and generate some statistics. For a 750MB file a perl script using the same methods (splits) can parse the file in 3 minutes. My python script takes 25...
by: David Jones | last post by:
Hi, I am trying to hunt down the difference in performance between some raw C++ code and calling the C++ code from Python. My goal is to use Python to control a bunch of number crunching code,...
by: Tim | last post by:
Hi, I'm using ASP to connect to an ACCESS database (db1) via ODBC. The table that I connect to (table1) in db1 is an ACCESS query linked to data in another database (db2). When I try the...
by: Aaron | last post by:
Hello fellow programmers, I am trying to run an append/update query from code, a command button on a form initiates the queries. the format i am using is; ...
by: Sylvain | last post by:
Hi ! I have written an installer (with Innosetup) which do many things and one of them is checking for a required version of installed Jet4 ans if necessary, install it according to the OS. It...
by: fumanchu | last post by:
I need to insert records into a Sybase database via a linked table. I have a valid, tested DSN ODBC connection to the Sybase database named 'SBA9link'. I also have created a link to a table in...
by: Mark Shelor | last post by:
I've encountered a troublesome inconsistency in the C-language Perl extension I've written for CPAN (Digest::SHA). The problem involves the use of a static array within a performance-critical...
by: monnomiznogoud | last post by:
Ok, my problem is the following: I have very complicated Access 97 databases that link through ODBC to Sybase databases. Now in some of the forms controls I had queries that used as "where...
by: olle | last post by:
Hi. I have the problem that some records in a ms sqlserver table is unable to update from Access. I get the error message odbc-time out error in linked table...... I tried to copy this table...
by: eHaak | last post by:
A couple years ago, I built a database in MS Access 2003. I built the form using macros in some of the command buttons, and now Im trying to eliminate the macros and just use visual basic code. ...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.