Client side cursor improves performance?

[jal]

Client side cursor improves performance? If so, what am I doing wrong? I tried to create a performance test, using a table of 200,000 records.

The code is very simple. It simply loops through the table once, numbering the rows. It numbers them according to lastname.

Smith......1
Smith......2
Johnson...1
Johnson...2
Johnson...3

I am using ADO. When I specified "client-side cursor" (highligted in red), it ran so slow that I gave up after 10 minutes (I am simply running a regular Windows form on my desktop computer). When I removed that line of code, it took 13 seconds. (By the way, in DAO it took 9 seconds).

DAO.DBEngine.SetOption dbMaxLocksPerFile, 50000
Dim rs As New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT LastName, Index FROM Customers ORDER BY LastName", CurrentProject.Connection, adOpenStatic, adLockOptimistic
Dim index As Long, previousName As String
index = 0
previousName = rs("LastName")
With rs
Do While Not .EOF
If rs("LastName") = previousName Then index = index + 1 Else index = 1
rs("Index") = index
previousName = rs("LastName")
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing

 

[KenHigg]

This 'Client side cursor' stuff is greek to me but DAO is connected at the hip to Jet while ADO is for more generic use which probably means there are several extraneous layers it has to go through to do stuff. Also, if you said DAO ran in 9 seconds why not use it?

 

[jal]

Also, if you said DAO ran in 9 seconds why not use it?

It's useful to learn both ADO and DAO. For instance you can use Access as a front end to SQL Server where ADO is generally used.

Even in a pure Jet scenario, ADO does some things that DAO can't. For instance you can't bind a DAO recordset to a listbox, in Access 2003. The same is true with some of the other controls as well.

Does anyone know the answer to my question?

 

[alfi]

try to set your recordset open statement as follow :
rs.Open tsql, conn, adOpenForwardOnly, adLockBatchOptimistic , mybe the problem is in the table/query use index/ key to improve proformance,

 

[KenHigg]

Here's a bit I managed to google, maybe it helps a tiny bit:

http://www.adopenstatic.com/experiments/clientsidecursortypes.asp

 

[jal]

try to set your recordset open statement as follow :
rs.Open tsql, conn, adOpenForwardOnly, adLockBatchOptimistic , mybe the problem is in the table/query use index/ key to improve proformance,

You say to use batch-updating. The loop executes very fast that way (about 7 seconds whether static or forward-only) but then, after the loop, I presumably have to call rs.UpdateBatch, but when I do that, Access comes to a standstill - I checked the table to see if any records are updating - which they are - but at a crawling pace. This isn't helping.

I'm fairly certain that I've read about clientside cursors improving performance, so maybe I'm doing something wrong.

 

[jal]

Here's a bit I managed to google, maybe it helps a tiny bit:

http://www.adopenstatic.com/experiments/clientsidecursortypes.asp

I'm pretty sick today (stayed home from work) so it's hard to comprehend what I read. I take he's saying that AdUseClient does NOT give a performance advantage?

That seems to be what I am experiencing, although it seems contrary to what I've heard elsewhere.

 

[KenHigg]

Like I said it's all greek to me but when you get it all sorted out it'd be cool if you could post what you figure out -

 

[ByteMyzer]

I'm fairly certain that I've read about clientside cursors improving performance, so maybe I'm doing something wrong.

Using a Client-side Cursor can improve performance if the Database Engine behind your connection is anything OTHER than Jet-SQL (ex. SQL Server, Oracle).

Because your code specifies CurrentProject.Connection as the ADO connection, this indicates that your connection is a Jet-Database Engine. With the Jet-Engine, the Client is also the Server, since Jet is not a Client/Server data engine, but rather, a file-server data engine. Consequently, when you specify a Client-side cursor with an ADO connection to the Jet-Database, you are tasking your machine with both Client and Server operations.

 

[KenHigg]

So what the heck is client side cursor anyway? My guess is that the sql steps through records on the client rather than the server? So if you were on a network all of the records would have to be brought over to the client?

 

[jal]

Using a Client-side Cursor can improve performance if the Database Engine behind your connection is anything OTHER than Jet-SQL (ex. SQL Server, Oracle).

Because your code specifies CurrentProject.Connection as the ADO connection, this indicates that your connection is a Jet-Database Engine. With the Jet-Engine, the Client is also the Server, since Jet is not a Client/Server data engine, but rather, a file-server data engine. Consequently, when you specify a Client-side cursor with an ADO connection to the Jet-Database, you are tasking your machine with both Client and Server operations.

Ok that makes sense, apparently a client-side cursor has a performance loss in a local-machine-only scenario. I'm surprised that the difference would be so dramatic. 13 seconds versus -about 20 minutes?

 

[jal]

So what the heck is client side cursor anyway? My guess is that the sql steps through records on the client rather than the server? So if you were on a network all of the records would have to be brought over to the client?

