[RESOLVED] Why is ODBCDirect more efficient than JET?

[klempie]

Some of you may remember that I converted an Access platform application to an SQL Server platform at the end of last year.

The way I did this was with two different data access components, one accessing an ODBC-linked Access 97 mdb and the other using ODBCDirect. The explanation for using both is that there were a lot of objects in the code using JET calls. Most of the code, however, could be converted to ODBC calls which I did because I thought there would be a performance benefit. Basically, everything that I have read has said that performance in ODBC would be better.

There have, however, been huge stability issues for remote client apps and during a fact-finding mission, the client's Head of IT basically told me that using server-side cursors was poor form and could be causing the stability problems. Now this makes sense to me if there is a network problem. But the systems that we setup up typically comprise of the server and two client machines on a switch or LAN. I could understand the server falling over with loads of clients and recordsets being created, but that just doesn't happen in our app.

Anyway, because of my inexperience in these matters, I've now done some analysis using SQL Server Profiler and I understand alot better how the cursors work and obviously JET doesn't use server-side cursors because it drags all the data over. I've also read here that:

Mechanically, much of Jet's interaction with a server is handled through two components:

Query Engine. This Jet service processes queries that you create, translates them into SQL statements, optimizes them, and compiles them.
Remote Manager. When your query must communicate with the ODBC API to share requests and data with a server, this component translates Jet's communications into ODBC function calls.

In contrast to Jet's local workings, the new ODBCDirect engine attempts to offload as much work to the server as possible. It does this by employing ODBC's ability to use cursors (server-based recordsets). When the DAO passes a direct record request through to the server, it tells the server to create a cursor using server resources. DAO then fetches records from the cursor as required. In essence, ODBCDirect moves steps 2 and 4 in Figure 19.1 from the client side to the ODBC driver if the driver is connected to a server that supports the required cursor capabilities. This increases performance.

So my question is this. What possible benefits could ODBCDirect have, given that server-side cursors (for reasons that are now clear to me) are an industry no-no? I don't see the point in it.

PS. I know that DAO is deprecated but it was a legacy component.