Ускорьте вставки в SQL Server из pyodbc

В pythonУ меня есть процесс, чтобы выбрать данные из одной базы данных (Redshift с помощью psycopg2), затем вставьте эти данные в SQL Server (с помощью pyodbc). Я решил делать чтение / запись, а не чтение / плоский файл / загрузку, потому что число строк составляет около 100 000 в день. Казалось, проще просто подключить и вставить. Тем не менее - процесс вставки идет медленно и занимает несколько минут.

Есть ли лучший способ вставить данные в SQL Server с помощью Pyodbc?

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()
Источник

2 ответа

Решение

ОБНОВЛЕНИЕ: 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 "партия" из 10000 строк вы бы

  • выполнение 10000 отдельных вставок,
  • с 10 000 обращений к серверу и
  • отправка идентичного текста команды SQL (INSERT INTO ...) 10000 раз.

Можно сделать так, чтобы pyodbc отправлял инициалы sp_prepare а затем сделать .executemany призвание sp_execute, но природа .executemany это то, что вы все равно будете делать 10000 sp_prepexec звонки, просто выполняю sp_execute вместо INSERT INTO ..., Это могло бы повысить производительность, если бы оператор SQL был довольно длинным и сложным, но для короткого, такого как пример в вашем вопросе, он, вероятно, не имел бы такой большой разницы.

Можно также проявить творческий подход и создать "конструкторы табличных значений", как показано в этом ответе, но обратите внимание, что он предлагается только как "План Б", когда собственные механизмы массовой вставки не являются выполнимым решением.

Источник

Это хорошо, что вы уже используете executemany() , [ Вычеркнуто после прочтения другого ответа. ]

Это должно ускорить (очень немного), если вы перемещаете connect() а также cursor() призывает к вашему insert_cnxn а также insert_cursor за пределами вашего цикла. (Конечно, если вы сделаете это, вы должны также переместить 2 соответствующих close() вызовы также вне цикла.) Помимо того, что нет необходимости (повторно) устанавливать соединение каждый раз, повторное использование курсора избавит от необходимости каждый раз перекомпилировать SQL.

Тем не менее, вы, вероятно, не увидите в этом огромного ускорения только потому, что в любом случае вы, вероятно, делаете ~10 проходов (учитывая, что вы сказали ~100 000 в день, и ваши группы группируются вместе по 10 000 за раз).

Еще одна вещь, на которую вы могли бы обратить внимание: есть ли какие-либо "закулисные" преобразования, сделанные на вашем OrderDate параметр. Вы можете перейти в SQL Server Management Studio и посмотреть план выполнения запроса. (Найдите свой запрос на вставку в списке "недавних дорогих запросов", щелкнув правой кнопкой мыши на узле сервера и выбрав "Монитор активности"; щелкните правой кнопкой мыши на запросе на вставку и посмотрите его План выполнения.)

Источник
Другие вопросы по тегам