Как ускорить загрузку данных из oracle sql в pandas df

#python #sql #pandas #oracle #parsing

#python #sql #pandas #Oracle #синтаксический анализ

Вопрос:

Мой код выглядит так, я использую pd.DataFrame.from_records для заполнения данных в dataframe, но для обработки запроса и загрузки данных из таблицы sql с 22 миллионами строк в df требуется время: 1 час 40 минут 30 секунд.

 # I skipped some of the code, since there are no problems with the extract of the query, it's fast
cur = con.cursor()

def db_select(query): # takes the request text and sends it to the data_frame
    cur.execute(query)
    col = [column[0].lower() for column in cur.description] # parse headers
    df = pd.DataFrame.from_records(cur, columns=col) # fill the data into the dataframe
    return df

Затем я передаю sql-запрос функции:

 frame = db_select("select * from table")

Как я могу оптимизировать код для ускорения процесса?

1. Похоже, что требуется обработать слишком много данных (22 млн строк). У вас есть несколько вещей, которые работают против вас: вы выбираете все данные, поэтому в игре нет индексов для ускорения вашего запроса. Вероятным результатом будет полное сканирование таблиц (и все связанные с ним операции ввода-вывода сервера). Затем вы должны передать все это по сети и кэшировать (возможно, несколько раз) в приложении. Это требует большого количества переключений контекста и (я бы предположил) памяти, а возможно, даже подкачки ввода-вывода. Какие узкие места в ресурсах вы наблюдали на серверах или в сети?

2. вы пробовали pd.read_sql ?

3. Я не знаком с oracle, но я помню, что подключение к postgres было медленным; Я думаю, вы можете создать новое соединение и записать каждую строку по отдельности, если не будете осторожны

4. кроме того, 22m — это много строк 🙂

5. Вы могли бы попробовать dd.read_sql_table() , как в dask (pandas big data big brother) вместо pandas. pip install dask и import dask.dataframe as dd

Ответ №1:

Установка правильного значения для cur.arraysize может помочь в настройке производительности выборки. Вам нужно определить наиболее подходящее для него значение. Значение по умолчанию равно 100. Для определения этого значения может быть запущен код с различными размерами массива, например

 arr=[100,1000,10000,100000,1000000]
for size in arr:
        try:
            cur.prefetchrows = 0
            cur.arraysize = size
            start = datetime.now()
            cur.execute("SELECT * FROM mytable").fetchall()
            elapsed = datetime.now() - start
            print("Process duration for arraysize ", size," is ", elapsed, " seconds")
        except Exception as err:
            print("Memory Error ", err," for arraysize ", size)

а затем установите, например, cur.arraysize = 10000 перед вызовом db_select из вашего исходного кода

1. тестирование df с гораздо меньшей формой (4816566, 6) [строки, столбцы] показало следующие результаты: с настройкой cur.arraysize по умолчанию требуется с настройкой требуется Wall time: 26min 59s с настройкой cur.arraysize = 1000 требуется Wall time: 7min 49s с настройкой cur.arraysize = 10000 требуется Wall time: 4min 42s с настройкой cur.arraysize = 100000 требуется Wall time: 4min 32s ** с настройкой cur.arraysize = 1000000 требуется Wall time: 4min 33s** значительное повышение скорости, большое спасибо за вашу помощь!

2. в моем случае установка предварительной выборки как можно выше обеспечивает наилучшую производительность. У меня около 2 м строк и 30 столбцов, и это заняло 1 минуту с предварительной выборкой = 2000000 и arraysize = 1000.