What is Faster for Inserting Records Single SQL insert or Recordset Addnew? (1 Viewer)

[MajP]

I am running a loop in code that is generating values for inputs into a table. Based on the starting values, the code generates from 10s to maybe low thousands of records.
I can either open a recordset and do individual add new calls or I can generate the sql string in vba and execute. I guess I could also do a parameterized query def. Which is faster, and is this difference significant. I assume it may depend on how many records get inputted, in that there may be some initial overhead for opening the recordset which may be inconsequential for a bigger loop. Thanks.

 

[Pat Hartman]

.AddNew would be faster than executing an append query for each row. There is a lot of overhead associated with compiling and calculating an execution plan for a query that is created on the fly. With the open recordset, that happens only once rather than for each row.

 

[MajP]

Thanks. How about using a parameterized query definition? Does that make a difference?

 

[CJ_London]

Which is faster, and is this difference significant. I assume it may depend on how many records get inputted,

Also depends on byte size of records and amount of indexing.

assuming you are talking about a single sql statement to create multiple rows as a rule the sql is faster than vba looping. In my experience there is no significant difference between executing a sql string or executing a query.

 

[561414]

AddNew is so so so much faster.

Check the attached. Play on top to test other methods, I'm interested in knowing other approaches.

Cheers

 

[Pat Hartman]

Thanks. How about using a parameterized query definition? Does that make a difference?

It should since the stored execution plan would be used if the BE is Jet/ACE but if the BE is SQL Server, the server still needs to create the execution plan. This stuff takes an unmeasurably small amount of time. It is only when you run them thousands of times that you can see the difference.

Colin has done a lot of timing studies on a huge table. I don't recall if this is one of them. My money is still on .AddNew.

I had a pretty large import that came weekly. It was a reference table that got truncated and then refilled. The BE was SQL Server. It was about 200,000 rows and I loaded the table with a loop. Only took a couple of minutes. I never tried the separate append query method. The loop was fast enough given the volume of data.

 

[MajP]

Thanks. That was an easy test. I ran the parameterized query and that was twice as fast as the db.execute but still orders of magnitude slower than the addnew. (5k records entering a single numeric field)

CurrentDB.Execute took 1.456 seconds
CurrentDb.OpenRecordset took .0017 seconds
qdf took 0.74045 seconds

 

[Pat Hartman]

In my experience there is no significant difference between executing a sql string or executing a query.

For a single execution, the time difference would not be measurable but from inside a loop where you are running thousands of queries, the difference is significant.

Thanks. That was an easy test.

Does that mean I win

 

[The_Doc_Man]

A little logic would answer the question. When you use a query for single-record insertion, you have to establish the connection and open the recordset each time. AND then close the recordset and close that connection. It is the overhead that eats your lunch. With an already opened recordset, the connection to the DB and the recordset are already open and ready for use. No preparation overhead. And if you are leaving it open for the next iteration, no closure overhead either. I would think that even for SQL server (vice JET/SQL), the same would be true.

 

[ebs17]

I'm interested in knowing other approaches

The comparison that actually makes sense would be between the recordset loop and an append query about everything. SQL has its strength in bulk data processing (all at once).

INSERT INTO
   somedata(
      some_number
   )
SELECT DISTINCT
   S.some_number
FROM
   somedata AS S

Again, the recordset loop can be faster than the query in some cases.

 

[amorosik]

Thanks. That was an easy test. I ran the parameterized query and that was twice as fast as the db.execute but still orders of magnitude slower than the addnew. (5k records entering a single numeric field)

CurrentDB.Execute took 1.456 seconds
CurrentDb.OpenRecordset took .0017 seconds
qdf took 0.74045 seconds

Can you post the test routine?

 

[isladogs]

AddNew is so so so much faster.

Check the attached. Play on top to test other methods, I'm interested in knowing other approaches.

Cheers

One of the reasons that the first test where you execute the append query repeatedly was so slow is because each time it is run Access has to determine the CurrentDb

You can significantly speed up that test by instead using

Dim Db As DAO.Database
Set Db = CurrentDb

'then in the loop use
...Db.Execute ...

