Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Firebird, InterBase Новый топик    Ответить
 удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
GrigoriyFomin
Member

Откуда:
Сообщений: 88
Есть такой несложный запросец, который должен удалить все проводки (журнал OPERS) товара с ID товара (MODID), который был внесен в журнал OPERS по накладным с номерами 35333003 и 35332003 ( ORDERS связан с OPERS по связке 2-х полей, которые указаны в запросе). Подзапрос содержит список из около 4,5 тыс. MODID, которые integer. Журнал OPERS не имеет индекса по MODID и соответственно полностью сканируется вся таблица (ну пусть 3 млн. операций).

delete from OPERS
where MODID in (select MODID
                from OPERS OP
                join ORDERS O on O.ORDID = OP.ORDERID and
                      O.ORDCAIDOWN = OP.OPCAIDOWN
                where ORDNO = 35333003 or ORDNO = 35332003)


Потом также надо удалить все эти MODID из журнала остатков, и самого справочника товаров (просто меняем в операторе DELETE названия таблиц).
А теперь собственно вопрос - запросы выполнялся на отдельном неслабом серваке HP 1,5 суток и так и не завершился, пришлось грохать сам FB через процессы. Проц был загружен файрбердом на одно ядро (установка FB с умолчательными параметрами), потребление оперативы - около 45 МБ (при физических 16 гигах). Быстрая SSD и никаких других задач на серваке. Версия FB 3.0.4, база размером 1,4 ГБ, при выполнении запроса размер заметно не меняла. Читал, что нежелательность использования IN, ну а как еще по-другому можно выбрать условие для удаления? Может, копать в сторону применения подзапроса с CTE?

План, который предполагается для выполнения запроса:
PLAN JOIN (OP INDEX (OPERS_IDX1), O INDEX (PK_ORDERS))
PLAN (OPERS NATURAL)

Select Expression
-> Filter
-> Nested Loop Join (inner)
-> Table "OPERS" as "OP" Access By ID
-> Bitmap
-> Index "OPERS_IDX1" Range Scan (partial match: 1/3)
-> Filter
-> Table "ORDERS" as "O" Access By ID
-> Bitmap
-> Index "PK_ORDERS" Unique Scan
Select Expression
-> Filter
-> Table "OPERS" Full Scan
1 июн 20, 23:23    [22144064]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
Sergey A. Volkov
Member

Откуда:
Сообщений: 5
GrigoriyFomin
Подзапрос содержит список из около 4,5 тыс. MODID, которые integer. Журнал OPERS не имеет индекса по MODID и соответственно полностью сканируется вся таблица (ну пусть 3 млн. операций).

Полный скан будет по количеству возвращенных MODID, т.е. 4,5тыс * кол-во строк в OPERS. ~12 млрд. фетчей.
Если создать индекс по modid (это всего одно сканирование таблицы OPERS), то запрос все равно не будет использовать индекс т.к. возвращаемый из подзапроса MODID не отсортирован.
Если добавить order by modid, тогда оптимизатор может использовать индекс, но не уверен, что оптимизатор так умеет.
Точно будет использовать индекс если сделать так:
execute block as
declare modid integer;
begin
  for select ...
  into :modid
    delete from opers where modid=:modid;
end


Сообщение было отредактировано: 1 июн 20, 23:59
1 июн 20, 23:57    [22144082]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
GrigoriyFomin
Member

Откуда:
Сообщений: 88
Sergey A. Volkov,

как-то совсем все печально. не могу понять - почему полный скан OPERS занимает столько времени. Полный бэкап базы занимает 2 минуты, рестор - столько же, почему даже при 12-кратном увеличенном скане всех записей это больше суток занимает? откуда такие тормоза? никакого индекса добавлять не буду, так как задача разовая. я вручную быстрее это сделаю, чем сервер. просто непонятно - в чем академическая ошибка?
2 июн 20, 01:37    [22144113]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 28878
GrigoriyFomin
почему полный скан OPERS занимает столько времени.

нееет. Это не просто полный скан. У вас же план запроса есть.
С конца - table opers full scan. То есть, едем по таблице OPERS, и для каждой записи выполняем то, что выше.
Вам же Волков написал - подзапрос не коррелирован с внешним запросом, поэтому на каждую запись OPERS подзапрос будет выполнять одно и то же - строить битмап, потом делать джойн, и выдавать 4.5 тыс записей результата.
А в OPERS 3млн записей (с ваших слов).
Это значит, что 3 миллиона раз конкретная запись проверяется среди 4.5 тыс "записей результата", так еще и для получения "записей результата" нужно действия выполнить. 3млн * 4.5к = 13.5 млрд операций минимум.
Сервер просто долбит и долбит то что вы ему сказали, а вы ждете и ждете.

GrigoriyFomin
я вручную быстрее это сделаю, чем сервер