Excellent question. ClientSide is alleged in the article to scale better than server-side cursors, but isn't that a dubious conclusion if network traffic is increased?

 

[ByteMyzer]

So what the heck is client side cursor anyway? My guess is that the sql steps through records on the client rather than the server? So if you were on a network all of the records would have to be brought over to the client?

See the following article: The Significance of Cursor Location

 

[Banana]

The side of a cursor only dictate where the operations happens, on the client (e.g. your computer) or the server (e.g. the host computer holding the backend data).

Cursors basically load a list of keys and maintain the position where it is at a given row. With a Forward-Only cursor, it only loads the keys that are next to the current row it resides on and toss it out when it navigates to next key.

Static cursor merely fetches a list of keys matching a given query and maintain the position so one can navigate forward, backward and move to first and last. It, however, does not check back to see if the list has changed since it was fetched (hence the name 'Static')

Dynamic cursors are the most expensive cursor; providing fully updateable list and monitor to see if new rows that matches the criteria were added, old rows deleted by other users, or if data for a given row was changed.

Keyset cursors is basically a static cursor but with ability to view changes for the given list retrieved, though it may not know of any new rows added or old rows deleted.

Now, the location of cursor affects what cursor types are available. Think about it; if we had a dynamic cursor on client-side, that wouldn't really make much sense because we're saving and editing data on the server, while client had its own process which remains ignorant of those changes. For that reason, dynamic and keyset cursors can only be server-side, so it's managed by the same daemon that will write and read the changes.

When client-side cursors is used, it is now managed by ADO library, not by the server's daemon so only forward-only and static cursors can be available to clients. It also should not go unnoticed that when it's a client-side cursors, we now have a extra round trip to server when it requests for the keys then when the key is iterated, another request for the actual data. Thus, as a general rule, client-side cursors require more network traffic than server-side cursors.

Please keep in mind that the above discussion is totally in context of a server-client configuration. With Jet, there is no daemon to centrally administer the database; all processes are inherently client-side, whether we use ADO or DAO, and this remains true even when the database is split and physically located on another drive on a network.

Thus, it goes without saying that what works for SQL Server, MySQL, DB/2, or whatever may not be necessarily true for Jet. When we request server-side client with a Jet, all we are doing is asking ADO library to manage it, and in that instance, we see more overhead than if we used client-side cursors.

Another reason why client-side cursor may be preferable to server-side cursor is that they can relieve the processing from a server; it can mean that server can serve more users concurrently at expense of increased network traffic . There may be other scenarios where we need client-side cursors, but hopefully the explanation helps give us a framework for understanding the nature of cursors and knowing which types and sides of cursors to use for a given situation.

PS Reading ByteMazer's great link, I realized one way to think about the prices of network traffic location of cursors incur: Server side cursors are more 'chatty', while client-side cursors are more 'chunky'. Someone once mentioned to me that in general, chunky client is preferable to chatty client. I'm not totally convinced with that proposition, but I can understand why; it's easier to process fewer requests with more packets each than it is to manage several requests with few packets each to same client. So, again, I'd bet the exact topology of your network may affect the viability of either side.

 

[KenHigg]

Wow... Great post Banana. Thanks...

Seems if it took 10 minutes to process like in the original post you'd be better off to bring the entire dataset over to the client and process it it there...

 

[Banana]

Just wanted to add some more points:

Whenever you want to bind a ADO recordset to say a form or recordset, it must be out of necessity a client-side cursor; else the class managing form would have no idea whether it should disable the move to first record, move to last record, previous and next buttons, and where it stood, and if we want to update the recordset, it further requires that we use a Static type cursor. Not much of a choice there. (I should hasten to add that SQL Server's ADO provider is slightly different; server-side keyset cursors can be used in that context.)

Furthermore, to correct a misunderstanding, either DAO and ADO recordsets can be bound to a listbox or combobox, and I'd always prefer DAO over ADO for Jet-only scenarios.

Also, IINM, batch-updating is irrelevant in this context because we aren't really working with a disconnected recordset.

 

[jal]

Furthermore, to correct a misunderstanding, either DAO and ADO recordsets can be bound to a listbox or combobox

I'll have to work on that, then, becaue the last time I tried binding a DAO recordset to a listbox it threw an error. I don't know what I did wrong.

 

[Banana]

One thing that leaps out to me is that this only works if we're using Access 2003 format. This is not available in Access 2000 format, and Access 2003 by default uses 2000 format so one may have to explicitly convert the mdb to 2003 format. Beyond that, I don't remember doing anything special to get it to work.

 

[jal]

Thanks everyone, especially Banana. This has been really helpful. Here again I tried to rep you but I am still getting the msg, "You cannot rep Banana again at this time."

 

[jal]

I wanted to add, the performance loss was aparently a matter of updating records. As for just looping through the records, the speed was excellent even with a client side cursor (about 3 seconds).