Cursor Types

[David.Poundall]

Could someone please set me straight on cursor types I am going round in circles here.

I thought I was using adUseClient for Cursor Location but in fact it turns out I was using adUseServer. I have heard snippets that say that this latter cursor location has unreliable record count feedback. I have not found this to be the case.

However, I find that I have an Update speed on a record of about 100msecs per record. Which is unacceptable slow. So I have done a trawl of the dbase forum and found hints that adUseClient should be faster.

But In fact I find it is ten times slower ??????

Does anyone know what the fastest way of doing a record update is in MSAccess 2000 ? Also what should I be using for my Cursor Location, client or server.

Thanks in advance.

.

[MystikShadows]

Hi,

To answer your question I need to explain the difference between the two.

adUseServer will let the server perform the query you are asking for and return you the results.

adUseClient will perform the query on the machine running your software.

If your database is on your computer (say it's just a database destined to be used by your application only, no server, no internet access at all) then aduseserver will be faster because it will be executed from the MSDE engine itself.

If your database is on a website, or on a network somewhere, aduseClient will be faster as it frees the server from performing the query thus lightening it's workload considerably.

Hope that cleared things up a bit :-)

[David.Poundall]

Thanks you for the detailed response MystikShadows.

I must be thick though as I am still having difficulty understanding HOW the work is being done with the two different cursors.

Is the client in this instance - VB? In that case the server must be JET, and is 100msecs typical for a jet adUserServer update instruction? It seems slow to me.

Would it be quicker to do an SQL update querie rather than using ADO do you think ?

.

[MystikShadows]

yes...VB would be the client, the client would technically be the ADO or DAO or RDO or OLEDB object hierarchy you'd use to connect to the database.

the Server is Access's own database access....the reason for the 100ms is probably because of page caching (buffering of records for increased speed gains (useful only if you actually need that many records). Not sure about access 2000 but access 97 defaults this to 100 cached records. takes time to cache those records. there's a setting somewhere to change that, i;m thinking of you bring it down to 1 or 10 you'd probably get faster performances.

As for the SQL Update querie...from VB's code side it's quicker to do

cSQLString = "UPDATE <TableName> SET FieldName = FieldValue"
AdoCommand.Execute cSQLString

than to do

RecordsetObject.Edit
RecordsetObject.Fields(1) = FieldValue1
RecordsetObject.Fields(2) = FieldValue2
RecordsetObject.Fields(3) = FieldValue3
RecordsetObject.Fields(4) = FieldValue4
RecordsetObject.Fields(5) = FieldValue5
RecordsetObject.Update

Like I said, if your access database is local to your application, executing the SQLString through the command object will be as fast as making access perform the same task....however, if your database is on a server somewhere, chances are using adUseClient will be faster as far as VB's side is concerned.

Hope that helped :-)

and to answer your quote.

If you gain wisdom through your mistakes, I must be ripe to move myself on top of a mountain somewhere by now...LOL

[David.Poundall]

LOL

Many thanks for that. I have an experiment or two to be getting on with tomorrow now to find the fastest way of updating - sigh.

For anyone else out there who needs more info, this link was posted in the dbase forum and answers a lot of peripheral questions on cursors.

http://www.adopenstatic.com/experime...dsetpaging.asp

Many thanks Mystic

[MystikShadows]

You're very welcome :-)

[David.Poundall]

BIG NOTE -

I ran my database update code last night and finally hit the sack. This morning I checked the stats and my update of 65000 records had taken about two hours. Roughly 10 updates a second.

This morning I noticed that one of the records had not updated correctly. I lazily blanked out 95% of the update code (case statement) just leaving the one records type I needed to do the update on.

Running the code again, I expected the counter to fly up to the record that I needed to change - and then plod at ten records a second.

But no, what happened was that even cycling through to next record - without doing anything - still took 0.1 of a second.

So, I modified the SQL to pick out ONLY the lines I needed to update. Pressed the button, and 3500 records updated in 35 seconds. 10mSecs per update as oposed to 100msecs.

So it looks like it was the physical size of the recordset that was causing my speed hangups. It wasn't the update, just the .movenext statement.

So the tip looks like being .. chop up recordsets where you can and update in small chunks rather than large chunks.

FYI. The 65000 records occupied 15Meg of the Access db size.

If I ever get around to it I will do a speed compare using an SQL to do the update rather than using ADO.