Долгая обработка SQL запроса?

Question

[Тамаз Харламов]

Добрый день.
Изучаю python и работу с SQLite3.
Очень смущает долгая обработка запроса.
Имеем VPS сервер 1 ядро в 2.5 ГГЦ и оперативки в 2 ГБ.
SQLite3 база с двумя таблицами. Записей в одной 11 миллионо в другой 3 миллиона.
требования из первой таблицы удалить все значения которые есть во второй. Сверка по одному столбцу.
Код в python который это делает:

print('Запуск удаления проверенных юзеров')
    step = 0
    cur.execute('''SELECT user_full_data_user_id FROM user_full_data''')
    user_parsing_ok = cur.fetchall()
    korteg = ()
    for k in user_parsing_ok:
        korteg += k
        step += 1
    cur.execute(f'''DELETE FROM parsing
                    WHERE parsing_user_id IN {korteg}''')
    con.commit()
    cur.execute('''VACUUM''')  # Сжимаем таблицу удаляя пустые строки в ней
    print(f'Строк проверенно {step}')

Данный код на удаленной машине выполняется уже 10 часов и еще не завершился.
ПОчему так медленно. Это ограничания SQLite3, сам Python медленный или я написал кривой код?

Comments

[Gip]

это делается одним SQL запросом без цикла. у вас я так понимаю 3 млн делетов происходит. ну и индексы на столбцы по которым сравниваете.

[Тамаз Харламов]

Gip, Подскажите, если одним сделать то это значительно ускорит?
ТАк долго из за цикла? неужели 3 миллиона так долго перебирает python?

[Everything_is_bad]

Тамаз Харламов, ну так учись замерять, тогда и будешь точно знать где у тебя затык, а не играть в угадалку. Да тут даже просто логирование добавить, уже поможет понять место проблемы

[Gip]

Тамаз Харламов, а, прошу прощения. DELETE таки один, это же питон) а индекс по полю parsing_user_id есть?

но я бы всё равно попробововал для начала

DELETE FROM parsing
                    WHERE parsing_user_id IN (SELECT user_full_data_user_id FROM user_full_data)

либо

DELETE FROM parsing p
inner join user_full_data u on u.user_full_data_user_id = p.user_full_data_user_id

[Даниил Рыжов]

1. Неэффективный запрос fetchall(), которым вы загружаете все данные в память.
2. Конструкция IN проверяет каждый раз есть ли значение в списке, что занимает много времени
3. Если ведутся логи изменений - это тоже влияет на скорость
4. VACUUM - требует полной перезаписи, что занимает много времени на больших объёмах данных

[Тамаз Харламов]

Даниил Рыжов, переписал код. Буду заменять за какой период выполняются все запросы и сам цикл.

[Тамаз Харламов]

Everything_is_bad, код переписал. Как завершится этот скрипт перезапущу уже с замерами. Просто хотел спросить у умных людей. Цель вопроса была направлена уточнение, это ограничение самой sqlite или все же кривизна запросов. Не думаю что 3 миллиона циклов так долго буду производится.

Answer 1

[Aragorn]

Можно использовать подзапрос вместо двух запросов и цикла, если я не ошибаюсь

cur.execute('''DELETE FROM parsing
               WHERE parsing_user_id IN (SELECT user_full_data_user_id FROM user_full_data)''')

Ну и, конечно, sqlite - это не для таких БД

P. S. Можно ещё индексы сделать к столбцам

CREATE INDEX idx_user_full_data_user_id ON user_full_data(user_full_data_user_id);
CREATE INDEX idx_parsing_user_id ON parsing(parsing_user_id);

Comments

[Тамаз Харламов]

Спасибо, что объяснили.

Answer 2

[Vindicar]

Какое лютое извращение.

user_parsing_ok = cur.fetchall()
    korteg = ()
    for k in user_parsing_ok:
        korteg += k
        step += 1

Во-первых, вы вытаскиваете 3 миллиона записей в память как питоновские объекты. Это не очень хорошая идея.
Во-вторых, вместо того, чтобы по-человечески сделать

korteg = tuple(item[0] for item in user_parsing_ok)

вы делаете конкатенацию кортежей по одному. Напомню, что при конкатенации создаётся новый кортеж - так что при добавлении трёхмиллионного элемента у вас в памяти будет один кортеж с 2999999 элементами и один кортеж с 3000000 элементами. Это дело будет люто, бешено, неиллюзорно жрать память, а значит, свопиться на диск. Что не прибавит скорости.
Как написали выше, просто используйте подзапрос. Вам совершенно незачем таскать эти айдишники из БД в питон, а потом из питона в БД. Пусть они обрабатываются только в рамках БД.