pyodbc-非常慢的批量插入速度

| 使用此表：

CREATE TABLE test_insert (
    col1 INT,
    col2 VARCHAR(10),
    col3 DATE
)

以下代码需要40秒钟才能运行：

import pyodbc

from datetime import date


conn = pyodbc.connect(\'DRIVER={SQL Server Native Client 10.0};\'
    \'SERVER=localhost;DATABASE=test;UID=xxx;PWD=yyy\')

rows = []
row = [1, \'abc\', date.today()]
for i in range(10000):
    rows.append(row)

cursor = conn.cursor()
cursor.executemany(\'INSERT INTO test_insert VALUES (?, ?, ?)\', rows)

conn.commit()

与psycopg2等效的代码只需3秒钟。我不认为mssql比postgresql慢得多。关于使用pyodbc时如何提高批量插入速度的任何想法？编辑：在戈尔兹的发现后添加一些注释在pyodbc中，executemany的流为：准备陈述为每组参数循环绑定参数集执行在ceODBC中，executemany的流为：准备陈述绑定所有参数执行

已邀请:

6 个回复

剿畦缄饥小

pyODBC使用executemany（）插入SQL Server 2008 DB时遇到类似的问题。当我在SQL端运行探查器跟踪时，pyODBC正在创建连接，准备参数化的insert语句并对其执行一行。然后它将取消准备该语句，并关闭连接。然后，对每一行重复此过程。我无法在pyODBC中找到没有做到这一点的解决方案。我最终切换到ceODBC以连接到SQL Server，并且它正确使用了参数化语句。

壬驴拿歪竞

尝试使用ceODBC和mxODBC，而且速度都很慢。最终在http://www.ecp.cc/pyado.html的帮助下进行了adodb连接。总运行时间提高了6倍！

comConn = win32com.client.Dispatch(r\'ADODB.Connection\')
DSN = \'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s%s\' %(dbDIR,dbOut)
comConn.Open(DSN)

rs = win32com.client.Dispatch(r\'ADODB.Recordset\')
rs.Open(\'[\' + tblName +\']\', comConn, 1, 3)

for f in values:
    rs.AddNew(fldLST, f)

rs.Update()

联课

与在Postgres（psycopg2）和Oracle（cx_Oracle）中进行批量操作相比，尝试使用pyodbc在MSSQL中插入+ 2M行要花费大量的时间。我没有使用BULK INSERT操作的特权，但是能够使用以下方法解决问题。许多解决方案正确地建议使用fast_executemany，但是，正确使用它有一些技巧。首先，我注意到在connect方法中将autocommit设置为True时，pyodbc在每一行之后提交，因此必须将其设置为False。我还观察到一次插入超过20k行时出现非线性减慢，即插入10k行是亚秒级，但是50k超过20s。我认为事务日志变得很大，并且使整个过程变慢。因此，必须在每个块之后对插入和提交进行大块处理。我发现每块5k行提供了良好的性能，但这显然取决于许多因素（数据，机器，数据库配置等...）。

import pyodbc

CHUNK_SIZE = 5000

def chunks(l, n):
    \"\"\"Yield successive n-sized chunks from l.\"\"\"
    for i in xrange(0, len(l), n): #use xrange in python2, range in python3
        yield l[i:i + n]

mssql_conn = pyodbc.connect(driver=\'{ODBC Driver 17 for SQL Server}\',
                            server=\'<SERVER,PORT>\',
                            timeout=1,
                            port=<PORT>,
                            uid=<UNAME>, 
                            pwd=<PWD>,
                            TDS_Version=7.2,
                            autocommit=False) #IMPORTANT

mssql_cur = mssql_conn.cursor()
mssql_cur.fast_executemany = True #IMPORTANT

params = [tuple(x) for x in df.values]

stmt = \"truncate table <THE TABLE>\"
mssql_cur.execute(stmt)
mssql_conn.commit()

stmt = \"\"\"
INSERT INTO <THE TABLE> (field1...fieldn) VALUES (?,...,?)
\"\"\"
for chunk in chunks(params, CHUNK_SIZE): #IMPORTANT
    mssql_cur.executemany(stmt, chunk)
    mssql_conn.commit()