Increase Ms Access Insert Performance

Question

I am using MS Access 2010, split in front end / back end; on a network drive (WAN) with 16+ table with one table of users (1.3 Million) which is mostly used for user information and is not insert heavy and few other tables, which will receive upto 2000+ inserts daily.

I have been able to optimize most of the read/select queries. Although 1 chunk of my code looks as below. This can be used for upto 2000 iterations daily.

Do Until rec.EOF
    Dim vSomeId As Integer
    vSomeId = rec!SomeId

    'StrSQL = StrSQL & "INSERT INTO TransportationDetails ( TransportationId, SomeId)" & _
        '"VALUES(" & vTransportationId & ", " & vSomeId & ");"

    StrSQL = "INSERT INTO TransportationDetails ( TransportationId, SomeId)" & _
        "VALUES(" & vTransportationId & ", " & vSomeId & ");"

    DoCmd.SetWarnings False
    DoCmd.RunSQL (StrSQL)
    DoCmd.SetWarnings True


    rec.Edit
    rec!SomeBoolean = rec!SomeOtherBoolean 
    rec.Update
    rec.MoveNext
Loop

My objective here, is to reduce the number of calls to the db to insert all the values. and MS ACCESS does NOT support having more than 1 query in a statement, as I tried in the commented part of the code. I also think the recordset upate method is a lot time consuming, and if any one can suggest a better way to update the recordset.

Is there any way I can trick Access to insert & Update in less hits to db through SQL Queries, or any other access feature. Or optimize in anyway, It can take up to 30 mins some time. Decreasing it to At least 2 - 5 mins will be appropriate.

P.S. I can not switch to SQL Server, It is JUST NOT POSSIBLE. I am aware it can be done in way more optimal way through sql server and Access shouldn't be used for WAN, but I don't have that option.

Solution: I went with Andre's and Jorge's solution. The time decreased by 17 times. Although Albert's Answer is correct too as I found my main issue was with the sql statements in a loop. Changing the edits in the recordset to sql didnt impact much on the time factor.

You should use an Insert Select query. Do it with your `REC` query in conjuntion with your insert query like `INSERT INTO TransportationDetails ( TransportationId, SomeId) select " & vTransportationId & ", someId from rectable where ....` You would call this insert just once — Jorge Campos, Oct 08 '15 at 06:00
I have a access query that is a recordset for the "rec" recordset. Can I run the select statement of this query, or I will have to rewrite the query in this sql statement? — Segmentation Fault, Oct 08 '15 at 06:17
If you use it in another places you would have to create a new one for the inserts, but if it exists just to this insert you just need one as the answer given below based on my sugestion. — Jorge Campos, Oct 08 '15 at 11:25
Thank you for the prompt comments. The performance increased by 17 times. — Segmentation Fault, Oct 09 '15 at 07:59

score 8 · Answer 1 · answered Oct 08 '15 at 20:43

8

I should point out that in the case of inserting rows, you will find FAR better performance by using a recordset. A SQL “action” query will ONLY perform better if you operating on a set of data. The instant you are inserting rows, then you don’t have a “set” insert, and using a DAO recordset will result in MUCH better performance (a factor of 10 to 100 times better).

answered Oct 08 '15 at 20:43

Albert D. Kallal

42,205
3
34
51

2

Good advice (as usual). I just did a quick test and 2000 SQL INSERTs took 24 seconds while 2000 DAO.Recordset AddNew inserts into the same table took less than 0.2 seconds. – Gord Thompson Oct 08 '15 at 23:04
1

I tested insert using the marked answer on 1447 inserts and it took me less than 2 seconds. My previous way of looping through and having 1 sql insert statement for each was taking 35 seconds. – Segmentation Fault Oct 09 '15 at 07:56
1

Although I was editing those 1447 records through the recordset and I didnt found any significant improvement after changing it from the recordset to a single sql statement. – Segmentation Fault Oct 09 '15 at 07:57
1

Above comments and results quite much matches the general experience of the Access community. As noted, if you can do a update using a single SQL statement, then you see good performance, but in general not a lot more then a loop+edit. If you turn off row locking, then you will again gain some additional performance gains, but also reduce bloat of the file by large amounts when doing many edits. – Albert D. Kallal Oct 10 '15 at 00:02
@AlbertD.Kallal How do you turn off row locking? Is it off by default when using a recordset? – Maxter Nov 29 '19 at 20:46
The row locking setting is active or not regardless if you set or use any locking in a form or code. the option is specifly called [x] use row locking. This works by expanding all records to the size of one data page, so the result is a fake row lock because access has page locking, but not true row locking. It is this huge expanding issue that causes this issue. File->options->client settings->scroll down to [x] Open databases by using record-locking. You have to exit + re-start (so, un-check this option - it is rare required). – Albert D. Kallal Nov 29 '19 at 21:20
I'd like to add it depends on the structure of the code. I used DAO.Recordset but I open the table with 6 million+ records, write a record and then close it, but this process happens thousands of times and bogs down the process. – dcary Mar 01 '23 at 02:39

score 4 · Accepted Answer · answered Oct 08 '15 at 06:48

If you have now

S = "SELECT SomeId, SomeBoolean, SomeOtherBoolean " & _
    "FROM recTable WHERE someCriteria"
Set rec = DB.OpenRecordset(S)

change your statements into

"INSERT INTO TransportationDetails (TransportationId, SomeId) " & _
"SELECT " & vTransportationId & ", SomeId " & _
"FROM recTable WHERE someCriteria"

and

"UPDATE recTable SET SomeBoolean = SomeOtherBoolean WHERE someCriteria"

For performance, avoid looping over Recordsets where possible. Use SQL statements that operate on whole sets instead.

score 0 · Answer 3 · answered Oct 07 '19 at 12:30

I recently ran into a problem where I have to import 200,000 records into 12 Access tables every 6 hours. This took too long as I was inserting each record one at a time.

I picked up a tip from a colleague who suggested using Linked Tables.

So I set up a linked table in my Access Database which was linked to a semi-colon delimited text file.

My program then created that semi-colon delimited text file every 6 hours.

You then select from the linked table into the table needed and that table is created.

This made my process immensely faster and I would definitely suggest it as an option.

Increase Ms Access Insert Performance

3 Answers3

Linked