[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.

[si_the_geek]

In many cases server-side cursors are good form, and are often recommended. Like almost anything however, there isn't a simple "one size fits all" golden rule.

While network issues could cause instability, it is not something that I would expect (unless the issues are big), and is not something that I have seen myself - even with dozens of simultaneous users (each with a large data set) across a national WAN.

What kind of issues were you getting?

[klempie]

Well, I'm not too concerned about the issues now. All I'm going to do is revert everything to JET calls, mainly because he's "got 30 years experience in the industry" and he wants the server-side cursors removed. So to placate him I'll do it. I just want to know when and why server-side cursors are a good idea so that next time I have a debate with a client about it, I don't have to just back down because of inferior knowledge.

[klempie]

The only thing I can see it useful for, is when there is limited bandwidth (so you don't have to get massive datasets) and/or when the client machines are lacking in storage and processing resources.

[si_the_geek]

Ah yes, "the user is always right".

The exact benefits of server-side cursors depend on the mix of technologies you use to connect (DAO/ADO/ODBC/OLE DB/...) and the database system you are connecting to.

A quick list of good/bad points can be found here (ADO documentation, but similar for DAO).

Other good points for server-side include:

• It is generally much faster, especially if the network is slow - as the data does not get transferred to/from your program as much (this is even if you want all of the data in your program, as it can be transferred in smaller chunks rather than waiting for one big transfer).
• it does not have as many issues when multiple users are working with the same database (such as locked records, which is much less frequent than with client-side).
• you can see data that other users have changed/added much sooner.
• ...

The bad points include lack of some features (in ADO you cannot use things like Recordset.Find, or paging), and higher reliance on the network (which is not good if the network is unreliable!).

[klempie]

Ah yes, "the user is always right".

Unfortunately yes. I personally think he was being podantic given the small loads on a dedicated network and small number of users.

The exact benefits of server-side cursors depend on the mix of technologies you use to connect (DAO/ADO/ODBC/OLE DB/...) and the database system you are connecting to.

A quick list of good/bad points can be found here (ADO documentation, but similar for DAO).

Ta.

Other good points for server-side include:

• It is generally much faster, especially if the network is slow - as the data does not get transferred to/from your program as much (this is even if you want all of the data in your program, as it can be transferred in smaller chunks rather than waiting for one big transfer).

Yes, I wanted to ask. With server-side cursors, are datasets returned record-at-a-time when using Move operations? If so, then I find his assertion even more ridiculous. I mean, it's one record at a time on a really fast network. His argument was that the frequent record requests could be putting pressure on the server/network, which sounded completely ridiculous to me.

• it does not have as many issues when multiple users are working with the same database (such as locked records, which is much less frequent than with client-side).
• you can see data that other users have changed/added much sooner.
• ...

The bad points include lack of some features (in ADO you cannot use things like Recordset.Find, or paging), and higher reliance on the network (which is not good if the network is unreliable!).

Yeh, that was why I had to keep the JET layer to do FindFirst etc. It has an unsavoury side effect though. When searching through a recordset, it sends SELECT statements to the server and just changing the values of the attributes in the WHERE clause. I'm going to take them all out and do the searches in code myself.

[si_the_geek]

Yes, I wanted to ask. With server-side cursors, are datasets returned record-at-a-time when using Move operations? If so, then I find his assertion even more ridiculous. ...

Basically yes - which is why it seems odd to me too.

Yeh, that was why I had to keep the JET layer to do FindFirst etc. It was an unsavoury side effect though. When searching through a recordset, it sends SELECT statements to the server and just changing the values of the attributes in the WHERE clause. I'm going to take them all out and do the searches in code myself.

I very rarely find a need for things like FindFirst (and certainly not a code equivalent, which would be slower and use more memory etc), as I just get the data that is needed by using an apt Where clause in the first place.

Obviously whether or not something like that is apt for you depends on the design of the application, but it can be relatively easy to change to that style.

[klempie]

Yeh I inherited it unfortunately so the FindFirsts were in there. I wouldn't use them either.

Unfortunately, because of the way the schema is, you can't do random access using WHERE clauses to get what we want. Difficult to explain but yeh, if the schema was designed better we wouldn't have to do it.

[klempie]

Anyway, thanks for all your help again si. I will certainly be better armed (apart from not being able to list experience) next time I get into a requirements debate with a client.

[si_the_geek]

Unfortunately, because of the way the schema is, you can't do random access using WHERE clauses to get what we want. Difficult to explain but yeh, if the schema was designed better we wouldn't have to do it.

There might well be a way - if you show us what kind of thing you do at the moment, we might be able to suggest a Where clause (or some other alternative) that works.

[klempie]

Nah it's ok. This project has a shelf life of about 6 months now. I'm not going to put much more effort into making it better. Two weeks from now we're canning development on it. Starting from scratch in .Net (if I have my way, but there are J2EE murmerings )

[leinad31]

Nah it's ok. This project has a shelf life of about 6 months now. I'm not going to put much more effort into making it better. Two weeks from now we're canning development on it. Starting from scratch in .Net (if I have my way, but there are J2EE murmerings )

Just curious. Does SQL Server support .Net languages internally, the same way that you can load Java in Oracle? It's a very convenient feature, e.g. sendmail java package, regular expression class, etc loaded in database.