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

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?

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?

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,

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

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

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.

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.

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 - :)

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.

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?

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?

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?

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 (http://msdn.microsoft.com/en-us/library/ms676564(VS.85).aspx)

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.

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

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.

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.

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.

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

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

Is the update equally slow for all three possible methods?

That was what I was stumping about earlier but didn't have a answer until you mentioned that.

Is the update equally slow for all three possible methods?

That was what I was stumping about earlier but didn't have a answer until you mentioned that.
Once I remove the line

rs.CursorLocation = adUseclient

I get very nice performance (about 13 seconds) on the updates. DAO did the job in about 9 seconds.

Not sure if that's what you were asking.

Interesting thread. Really liked Banana's cursor essay.
Inspired me to check timings on a bunch of inserts inside and outside a DAO transaction, and inside the transaction the inserts completed in about half the time. Here's the code...

Sub TransactionTimings()
Const MAX_LOOP = 5000&
Const TABLE_NAME = "tDate"
Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim time As Single

'get or clear a table
GetTable CurrentDb, TABLE_NAME

Debug.Print "Inserting " & MAX_LOOP & " records, twice..."

'start timer for non-transaction enabled database
time = Timer
'get current db
Set dbs = CurrentDb
'insert a bunch of dates
InsertDates dbs, MAX_LOOP, TABLE_NAME
'and print how long it took
Debug.Print "No Transaction: " & Timer - time & "sec"


'start the timer
time = Timer
'get the default workspace
Set wrk = DBEngine.Workspaces(0)
'begin trans against the default workspace
wrk.BeginTrans
'get CurrentDB from transaction enabled default workspace
Set dbs = CurrentDb
'run a bunch of inserts
InsertDates dbs, MAX_LOOP, TABLE_NAME
'commit the transaction
wrk.CommitTrans
'and how long did that take
Debug.Print "Transaction : " & Timer - time & "sec"
'and prove all the records were actually added
Debug.Print TABLE_NAME & " contains " & DCount("*", TABLE_NAME) & " records."
Debug.Print

End Sub

Sub GetTable(dbs As DAO.Database, tableName As String)
On Error GoTo handler
'create the table
dbs.Execute _
"CREATE TABLE " & tableName & " " & _
"(id COUNTER CONSTRAINT PK_ID PRIMARY KEY, " & _
"[mydate] date)"
Exit Sub
handler:
If Err = 3010 Then 'table already exists
'or if it already exists, delete any records
dbs.Execute "DELETE FROM " & tableName
End If
End Sub

Sub InsertDates(dbs As DAO.Database, count As Integer, tableName As String)
Dim i As Integer
'inserts multiple records into the table
For i = 1 To count
dbs.Execute _
"INSERT INTO " & tableName & " " & _
"( mydate ) " & _
"VALUES " & _
"( #" & Now() & "# );"
Next
End Sub

Pretty cool, Lagbolt, as I did not know how to do a tran in DAO.

Ya, if you start a transaction against the default workspace at DBEngine.Workspaces(0), then your ensuing references to CurrentDb return a transaction enabled DAO.Database. As I understand it, DAO caches the results of action queries and implicitly performs the disk writes as a batch when you commit the transaction.

Ya, if you start a transaction against the default workspace at DBEngine.Workspaces(0), then your ensuing references to CurrentDb return a transaction enabled DAO.Database. As I understand it, DAO caches the results of action queries and implicitly performs the disk writes as a batch when you commit the transaction.

Think you could do a simple db that illustrates the use of this tran stuff as I've never used it either. Something simple that myself and others can tinker with to get the hang of it... ?

Ken, as per your request, here's a little transaction explorer. It's mostly pretty simple, but the whole transaction thing is not that tricky. In it's simplest form it's this...
1) Begin trans against default workspace (DBEngine.Workspaces(0).BeginTrans)
2) Perform operations on database in that workspace (CurrentDB)
3) Commit or Rollback trans. (DBEngine.Workspaces(0).CommitTrans)
Cheers,
Mark

Thanks Mark, It's going to take me a while to figure what going on in the db.