pyodbc-批量插入速度非常慢 [英] pyodbc - very slow bulk insert speed

问题描述

使用此表：

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

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

the following code takes 40 seconds to run:

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时如何提高批量插入速度有任何想法吗？

The equivalent code with psycopg2 only takes 3 seconds. I don't think mssql is that much slower than postgresql. Any idea on how to improve the bulk insert speed when using pyodbc?

编辑：在戈尔兹发现之后添加一些注释

Add some notes following ghoerz's discovery

在pyodbc中， executemany 的流程为：

In pyodbc, the flow of executemany is:

• 准备语句


• 每个参数集的循环
  
  
  
  • 绑定参数集
  
  
  • 执行
  
  

  在ceODBC中，执行很多是：

  In ceODBC, the flow of executemany is:

  
  
  • 准备语句
  
  
  • 绑定所有参数
  
  
  • execute
  
  

  推荐答案

  我在将pyODBC插入SQL Server 2008中遇到类似的问题使用executemany（）的数据库。当我在SQL端运行探查器跟踪时，pyODBC正在创建连接，准备参数化的insert语句并对其执行一行。然后它将取消准备该语句，并关闭连接。然后，它为每一行重复该过程。

  I was having a similar issue with pyODBC inserting into a SQL Server 2008 DB using executemany(). When I ran a profiler trace on the SQL side, pyODBC was creating a connection, preparing the parametrized insert statement, and executing it for one row. Then it would unprepare the statement, and close the connection. It then repeated this process for each row.

  我无法在pyODBC中找到任何不这样做的解决方案。我最终切换到ceODBC以连接到SQL Server，并且它正确使用了参数化的语句。

  I wasn't able to find any solution in pyODBC that didn't do this. I ended up switching to ceODBC for connecting to SQL Server, and it used the parametrized statements correctly.

  这篇关于pyodbc-批量插入速度非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！