Производительность SQL-запросов Python

Question

Я использую jaydebeapi (Mac OS X) для запроса базы данных Netezza и выполняю некоторые быстрые / грязные расчеты времени:

t0 = time.time()
curs.execute('''select * from table1;''')
print time.time() - t0

Я сам создал таблицу, и она содержит 650 000 строк и 9 столбцов (целые числа и даты).

Когда я запускаю указанную выше команду, она занимает около 1,3 минуты (в среднем за 10 прогонов).

Затем, когда я пытаюсь получить данные:

t0 = time.time()
curs.execute('''select * from table1;''')
row = curs.fetchone()
while row is not None:
    row = curs.fetchone()
print time.time() - t0

Это займет около 10 минут (в среднем за 10 пробежек).

Теперь, когда я выполняю тот же SQL-запрос с использованием WinSQL (Windows 7, ODBC), для возврата данных требуется около 3 минут. Я не могу понять, почему в Python это занимает так много времени, и я не уверен, как и где начать искать.

Answer 1

Используете ли вы JayDeBeApi в сочетании с JPype или вместе с Jython? Извлечение больших наборов результатов с реализацией JPype вызывает некоторые вызовы JNI для каждого значения ячейки, что приводит к большим накладным расходам. Вы должны рассмотреть один из следующих вариантов:

1. Минимизируйте размер вашего набора результатов. Выполнять агрегации с использованием функций SQL.
2. Попробуйте новейшую реализацию JPype1. Там были некоторые улучшения производительности.
3. Переключите свою среду выполнения на Jython (JayDeBeApi работает и на Jython)
4. Реализуйте запросы БД и извлечение данных непосредственно в Java и вызывайте логику с помощью JPype, но с интерфейсом, не возвращающим большой набор данных.
5. Попробуйте улучшить код JPype и JayDeBeApi

Answer 2

У меня была похожая проблема, и я наблюдал улучшение, используя fetchall и установка курсора arraysize параметр (не равен 1), как указано в документации DB-API, по которой JayDeBeApi основан.

cursor = conn.cursor()
cursor.arraysize = 10000
cursor.execute("select * from table1")

rows = cursor.fetchall()

# storing data in a pandas DataFrame
df = pd.DataFrame(data=rows, columns = ["C1", "C2", "C3"])

cursor.close()

Я наблюдал следующие выступления на выборке 600.000 строк

arraysize = 10000 --- 509 seconds
arraysize = 1     --- 526 seconds

Тем не менее, я также наблюдал гораздо большее время выборки по сравнению, например, с клиентом на основе Java, использующим тот же драйвер JDBC. Мое предложение, как говорил 9000, состоит в том, чтобы потратить некоторое время на ваш запрос SQL и позволить базе данных выполнить свою работу, это более быстрое и гораздо более масштабируемое решение.

Answer 3

Возможно, вы захотите использовать curs.fetchmany() вместо fetchone. Это будет несколько оптимизировать движение вперед и назад для извлечения строк.

Примерно так будет даже скрываться тот факт, что вы выбираете много строк одновременно:

def fetchYield(cursor):
        li = []
        while True:
            if not li:
                li = cursor.fetchmany()
                if not li:
                    raise StopIteration
            yield li.pop(0)

for row in fetchYield(curs):
   <do something with row>

Тем не менее, я думаю, что если для получения данных в формате raw sql-инструмента требуется 3 минуты, то вполне разумно, чтобы ваш код Python занимал в 3 раза больше времени.