oracle - 如何提高对Access中ODBC链接表的批量INSERT的性能？

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

导入到Access的速度非常快，但是插入链接的Oracle表要花费很长时间。

这是我当前正在使用的过程:

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

tblTempSmartSSP是 Access 表，而METER_DATA是链接的Oracle表

我也尝试了直接导入到链接表，这也非常慢。

我如何加快这一过程？

最佳答案

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

INSERT INTO METER_DATA (MPO_REFERENCE)
SELECT MPO_REFERENCE FROM tblTempSmartSSP

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

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

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

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

如果批量导入机制不可行，则另一种可能性是在Access中构建一个或多个直通查询，以使用可以一次插入多行的INSERT语句上载数据。

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

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

用一条INSERT语句插入三行。

根据对另一个较早问题here的回答，Oracle的相应语法为

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行的 native [tblTempSmartSSP]表在SQL Server(由于我无权 Access Oracle数据库)中测试了这种方法。编码 ...

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秒。

相比之下，以下代码如上所述构建了多行INSERT(使用Microsoft所谓的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秒才能产生相同的结果。

(T-SQL表值构造函数仅限于一次插入1000行，因此，上面的代码比其他代码要复杂一些。)

关于oracle - 如何提高对Access中ODBC链接表的批量INSERT的性能？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25863473/