удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи? / Firebird, InterBase

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

14 сообщений из 14, страница 1 из 1

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965181

GrigoriyFomin

Гость

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

Код: sql

1.
2.
3.
4.
5.
6.

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?

План, который предполагается для выполнения запроса:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

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

...

Рейтинг:

0 / 0

01.06.2020, 23:23

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965188

Sergey A. Volkov

Гость

GrigoriyFomin

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

Полный скан будет по количеству возвращенных MODID, т.е. 4,5тыс * кол-во строк в OPERS. ~12 млрд. фетчей.
Если создать индекс по modid (это всего одно сканирование таблицы OPERS), то запрос все равно не будет использовать индекс т.к. возвращаемый из подзапроса MODID не отсортирован.
Если добавить order by modid, тогда оптимизатор может использовать индекс, но не уверен, что оптимизатор так умеет.
Точно будет использовать индекс если сделать так:

Код: sql

1.
2.
3.
4.
5.
6.
7.

execute block as
declare modid integer;
begin
  for select ...
  into :modid
    delete from opers where modid=:modid;
end

...

Рейтинг:

0 / 0

01.06.2020, 23:57

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965203

GrigoriyFomin

Гость

Sergey A. Volkov,

как-то совсем все печально. не могу понять - почему полный скан OPERS занимает столько времени. Полный бэкап базы занимает 2 минуты, рестор - столько же, почему даже при 12-кратном увеличенном скане всех записей это больше суток занимает? откуда такие тормоза? никакого индекса добавлять не буду, так как задача разовая. я вручную быстрее это сделаю, чем сервер. просто непонятно - в чем академическая ошибка?

...

Рейтинг:

0 / 0

02.06.2020, 01:37

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965207

kdv

Участник

Откуда: iBase.ru

Сообщения: 30 139

Рейтинг: 0 / 0

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, и грохнуть индекс, раз вам он так не нравится.

...

Рейтинг:

0 / 0

02.06.2020, 03:29

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965208

kdv

Участник

Откуда: iBase.ru

Сообщения: 30 139

Рейтинг: 0 / 0

GrigoriyFominПолный бэкап базы занимает 2 минуты
кстати, ну ок. 2 минуты забэкапить 3млн записей - нормально.
Но в запросе-то 3 млн записей умножается на 4.5к раз.
Тогда 2 минуты умножаем на 4.5к раз. Получается 9000 минут. Это 150 часов. Какую вы задачу задали, то и получаете.

...

Рейтинг:

0 / 0

02.06.2020, 03:33

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965361

court

Участник

Сообщения: 4 692

Рейтинг: 0 / 0

GrigoriyFomin

Читал, что нежелательность использования IN, ну а как еще по-другому можно выбрать условие для удаления?

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

...

Рейтинг:

0 / 0

02.06.2020, 15:10

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965363

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

courtmerge с delete, если 3-ка

Там не из той таблицы удаляется.
Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

02.06.2020, 15:15

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965370

court

Участник

Сообщения: 4 692

Рейтинг: 0 / 0

Dimitry Sibiryakov

courtmerge с delete, если 3-ка

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

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.

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

а может и того проще

Код: sql

1.
2.
3.
4.
5.

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

...

Рейтинг:

0 / 0

02.06.2020, 15:29

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965372

Dimitry Sibiryakov

Участник

Сообщения: 54 521

Рейтинг: 0 / 0

courtwhen matched then
delete

Хм, действительно. Что-то я не так прочитал в доке.
Posted via ActualForum NNTP Server 1.5

...

Рейтинг:

0 / 0

02.06.2020, 15:35

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965373

hvlad

Участник

Сообщения: 13 125

Рейтинг: 0 / 0

court,

ну наконец-то хоть кто-то вспомнил про MERGE

...

Рейтинг:

0 / 0

02.06.2020, 15:41

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965454

GrigoriyFomin

Гость

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

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

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

План
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

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

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*/

Код: plaintext

1.
2.
3.
4.
5.
6.
7.

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

...

Рейтинг:

0 / 0

02.06.2020, 22:05

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965478

Vlad F

Участник

Сообщения: 1 502

Рейтинг: 0 / 0

GrigoriyFomin,
Осталось проверить, что будет, если in (select) в исходном запросе заменить на exists().

...

Рейтинг:

0 / 0

02.06.2020, 23:59

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965483

GrigoriyFomin

Гость

Vlad F,

наверное, разница будет нулевая?

...

Рейтинг:

0 / 0

03.06.2020, 00:41

| Ответить | Цитировать | Написать

удалить записи по условию подзапроса - оооочень долго делается. Неужели никак не оптимизи?

#39965535

Vlad F

Участник

Сообщения: 1 502

Рейтинг: 0 / 0

GrigoriyFomin,

Гадать будем?))

...

Рейтинг:

0 / 0

03.06.2020, 09:05

| Ответить | Цитировать | Написать

14 сообщений из 14, страница 1 из 1

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=40&msg=39965478&tid=1560339]:	0ms
get settings:	9ms
get forum list:	10ms
check forum access:	3ms
check topic access:	3ms
track hit:	50ms
get topic data:	12ms
get forum data:	2ms
get page messages:	56ms
get tp. blocked users:	1ms
others:	15ms

total:	161ms