sql server - libreria - pyodbc-velocidad de inserción masiva muy lenta

sql server connector python

Question

Con esta tabla:

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

El siguiente código tarda 40 segundos en ejecutarse:

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

El código equivalente con psycopg2 solo toma 3 segundos. No creo que mssql sea mucho más lento que postgresql. ¿Alguna idea sobre cómo mejorar la velocidad de inserción masiva cuando se usa pyodbc?

EDITAR: Añadir algunas notas tras el descubrimiento de ghoerz

En pyodbc, el flujo de executemany es:

• preparar declaración
• bucle para cada conjunto de parámetros
  • enlazar el conjunto de parámetros
  • ejecutar

En ceODBC, el flujo de executemany es:

• preparar declaración
• atar todos los parámetros
• ejecutar

Answer 1

Escribí datos en un archivo de texto y luego invocé la utilidad BCP. Mucho más rápido. De unos 20 a 30 minutos a unos pocos segundos.

Answer 2

Estaba teniendo un problema similar con la inserción de pyODBC en una base de datos de SQL Server 2008 usando executemany (). Cuando ejecuté un rastreo del generador de perfiles en el lado de SQL, pyODBC estaba creando una conexión, preparando la instrucción de inserción parametrizada y ejecutándola por una fila. Entonces desaprueba la declaración y cierra la conexión. Luego repitió este proceso para cada fila.

No pude encontrar ninguna solución en pyODBC que no hiciera esto. Terminé cambiando a ceODBC para conectarme a SQL Server, y usé las declaraciones parametrizadas correctamente.

Answer 3

Estaba usando pypyODBC w / python 3.5 y Microsoft SQL Server Management Studio. Una tabla particular (~ 70K filas w / 40 vars) tardaba 112 segundos en INSERTAR utilizando el método .executemany () con pypyodbc.

Con ceODBC tardó 4 segundos.

Answer 4

Intenté tanto ceODBC como mxODBC y ambos también fueron dolorosamente lentos. Terminé yendo con una conexión adodb con ayuda de http://www.ecp.cc/pyado.html . Tiempo de ejecución total mejorado por un factor de 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()

Answer 5

Intentar insertar + 2M filas en MSSQL usando pyodbc tomó una cantidad de tiempo absurdamente larga en comparación con las operaciones masivas en Postgres (psycopg2) y Oracle (cx_Oracle). No tenía los privilegios para usar la operación BULK INSERT, pero pude resolver el problema con el método a continuación.

Muchas soluciones sugirieron correctamente fast_executemany, sin embargo, hay algunos trucos para usarlo correctamente. Primero, me di cuenta de que pyodbc se estaba cometiendo después de cada fila cuando autocommit se configuraba como Verdadero en el método de conexión, por lo tanto, esto debía configurarse como Falso También observé una ralentización no lineal al insertar más de ~ 20k filas a la vez, es decir, la inserción de 10k filas fue subsiguiente, pero 50k fue más de 20 s. Supongo que el registro de transacciones se está volviendo bastante grande y está ralentizando todo el proceso. Por lo tanto, debe fragmentar su inserción y cometer después de cada fragmento. Encontré 5k filas por trozo de buen rendimiento, pero esto obviamente dependerá de muchos factores (los datos, la máquina, la configuración de la base de datos, etc.).

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

Answer 6

pyodbc 4.0.19 agregó una opción Cursor#fast_executemany para ayudar a resolver este problema. Vea esta respuesta para más detalles.