Increase Ms Access Insert Performance

问题:

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.

回答1:

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.

回答2:

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

回答3:

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.