加快从 pyodbc 插入 SQL Server 的速度

In python，我有一个从一个数据库中选择数据的过程（Redshift via psycopg2），然后将该数据插入到SQL Server (via pyodbc）。我选择执行读/写而不是读/平面文件/加载，因为行数约为每天 100,000 行。似乎更容易简单地连接和插入。然而，插入过程很慢，需要几分钟。

有没有更好的方法使用 Pyodbc 将数据插入 SQL Server？

select_cursor.execute(output_query)

done = False
rowcount = 0

while not done:
    rows = select_cursor.fetchmany(10000)

    insert_list = []

    if rows == []:
        done = True
        break

    for row in rows:
        rowcount += 1

        insert_params = (
            row[0], 
            row[1], 
            row[2]
            )

        insert_list.append(insert_params)            

    insert_cnxn = pyodbc.connect('''Connection Information''')

    insert_cursor = insert_cnxn.cursor()

    insert_cursor.executemany("""
        INSERT INTO Destination (AccountNumber, OrderDate, Value)
        VALUES (?, ?, ?)
        """, insert_list)

    insert_cursor.commit()
    insert_cursor.close()
    insert_cnxn.close()

select_cursor.close()
select_cnxn.close()

UPDATE:pyodbc 4.0.19 添加了Cursor#fast_executemany该选项可以通过避免下面描述的行为来极大地提高性能。看这个答案了解详情。

您的代码确实遵循正确的形式（除了其他答案中提到的一些小调整），但请注意，当 pyodbc 执行.executemany它实际上做的是提交一个单独的sp_prepexec对于每个单独的行。也就是说，对于代码

sql = "INSERT INTO #Temp (id, txtcol) VALUES (?, ?)"
params = [(1, 'foo'), (2, 'bar'), (3, 'baz')]
crsr.executemany(sql, params)

SQL Server 实际上执行以下操作（由 SQL Profiler 确认）

exec sp_prepexec @p1 output,N'@P1 bigint,@P2 nvarchar(3)',N'INSERT INTO #Temp (id, txtcol) VALUES (@P1, @P2)',1,N'foo'
exec sp_prepexec @p1 output,N'@P1 bigint,@P2 nvarchar(3)',N'INSERT INTO #Temp (id, txtcol) VALUES (@P1, @P2)',2,N'bar'
exec sp_prepexec @p1 output,N'@P1 bigint,@P2 nvarchar(3)',N'INSERT INTO #Temp (id, txtcol) VALUES (@P1, @P2)',3,N'baz'

所以，对于一个.executemany“批量”10,000 行

• 执行 10,000 次单独插入，
• 到服务器的往返次数为 10,000 次，并且
• 发送相同的 SQL 命令文本（INSERT INTO ...) 10,000 次。

It is possible让 pyodbc 发送一个初始的sp_prepare然后做一个.executemany呼叫sp_execute，但本质上.executemany你仍然会做10,000sp_prepexec调用，只是执行sp_execute代替INSERT INTO ...。如果 SQL 语句相当长且复杂，这可以提高性能，但对于像您问题中的示例这样的简短语句，可能不会产生太大影响。

人们还可以发挥创意并构建“表值构造函数”，如下所示这个答案，但请注意，当本机批量插入机制不是可行的解决方案时，它仅作为“B 计划”提供。