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:

  • подготовить заявление
  • loop for each set of parameters
    • bind the set of parameters
    • выполнять

В ceODBC поток executemany:

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

sql-server pyodbc bulkinsert
person sayap    schedule 17.04.2011    source источник
comment
Попробуйте использовать явную транзакцию.   -  person Lasse V. Karlsen    schedule 17.04.2011
comment
Чтение stackoverflow.com/questions/1063770/, похоже, что pyodbc не поддерживает явную транзакцию.   -  person sayap    schedule 17.04.2011
comment
Я это не так читаю. Вы выключаете автоматическую фиксацию и должны явно вызвать откат или фиксацию. Однако я понятия не имею, имеет ли это значение или нет, но я бы попробовал это сам.   -  person Lasse V. Karlsen    schedule 17.04.2011
comment
То, что вы описали, - это именно то, что делает мой код. По умолчанию автоматическая фиксация отключена.   -  person sayap    schedule 17.04.2011
comment
Я не вижу причин для того, чтобы это происходило медленно. Какая версия SQL Server и является ли установка стандартной, т.е. без забавных конфигураций и т. Д.? Нравится запускать базы данных с USB и т. Д.? Вы также можете попробовать подключить SQL Profiler к базе данных и посмотреть, сможете ли вы определить причину неэффективности, но эквивалентный код на C # выполняется на моем компьютере менее чем за 3 секунды.   -  person Ryk    schedule 18.04.2011
comment
Ryk, я думаю, что эта проблема специфична для привязок python для mssql, и у Лассе было правильное предложение, т.е. заставить pyodbc обернуть 10000 INSERT за одну транзакцию. К сожалению, я не смог найти способ сделать это, и с тех пор изменил свой код, чтобы вместо этого использовать BULK INSERT.   -  person sayap    schedule 18.04.2011

Ответы (6)


arrow_upward
11
arrow_downward

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

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

person ghoerz    schedule 29.03.2012
comment
Спасибо за подтверждение и советы. Я подал это как code.google.com/p/pyodbc/issues / detail? id = 250 - person sayap; 30.03.2012

arrow_upward
5
arrow_downward

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

Многие решения правильно предлагали fast_executemany, однако есть некоторые уловки для его правильного использования. Во-первых, я заметил, что pyodbc выполняет фиксацию после каждой строки, когда для autocommit было установлено значение True в методе подключения, поэтому для него должно быть установлено значение 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()
person Dave Lyndon    schedule 24.09.2018

arrow_upward
3
arrow_downward

Пробовал как 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()
person Jay    schedule 05.04.2012

arrow_upward
2
arrow_downward

pyodbc 4.0.19 добавил параметр Cursor#fast_executemany, помогающий решить эту проблему. Дополнительные сведения см. В этом ответе.

person Gord Thompson    schedule 01.11.2017

arrow_upward
1
arrow_downward

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

person MikeP    schedule 31.05.2017

arrow_upward
0
arrow_downward

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

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

person Nirvan    schedule 26.02.2017