myRecordSet.Addnew hangs

[snerting]

Hi!

Suddenly .Addnew is hanging for about 15-20 seconds on my ADO-recordset. It is working fine most places, but this one place is lagging. It does finish in the end, but with this latency it's pretty much useless.

Now, .Addnew isn't even supposed to talk to the database, is it? Anyway, it's Access 2003, VB6, ODBC using Oracle ODBC Driver from Oracle 8. The server is 10G, but the 8 adapter is working perfectly everywhere, even from Access, except for this one place where it hangs. If .Addnew isn't speaking to the database at all, then it shouldn't matter anyway.

Code is fairly simple. It returns a clone of current entry in l_rstCloneMe. The code calling it is looping over l_rstlCloneMe, feeding "cloneEntry()" with one record at the time. rsCopy is closed in the other function (some changes are made to the clone before it is updated and closed).

'Returns clone
Public Function cloneEntry(l_rstCloneMe As Recordset) As Recordset
' copy existing dataset-entry to new row.
Dim i As Integer
Dim rsCopy As Recordset
Dim rsNew As Recordset


Dim rssource As String
rssource = stripWhereOrderByGroupByClauses(rsCopy.name)

Set rsCopy = l_rstCloneMe ' source for copy
Set rsNew = dbs.OpenRecordset(rssource) ' destination of copy
rsNew.AddNew ' empty row for the new row which is a duplicate of the old. Lagging 15-20 seconds
For i = 0 To rsCopy.Fields.Count - 1
rsNew.Fields(i).Value = rsCopy.Fields(i).Value
Next i

Set cloneEntry = rsNew
End Function

 

[rubbernilly]

Explicitly dimension your recordsets as DAO or ADODB. That might have something to do with it.

 

[JerryKlmns]

A forward only type Recordset would be faster

Set rsNew = dbs.OpenRecordset(rssource, dbOpenForwardOnly)

and it is better to use

Dim rsNew As DAO.Recordset (it is DAO and not ADO because of the OpenRecordset method)

and add

rsNew.Close
rsCopy.Close
Set rsNew = Nothing
Set rsCopy = Nothing

when done

 

[snerting]

Uhm, I'm using DAO, yes, not ADO as I stated above. Did try to explicitly declare the recordset as DAO.Recordset, no luck.

I wrote an extremely simple function adding 6 meaningless entries to the table at hand, which reproduced the lag-problem. It takes about 2 minutes inserting those 6 records.

 

[snerting]

Uhm, this is my new little test-script which is reproducing the problem. Now I'm only inserting so cannot be the dbOpenForwardOnly-thingy.

Private Sub TestButton_Click()
DoCmd.Hourglass True
Dim kake As DAO.Recordset

Dim i As Integer
Dim j As Integer
j = 5
Debug.Print "Before inserting to table1: " & Now()
For i = 0 To j
Set kake = dbs.OpenRecordset("SELECT * FROM table1")
kake.AddNew
kake!use = i + 1
kake!template_fk = 13371337
kake("_DS_DATASET") = 5
kake.Update
kake.Close
Set kake = Nothing
Next i
Debug.Print "Before inserting to table1: " & Now()
For i = 0 To j
Set kake = dbs.OpenRecordset("SELECT * FROM table2")
kake.AddNew
kake!stat = i
kake!Value = i
kake!template_fk = 13371337
kake("_DS_DATASET") = 5
kake.Update
kake.Close
Set kake = Nothing
Next i
Debug.Print "Done inserting to table2: " & Now()
DoCmd.Hourglass False
End Sub

---- output ----
Before inserting to table1: 08.11.2005 15:17:57
Before inserting to table2: 08.11.2005 15:18:04
Done inserting to table2: 08.11.2005 15:20:12
----------------

I kinda think 7 seconds is too long for 6 inserts as well, but 2 minutes 8 seconds is horrific. The only apparant difference between the two tables is that table2 holds 150.000 entries, while table1 holds 8000.

 

[snerting]

Did the "exact" same thing now from Python using ADODB connection, which was instant.

Then I replaced the content of the loop with dbs.Execute("INSERT....") statements, which was fast. So, the question remains, why does Addnew lag like this?

 

[snerting]

Ok, I found the problem..

As I mentioned above, the main difference between the two tables was that the slow one contained 150,000 records while the "fast" one contained 8,000 records. Furthermore, the "fast" one used 7 seconds while the "slow" used 2 min 8 seconds.

150.000/8000 = 18,75
128s/7s = 18,29

Very similar, right? So the increase in time was linear and the slope was about the same.

I put in a where-clause in the select that creates the recordset I use Addnew on. The where-clause will always return 0 records. Guess what, Addnew was suddenly instant.

So, Addnew will step by all records in the recordset before adding a new one at the end. This stepping takes a considerable amount of time when the recordsets grow large.

 

[lesw1433]

Wouldn't the .MoveLast before the .AddNew solve the problem?? Was just curious.

 

[lesw1433]

Just another thought, you are opening and closing the recordset within the loop which will also cause some delays. You might also play with the .MoveNext at the end of the loop and close the recordset after the loop is complete.

 

[snerting]

lesw1433: I know closing and opening the recordset is not the most ideal way to go, but the test-code was meant to reproduce the problem, not work around it.

.MoveLast would not solve the problem as MoveLast is also stepping through all the entries and is thus also using a lot of time when the recordset grows large. It might very well be that the actual implementation of .Addnew is using the same back-end code as .MoveLast. It is probably all tied together with the fact that you cannot do a .RecordCount unless you have been at the end of the Recordset, you simply have to step over all the records until EOF.

I think the only way to do this quickly without putting a where-clause that provides a small/empty recordset is to put the entire recordset into the memory. As I don't really need the data anyway, just the recordset so I'm able to use the addnew, a where-clause is probably better (SELECT * FROM foo WHERE 1=0).