提高女士访问插入性能 [英] Increase Ms Access Insert Performance

问题描述

我正在使用MS Access 2010，分为前端/后端；在具有16个以上表的网络驱动器(WAN)上，其中一个表是用户(130万)，该表主要用于提供用户信息，并且不插入过多的其他表，而将接收每天最多插入2000次以上.

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.

我已经能够优化大多数读取/选择查询.虽然我的代码中有1个块如下所示.每天最多可使用2000次迭代.

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

我在这里的目标是减少对数据库的调用次数，以插入所有值.并且 MS ACCESS不支持在一个语句中进行超过1个查询，就像我在代码的注释部分中尝试过的那样.我还认为记录集更新方法非常耗时，并且如果有人可以提出一种更好的更新记录集的方法.

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.

是，我可以通过任何方法欺骗Access来插入&通过SQL查询或任何其他访问功能，在更少的匹配中更新到数据库.或以任何方式进行优化，这可能需要长达30分钟的时间.将其减少到至少2-5分钟将是适当的.

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. 我无法切换到SQL Server，这是永远不可能.我知道可以通过sql服务器以更优化的方式完成此操作，并且Access不应该用于WAN，但是我没有这种选择.

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.

解决方案: 我选择了安德烈(Andre)和豪尔赫(Jorge)的解决方案.时间减少了17倍.尽管阿尔伯特的答案也是正确的，因为我发现我的主要问题是循环中的sql语句.将记录集中的编辑更改为sql不会对时间因素产生很大影响.

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.

推荐答案

如果您现在拥有

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

将您的陈述更改为

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

和

"UPDATE recTable SET SomeBoolean = SomeOtherBoolean WHERE someCriteria"

为了提高性能，请避免在可能的情况下遍历记录集.而是使用对整个集合进行运算的SQL语句.

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

这篇关于提高女士访问插入性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！