Client side cursor improves performance?

[Banana]

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.

 

[jal]

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.

 

[MarkK]

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

[SIZE="1"][FONT="Verdana"]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[/FONT][/SIZE]

 

[jal]

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

 

[MarkK]

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.

 

[KenHigg]

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

 

[MarkK]

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

 

[KenHigg]

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