如何提高对 Access 中 ODBC 链接表的批量插入的性能?

2021-12-03 00:00:00 vba ms-access odbc oracle ms-access-2003

我要导入 CSV 和 TXT 文件.我将文件导入 Access，然后将记录插入到链接的 Oracle 表中.每个文件大约有 300 万行，这个过程需要很长时间才能完成.

I have CSV and TXT files to import. I am importing the files into Access and then inserting the records into a linked Oracle table. Each file has around 3 million rows and the process is taking a long time to complete.

导入 Access 非常快，但插入链接的 Oracle 表需要很长时间.

Importing into Access is very fast, but inserting into the linked Oracle table is taking an extremely long time.

这是我目前使用的流程:

Here is the process I am currently using:

DoCmd.TransferText acImportFixed, "BUSSEP2014 Link Specification", "tblTempSmartSSP", strFName, False
db.Execute "INSERT INTO METER_DATA ([MPO_REFERENCE]) SELECT MPO_REFERENCE FROM tblTempSmartSSP;"`

tblTempSmartSSP 是一个访问表，METER_DATA 是一个链接的 Oracle 表

tblTempSmartSSP is an Access Table and METER_DATA is a linked Oracle table

我也尝试过直接导入链接表，但速度也很慢.

I also tried direct import to linked table and that was also very slow.

如何加快流程?

推荐答案

在 Access 中处理对 ODBC 链接表的批量 INSERT 时，这种情况并不少见.在以下访问查询的情况下

This situation is not uncommon when dealing with bulk INSERTs to ODBC linked tables in Access. In the case of the following Access query

INSERT INTO METER_DATA (MPO_REFERENCE) 
SELECT MPO_REFERENCE FROM tblTempSmartSSP

其中 [METER_DATA] 是 ODBC 链接表，而 [tblTempSmartSSP] 是本地(本机)Access 表，ODBC 的智能程度在某种程度上受到限制，因为它必须能够适应各种目标数据库能力可能会有很大差异.不幸的是，这通常意味着尽管只有一条 Access SQL 语句，但实际发送到远程(链接)数据库的内容却是一个单独的 INSERT(或等效项)对于本地表中的每一行.可以理解的是，如果本地表包含大量行，这可能会非常慢.

where [METER_DATA] is an ODBC linked table and [tblTempSmartSSP] is a local (native) Access table, ODBC is somewhat limited in how clever it can be because it has to be able to accommodate a wide range of target databases whose capabilities may vary greatly. Unfortunately, it often means that despite the single Access SQL statement what actually gets sent to the remote (linked) database is a separate INSERT (or equivalent) for each row in the local table. Understandably, that can prove to be very slow if the local table contains a large number of rows.

选项 1:本地批量插入到远程数据库

所有数据库都有一种或多种用于批量加载数据的本地机制:Microsoft SQL Server 有bcp"和BULK INSERT，Oracle 有SQL*Loader".这些机制针对批量操作进行了优化，通常会提供显着的速度优势.事实上，如果数据需要导入 Access 并在传输到远程数据库之前进行按摩"，将修改后的数据转储回文本文件，然后将其批量导入远程数据库仍然会更快.

All databases have one or more native mechanisms for the bulk loading of data: Microsoft SQL Server has "bcp" and BULK INSERT, and Oracle has "SQL*Loader". These mechanisms are optimized for bulk operations and will usually offer significant speed advantages. In fact, if the data needs to be imported into Access and "massaged" before being transferred to the remote database it can still be faster to dump the modified data back out to a text file and then bulk import it into the remote database.

选项 2:在 Access 中使用传递查询

如果批量导入机制不是一个可行的选择，那么另一种可能性是在 Access 中构建一个或多个传递查询以使用可以一次插入多行的 INSERT 语句上传数据.

If the bulk import mechanisms are not a feasible option, then another possibility is to build one or more pass-through queries in Access to upload the data using INSERT statements that can insert more than one row at a time.

例如，如果远程数据库是 SQL Server(2008 或更高版本)，那么我们可以像这样运行 Access pass-through (T-SQL) 查询

For example, if the remote database was SQL Server (2008 or later) then we could run an Access pass-through (T-SQL) query like this

INSERT INTO METER_DATA (MPO_REFERENCE) VALUES (1), (2), (3)

用一个 INSERT 语句插入三行.

to insert three rows with one INSERT statement.

根据之前另一个问题的答案此处，Oracle 的相应语法为

According to an answer to another earlier question here the corresponding syntax for Oracle would be

INSERT ALL
    INTO METER_DATA (MPO_REFERENCE) VALUES (1)
    INTO METER_DATA (MPO_REFERENCE) VALUES (2)
    INTO METER_DATA (MPO_REFERENCE) VALUES (3)
SELECT * FROM DUAL;

我使用具有 10,000 行的本机 [tblTempSmartSSP] 表在 SQL Server 上测试了这种方法(因为我无法访问 Oracle 数据库).代码...

I tested this approach with SQL Server (as I don't have access to an Oracle database) using a native [tblTempSmartSSP] table with 10,000 rows. The code ...

Sub LinkedTableTest()
    Dim cdb As DAO.Database
    Dim t0 As Single

    t0 = Timer
    Set cdb = CurrentDb
    cdb.Execute _
            "INSERT INTO METER_DATA (MPO_REFERENCE) " & _
            "SELECT MPO_REFERENCE FROM tblTempSmartSSP", _
            dbFailOnError
    Set cdb = Nothing
    Debug.Print "Elapsed time " & Format(Timer - t0, "0.0") & " seconds."
End Sub

... 在我的测试环境中执行大约需要 100 秒.

... took approximately 100 seconds to execute in my test environment.

相比之下，下面的代码构建了如上所述的多行插入(使用 Microsoft 所谓的 表值构造器) ...

By contrast the following code, which builds multi-row INSERTs as described above (using what Microsoft calls a Table Value Constructor) ...

Sub PtqTest()
    Dim cdb As DAO.Database, rst As DAO.Recordset
    Dim t0 As Single, i As Long, valueList As String, separator As String

    t0 = Timer
    Set cdb = CurrentDb
    Set rst = cdb.OpenRecordset("SELECT MPO_REFERENCE FROM tblTempSmartSSP", dbOpenSnapshot)
    i = 0
    valueList = ""
    separator = ""
    Do Until rst.EOF
        i = i + 1
        valueList = valueList & separator & "(" & rst!MPO_REFERENCE & ")"
        If i = 1 Then
            separator = ","
        End If
        If i = 1000 Then
            SendInsert valueList
            i = 0
            valueList = ""
            separator = ""
        End If
        rst.MoveNext
    Loop
    If i > 0 Then
        SendInsert valueList
    End If
    rst.Close
    Set rst = Nothing
    Set cdb = Nothing
    Debug.Print "Elapsed time " & Format(Timer - t0, "0.0") & " seconds."
End Sub

Sub SendInsert(valueList As String)
    Dim cdb As DAO.Database, qdf As DAO.QueryDef

    Set cdb = CurrentDb
    Set qdf = cdb.CreateQueryDef("")
    qdf.Connect = cdb.TableDefs("METER_DATA").Connect
    qdf.ReturnsRecords = False
    qdf.sql = "INSERT INTO METER_DATA (MPO_REFERENCE) VALUES " & valueList
    qdf.Execute dbFailOnError
    Set qdf = Nothing
    Set cdb = Nothing
End Sub

... 需要 1 到 2 秒才能产生相同的结果.

... took between 1 and 2 seconds to produce the same results.

(T-SQL 表值构造器一次只能插入 1000 行，因此上面的代码比其他情况要复杂一些.)

(T-SQL Table Value Constructors are limited to inserting 1000 rows at a time, so the above code is a bit more complicated than it would be otherwise.)

相关文章