Вам опять же посоветовали "перевернуть" подзапрос наружу - 22144082, выполнится быстрее.
Хотя, если не хотите строить индекс, то получится чуть наоборот - снаружи будет результат 4.5к записей, и запрос
delete from opers where modid=:modid;
выполнится эти самые 4.5к раз. Полностью читая 3млн записей OPERS. Тем не менее, будет не так медленно, как в предыдущем случае.
Быстрее будет создать индекс, выполнить тот execute block, и грохнуть индекс, раз вам он так не нравится.
2 июн 20, 03:29    [22144120]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 28878
GrigoriyFomin
Полный бэкап базы занимает 2 минуты

кстати, ну ок. 2 минуты забэкапить 3млн записей - нормально.
Но в запросе-то 3 млн записей умножается на 4.5к раз.
Тогда 2 минуты умножаем на 4.5к раз. Получается 9000 минут. Это 150 часов. Какую вы задачу задали, то и получаете.
2 июн 20, 03:33    [22144121]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
court
Member

Откуда:
Сообщений: 1985
GrigoriyFomin
Читал, что нежелательность использования IN, ну а как еще по-другому можно выбрать условие для удаления?
merge с delete, если 3-ка
2 июн 20, 15:10    [22144395]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 50343

court
merge с delete, если 3-ка

Там не из той таблицы удаляется.

Posted via ActualForum NNTP Server 1.5

2 июн 20, 15:15    [22144399]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
court
Member

Откуда:
Сообщений: 1985
Dimitry Sibiryakov
court
merge с delete, если 3-ка

Там не из той таблицы удаляется.
из какой "не из той" ?

merge into OPERS as a
using (select /* distinct */ MODID
                from OPERS OP
                join ORDERS O on O.ORDID = OP.ORDERID and
                      O.ORDCAIDOWN = OP.OPCAIDOWN
                where ORDNO = 35333003 or ORDNO = 35332003) as b
on a.MODID=b.MODID
when matched then
    delete


а может и того проще
merge into OPERS as OP
using ORDERS O
on O.ORDID = OP.ORDERID and O.ORDCAIDOWN = OP.OPCAIDOWN and OP.ORDNO in (35333003,35332003)
when matched then
    delete
2 июн 20, 15:29    [22144410]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 50343

court
when matched then
delete

Хм, действительно. Что-то я не так прочитал в доке.

Posted via ActualForum NNTP Server 1.5

2 июн 20, 15:35    [22144414]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
hvlad
Member

Откуда:
Сообщений: 10954
court,

ну наконец-то хоть кто-то вспомнил про MERGE
2 июн 20, 15:41    [22144415]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
GrigoriyFomin
Member

Откуда:
Сообщений: 88
hvlad, про merge было неожиданно. Но у этого оператора синтаксис - литру надо брать, чтоб победить. какой-то сложный и не интуитивно понятный.

По моему вопросу насчет количества обращений к базе - почему СУБД не кэширует результат подзапроса? Разве для каждой итерации надо выполнять подзапрос условия? А если никаких изменений другими пользователями не делалось в базе и не зачем кэш пересчитывать?
Вообщем, решил проблему вручную и в лоб - создал временную таблицу с нужными modid и первичным индексом и грохнул журнал операций по вхождению в эту временную таблицу. Запрос отработал за 2 секунды. Невероятно, но факт.
План
PLAN (TMP INDEX (PK_TMP))
PLAN (OPERS NATURAL)

2200 записей было удалено из таблицы OPERS

------ Информация о производительности ------
Время подготовки запроса = 0ms
Время выполнения запроса = 6s 172ms
Current memory = 38 242 592
Max memory = 56 753 056
Memory buffers = 2 048
Reads from disk to cache = 26 599
Writes from cache to disk = 10
Чтений из кэша = 10 767 533


delete from OPERS
where MODID in (select modid from tmp)

/*
insert into tmp (modid)
select MODID
                from OPERS OP
                join ORDERS O on O.ORDID = OP.ORDERID and
                      O.ORDCAIDOWN = OP.OPCAIDOWN
                where ORDNO = 35333003 or ORDNO = 35332003*/


Select Expression
-> Filter
-> Table "TMP" Access By ID
-> Bitmap
-> Index "PK_TMP" Unique Scan
Select Expression
-> Filter
-> Table "OPERS" Full Scan


Сообщение было отредактировано: 2 июн 20, 22:07
2 июн 20, 22:05    [22144543]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
Vlad F
Member

Откуда:
Сообщений: 1295
GrigoriyFomin,
Осталось проверить, что будет, если in (select) в исходном запросе заменить на exists().
2 июн 20, 23:59    [22144580]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
GrigoriyFomin
Member

Откуда:
Сообщений: 88
Vlad F,

наверное, разница будет нулевая?
3 июн 20, 00:41    [22144591]     Ответить | Цитировать Сообщить модератору
 Re: удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?  [new]
Vlad F
Member

Откуда:
Сообщений: 1295
GrigoriyFomin,

Гадать будем?))
3 июн 20, 09:05    [22144684]     Ответить | Цитировать Сообщить модератору
Все форумы / Firebird, InterBase Ответить