Doing that means Access will use the same instance of CurrentDb each time the loop is run rather than creating it 5000 times
Even so, it will still be slower than the recordset for this particular very limited test where a single record is added each time
You can do the same for the AddNew test but it will have minimal effect there - you should be able to see why

You will get very different outcomes if you add multiple records each time

There are also several other approaches that are much faster than CurrentDb. See

Speed Comparison Tests 2

This article compares the speed of four methods used to reference the current database - CurrentDb vs DBEngine(0)(0) vs a custom property ThisDb and a custom function CurDb. It is the second in a series of articles discussing various tests done to compare the efficiency of different approaches...

www.isladogs.co.uk

 

[amorosik]

Speed Comparison Test2
Congratulations, wonderful article
Very educational (at least for me)

 

[MajP]

Although not a completely fair test, switching from CurrentDB.Execute to opening a recordset once and running addnew made a huge difference. A fairer test would be opening the db outside the loop and doing a db.execute. However as @isladogs points out, that only makes the solution marginally faster, not orders of magnitude faster.

The database I was working is here

Best approach to calculate if required dose possible from multiple different stock sizes

We have a manual process where if a 250mg dose of Drug A is required, we give 120mg + 75mg + 55mg as 3 separate doses, or (2 x 75mg) + 100mg, or (2 x 80mg) + 90mg What I'm after is the best way to calculate if the available dose is divisible by any combination of 120mg, 100mg, 90mg, 80mg, 75mg...

www.access-programmers.co.uk

In this case you cannot do a bulk insert at once because the values are generated in code through execution of the algorithm. In the OPs case they were looking at problems of dosing a prescription through a variety of different size pills. This is solved using a spanning tree to generate feasible patterns. Applicable to other similar problems. For the OPs problem of filling a 250 mg dose there was only 29 potential patterns, and 3 feasible patterns. However by simply moving the order size from 250 to 2000 the potential solutions are in the 10s of thousands. In my testing with currentdb.execute at 2000mg the system basically locked up and with the addnew was less than a second.

 

[561414]

One of the reasons that the first test where you execute the append query repeatedly was so slow is because each time it is run Access has to determine the CurrentDb

I thought I was being fair when I did not declare the database in either of the subs. Thanks for pointing it out. Since this is an approaches thread, I made another attempt but being explicit this time, I achieved a similar result to declaring it, kinda faster actually:

Workspaces(0).Databases(0).Execute _
  "INSERT INTO somedata (some_number) VALUES (" & i & ")"

The third result, ExplicitDb.Execute in the debug window is Workspaces(0).Databases(0).Execute "...", that is, no declarations. Here's the subs to avoid confusion:

Sub testInsert2()
   
    ' start
    beginTime = MicroTimer

    Dim predeclaredDb As DAO.Database
    Set predeclaredDb = CurrentDb
   
    For i = 1 To 5000
        predeclaredDb.Execute "INSERT INTO somedata (some_number) VALUES (" & i & ")"
    Next i
   
    ' finish
    endTime = MicroTimer
   
    Debug.Print "predeclaredDb.Execute took " & endTime - beginTime & " seconds"

End Sub

Sub testInsert3()
   
    ' start
    beginTime = MicroTimer

   
    For i = 1 To 5000
        Workspaces(0).Databases(0).Execute "INSERT INTO somedata (some_number) VALUES (" & i & ")"
    Next i
   
    ' finish
    endTime = MicroTimer
   
    Debug.Print "ExplicitDB.Execute took " & endTime - beginTime & " seconds"

End Sub

The difference is circumstantial though. I'd say these two take the same time.
Dim db As DAO.Database: Set db = CurrentDb: db.Execute ...
Workspaces(0).Databases(0).Execute ...

There are also several other approaches that are much faster than CurrentDb. See

Speed Comparison Tests 2

This article compares the speed of four methods used to reference the current database - CurrentDb vs DBEngine(0)(0) vs a custom property ThisDb and a custom function CurDb. It is the second in a series of articles discussing various tests done to compare the efficiency of different approaches...

www.isladogs.co.uk

Thanks, the variety of approaches in your article is very helpful.