如何增加对于批量插入到ODBC链接表在Access中的表现？ [英] How to increase performance for bulk INSERTs to ODBC linked tables in Access?

问题描述

我有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.

导入到访问速度非常快，但插入链接的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.

我怎么能加快这一进程？

How can I speed up the process?

推荐答案

在与批量插入在访问ODBC链接表时，这种情况并不少见。在以下Access查询的情况下

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有所如何巧它可以是有限的，因为它必须能够适应广泛的目标数据库的的能力会有很大的改变。不幸的是，这往往意味着，尽管单一访问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：本机批量插入到远程数据库

所有的数据库都有一个或批量加载数据的多个天然机制：微软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服务器（2008或更高版本），那么我们就可以运行一个访问直通（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;

我使用本机[tblTempSmartSSP表10,000行测试，这种方法与SQL Server（因为我没有访问Oracle数据库）。在code ...

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.

相比之下，以下code，上述（使用微软称之为的表值构造）...

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行，所以上述code是一个比较复杂一点比起来，否则。）

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

这篇关于如何增加对于批量插入到ODBC链接表在Access中的表现？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！