Slow ODBC Queries to SQL 2000

[KTM]

I have a database with linked tables to an SQL 2000 database via an ODBC DSN.
On some computers, the queries return results almost immediately while on
others they take 10 times as long to get results. After performing a number
of traces, I've found that the users with a fast response are performing all
of the queries using SQLExecDirect calls whereas the slow machines are
performing SQLPrepare/SQLExecute calls.

I've compared MDAC settings and drivers and everything is the same (as near
as I can tell). We're using Access 97 SR-2 on Windows XP Pro. All machines
are patched with the latest Jet service packs and SQL drivers. The same
issue occurs with both SQL Server and SQL Native drivers.

Given that both sets of computers are using the same Access 97 database
application, datasources, and identical transactions, does anyone have any
suggestions why I'm seeing a difference?

KTM

 

[david]

It's supposed to be a property of the query, set when the
query plan is prepared (on the Access side. Not the query
plan used by the server).

I suppose it's possible it might be reset when the database
is compacted???

Another possibility is the "prepare" keys at
\HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5\Engines\ODBC

....but I'm just guessing wildly. I don't have any idea either.

(david)

 

[KTM]

I'm not using static queries but ones that are build in the code and are
called using an openrecordset. Compacting has had no effect. Specifying
dbExecDirect on the method also has no effect. The same code/query on one
computer runs fast and slow on another. Something on the slow computer is
causing the queries to run as parameterized queries and uses
SQLPrepare/SQLExecute calls instead of SQLExecDirect. I'm trying to figure
out what is causing that to happen.

KTM

 

[KTM]

For what it's worth, I've eliminated the problem by removing a linked query
that had a calculated field in it and modified the code to handle the data in
a different manner.

I still don't understand why one computer worked one way versus another
however I have eliminated the repeated calls to SQLPrepare and resolved the
performance issue.

Regards,
KTM

 

[david]

If you are building the queries dynamically, you should try explicitly
setting the "prepare" property instead of depending on the default
value.

(david)