ADO batch inserts appallingly slow

[DevHead]

I need to transfer 144000 records from an access DB to an access DB using a VBA client app. I'm using ado recordsets to batch the inserts but its really slow, about 24 records processed per second. So it takes about an hour and 40 minutes to transfer. The retrieval time is negligeble compared to the insert time. Does anybody know a faster way to insert records into access?

Generic code below:

Private Function FillDataTable(rst_RS As ADODB.Recordset, _
                                cnx As ADODB.Connection)


         Dim rst_Flow as New ADODB.Recordset
         Dim str_SQL as String
         Dim int_RecCount as Integer

         Do While Not rst_RS.EOF
            str_SQL = "SELECT * FROM TBL1 WHERE ID = 9999999"
            
            rst_Flow.CursorLocation = adUseClient
            rst_Flow.LockType = adLockBatchOptimistic
            rst_Flow.Open str_SQL, cnx
                   
            Do While Not rst_RS.EOF And (int_RecCount < 1000)
        
                    rst_Flow.AddNew
                    rst_Flow!Field1 = rst_RS!Field1
                    rst_Flow!Field2 = rst_RS!Field2
                    rst_Flow!Field3 = rst_RS!Field3
                    rst_Flow!Field4= rst_RS!Field4
                    rst_Flow!Field5= rst_RS!Field5
                    rst_Flow.Update
                End If
                int_RecCount = int_RecCount + 1
                
                rst_RS.MoveNext
            Loop
            DoEvents
            int_RecCount = 0
            rst_Flow.UpdateBatch
            rst_Flow.Close
        Loop
  End Function

 

[Travis]

You could try a striaght Append Query (Insert Into). You have not made it clear why you want to break this down into 1000 records at a time.

Is [ID] the primary Index?

 

[DevHead]

Travis,

The recordset passed into the function will have five fields with ID as the foreign key for the table.

Correct me if im reading you wrong here -
You want me to do an insert statement for every record in the recordset?
So 144000 insert statements? If that is what you mean, I have tried that - using ado and using linked tables - and the fastest it inserts is about 11 inserts a second - as oppossed to 24 inserts a second using batching.



Thanks for the reply

 

[DevHead]

Travis now that I think about it is this line confusing you:

str_SQL = "SELECT * FROM TBL1 WHERE ID = 9999999"

All that does is give my recordset the correct schema.
There will never be an ID 999999 so it returns no records but does give my recordset the correct schema so that I don't have to build it implicitly.

The reason I break it into batches of 1000 is because while this line:

rst_RS.UpdateBatch

is executing it hogs all the computer resources. So I could just send it in one big batch but I choose to break it into 1000 so that I can

DoEvents

and let the system do some stuff in between updates.


Thanks

 

[Travis]

That Line did not confuse me. What is confusing me is the Idea of 1000 at a time when you could do

Insert Into [TBL1] (Field1, Field2 ,Field3 ,Field4,Field5)
Select Field1, Field2 ,Field3 ,Field4,Field5 From [TableName of rst_RS]

But now I see that you have two different DB's.

Have you determined how long each line is taking?

You are opening the RS_Flow recordset approx 144 times.
Do you get any improvement if you change the rs_flow recordset to:

str_SQL = "SELECT Top 1 * FROM TBL1 WHERE ID = 9999999"


Do you need to do the update every 1000. This would reduce the # of times you have to open the recordset. Access is notorious for bringing all of the data accross the network and then doing the query against it.

 

[DevHead]

Hey Travis, thanks for your reply.

I have determined time of execution for each line.

rst_RS.UpdateBatch

averages 8 seconds to complete.
All other lines execute in milliseconds. However this in itself doesn't tell me anything, because when I'm building my recordset with .AddNew I'm not sure if ado is just adding a reference to the parameter recordset or actually building my new recordset in memory. So it may be doing the retrieve (a whole lot of retrieves as you mentioned) and building the recordset all inside

rst_RS.UpdateBatch

or it may be just slow inserting records.


I have tried SELECT TOP 1 * ... with no gains in performance.

I am working on dumping the recordset into a linked list and using it instead of the retrieved recordset so that it forces ado to build the recordset when I AddNew

No I don;t have to do it every 1000, it can be 10000 or whatever but I lose the batching gains if I use a small number. I.E. It's close to doing the 144000 insert statements.