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?

РЕДАКТИРОВАТЬ: добавьте заметки после обнаружения ghoerz

В pyobbc поток executemany равен:

  • подготовить выражение
  • для каждого набора параметров
    • связывает набор параметров
    • выполнить

В ceODBC поток executemany равен:

  • подготовить выражение
  • привязать все параметры
  • выполнить

Ответы

Ответ 1

У меня была аналогичная проблема с вставкой pyODBC в базу данных SQL Server 2008 с использованием функции executeemany(). Когда я запускал трассировку профилировщика на стороне SQL, pyODBC создавал соединение, готовил параметризованный оператор insert и выполнял его для одной строки. Затем он будет не готовить выражение и закрыть соединение. Затем он повторял этот процесс для каждой строки.

Я не смог найти решение в pyODBC, которое этого не делало. Я перешел на ceODBC для подключения к SQL Server, и он правильно использовал параметризованные утверждения.

Ответ 2

Пробовал как ceODBC, так и mxODBC, и оба были также очень медленными. Закончился переход с помощью adodb с помощью http://www.ecp.cc/pyado.html. Общее время работы улучшено в 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()

Ответ 3

pyodbc 4.0.19 добавил параметр Cursor#fast_executemany, чтобы помочь решить эту проблему. Подробнее см. этот ответ.

Ответ 4

Я написал данные в текстовый файл, а затем вызвал служебную программу BCP. Намного намного быстрее. От 20 до 30 минут до нескольких секунд.

Ответ 5

Попытка вставить строки +2M в MSSQL с помощью pyodbc заняла абсурдно много времени по сравнению с массовыми операциями в Postgres (psycopg2) и Oracle (cx_Oracle). У меня не было привилегий для использования операции BULK INSERT, но я смог решить проблему с помощью метода ниже.

Многие решения правильно предлагают fast_executemany, однако, есть несколько хитростей, чтобы правильно его использовать. Во-первых, я заметил, что pyodbc выполнял коммит после каждой строки, когда для autocommit было установлено значение True в методе connect, поэтому для него должно быть установлено значение False. Я также наблюдал нелинейное замедление при вставке более чем ~ 20 тыс. Строк за раз, то есть вставка 10 тыс. Строк была менее чем за секунду, а 50 тыс. Выше 20 с. Я предполагаю, что журнал транзакций становится довольно большим и замедляет все это. Поэтому вы должны разделить свою вставку и зафиксировать после каждого фрагмента. Я обнаружил, что 5 тыс. Строк на чанк обеспечивают хорошую производительность, но это, очевидно, будет зависеть от многих факторов (данных, машины, конфигурации БД и т.д.).

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

Ответ 6

Я использовал pypyODBC w/python 3.5 и Microsoft SQL Server Management Studio. Отдельная таблица (~ 70K строк с 40 vars) занимала 112 секунд для INSERT с использованием метода .executemany() с pypyodbc.

С ceODBC это заняло 4 секунды.