如何提高Access中ODBC连接表的批量插入性能？

Question

我有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表。

我也尝试过直接导入到连接的表中，但速度非常慢。

我该如何加快这个过程？

- ravi chaudhary

---

Meter_Data 是一个 Oracle 数据库表。 - ravi chaudhary

Answer 1

处理大量插入到Access的ODBC链接表时，这种情况并不罕见。在以下Access查询中的情况如下：

INSERT INTO METER_DATA (MPO_REFERENCE) 
SELECT MPO_REFERENCE FROM tblTempSmartSSP

其中[METER_DATA]是ODBC链接表，[tblTempSmartSSP]是本地（原生）Access表。由于Access数据库引擎必须能够适应各种目标数据库的能力差异，因此对ODBC链接表的处理受到一定限制。不幸的是，尽管单个Access SQL语句可以完成操作，但实际上发送到远程（链接）数据库的内容可能是每行都有一个独立的INSERT（或等效的操作）。可以理解的是，如果本地表包含大量行，则这可能会导致非常缓慢。

选项1：将数据原生批量插入到远程数据库

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

选项2(a)：使用Python和pandas

使用fast_executemany=True的pyodbc可以比在链接表上使用INSERT INTO … SELECT …更快地上传行。有关详细信息，请参见此答案。

选项2(b)：在Access中使用透传查询

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

例如，如果远程数据库是SQL Server（2008或更高版本），则我们可以运行一个Access透传（T-SQL）查询，如下所示：

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

使用一个INSERT语句插入三行数据。

根据这里对另一个早期问题的回答，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;

由于我没有访问Oracle数据库，因此我使用SQL Server对本方法进行测试，使用了一个包含10,000行的[tblTempSmartSSP]本地表。 代码...

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

相比之下，以下代码使用了微软所称的表值构造函数来构建多行插入语句...

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行，所以上述代码比通常情况下更加复杂。)

- Gord Thompson

---

非常出色的Gord。作为一般性说明，人们通常认为使用SQL插入语句比DAO记录集更快 - 除非一个SQL语句可以替换一个记录集循环。然而，对于每一行执行“整个”SQL字符串往往比记录集慢得多。因此，我建议发帖者只需在链接表上尝试简单的记录集。 - Albert D. Kallal

嗨，Gord Thompson，感谢您提供的代码。在运行此代码时，我遇到了一个错误：“ODBC-调用失败”，出现在qdf.Execute dbFailOnError处。 - ravi chaudhary

@ravichaudhary 请提出一个新问题，展示您的实际代码。 - Gord Thompson

请注意，上面的示例在Oracle RDMS上无法正常工作。 Oracle不允许插入语句为：INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9); 相反，您必须使用INSERT ALL语句，然后为valueList内容的每一行编写INTO，即首先在第一行中使用INTO tbl_name (a,b,c) VALUES(1,2,3)，然后在第二行中使用INTO tbl_name (a,b,c) VALUES(4,5,6)，依此类推。请参见http://www.techonthenet.com/oracle/questions/insert_rows.php，这个细节破坏了我的解决方案的性能优势:( - MOLAP

@MOLAP 感谢您的评论。在答案中，我确实声明了测试代码是针对（Microsoft）SQL Server的。它还展示了Oracle的相应INSERT ALL语法，如果有可用的Oracle服务器，我也会进行测试。Oracle语法肯定比SQL Server语法更冗长，这会在一定程度上减轻任何性能优势，但您是否意味着使用这种方法针对Oracle数据库与使用带有（Oracle）链接表的普通INSERT INTO没有显着优势？ - Gord Thompson

Answer 2

如果bcp或BULK COPY不可用，SQL Server的另一个选择：

• METER_DATA是Access中对应于SQL Server上的dbo.METER_DATA的ODBC链接表
• tblTempSmartSSP是包含10,000行的本地Access表

在我的（非常老旧的）测试网络上，此VBA代码运行需要约240秒（大约4分钟）。

Sub upload()
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    Dim t0 As Single
    t0 = Timer
    cdb.Execute "INSERT INTO METER_DATA (MPO_REFERENCE) SELECT MPO_REFERENCE FROM tblTempSmartSSP"
    Debug.Print Timer - t0
End Sub

使用Python和pandas，这段代码可以在大约5秒钟内上传相同的10_000行数据。

from time import perf_counter

import pandas as pd
import sqlalchemy as sa

acc_engine = sa.create_engine(
    "access+pyodbc://@meter_data"
)
sql_engine = sa.create_engine(
    "mssql+pyodbc://scott:tiger^5HHH@mssql_199",
    fast_executemany=True,
)

t0 = perf_counter()
df = pd.read_sql_query("SELECT MPO_REFERENCE FROM tblTempSmartSSP", acc_engine)
df.to_sql("METER_DATA", sql_engine, schema="dbo", if_exists="append", index=False)
print(perf_counter() - t0)

需求：

• Python
• pandas
• sqlalchemy-access（披露：我是该项目的维护者。）

请注意，SQLAlchemy支持的其他数据库（例如PostgreSQL）可能比Access中的ODBC链接表提供更大的性能改进。

- Gord Thompson

Answer 3

你必须导入所有列吗？也许你想留出空列（如果有的话），以及那些对业务目的并非绝对必要的列。

- user2290801

---

我需要所有的列，最多只有5列。 我也试图导入小数据，像6000行，但它花费了一个多小时。 - ravi chaudhary

刚想到一个办法；尝试使用以下方法来运行你的操作查询。也许它很慢是因为你需要更多的声明： - user2290801

你是将文件导入到现有表中吗？而且，你是唯一的用户吗？创建一个表需要更多时间，如果你正在导入，你不希望其他人在数据库中。此外，在复制列之前运行一个查询以删除空值。空值会减慢速度。这只是一些想法。 - user2290801

我正在将数据导入现有的表格中，使用的是数据库。为了删除空值，我已经运行了查询，但是当我尝试从MS Access表格插入到Oracle数据库表格中，会花费很长时间，即Meter_data表格。 - ravi chaudhary

Answer 4

抱歉，我忘记包含代码了：

Option Compare Database
Option Explicit

Public Function Run_Safe_SQL(strSQL)
On Error GoTo Error_Handler
Dim db As DAO.Database

   Set db = CurrentDb()
   db.Execute strSQL, dbFailOnError
   DBEngine.Idle dbRefreshCache
'   DoEvents

Exit_Here:
   'Cleanup
   Set db = Nothing
   strSQL = ""
   Exit Function

Error_Handler:
    MsgBox Err.Description & " " & Err.Number

End Function

- user2290801