Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Оптимизация запросов с большим количеством вставок и удалений / 25 сообщений из 37, страница 1 из 2
  1  все
17.09.2020, 14:39
    #39999837
demon1992
demon1992
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
Доброго дня. Есть хп, которая делает очень много расчетов, во время этих расчетов что-то пишет и удаляет во временные таблицы.
До определенного момента все работает очень быстро, но вот чем больше проходит вставок и удалений, производительность падает в геометрической прогрессии. Проблема решается простым дроблением вызова процедуры не за один большой период, а за меньшие, при которых база не тормозит.
Так вот вопрос, почему так происходит? Это такие проблемы архитектуры субд, или есть какие то алгоритмы при которых сервак не будет уходить в себя? Или может быть в 4-ке будет лучше с этим.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2020, 14:45
    #39999844
hvlad
hvlad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
Если временные таблицы - это GTT, то
а) не надо ничего из них удалять - это пустая трата времени и памяти
б) нужно обеспечить чтобы ненужные на данном этапе (старые) записи не попадали в новые запросы
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2020, 14:46
    #39999845
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
demon1992Так вот вопрос, почему так происходит?

Потому что автор процедуры не потрудился проанализировать её план на реальных данных
большого объёма.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2020, 14:47
    #39999846
Мимопроходящий
Мимопроходящий
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
имхо, тут программист нужен. (С)
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2020, 15:04
    #39999870
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
demon1992Так вот вопрос, почему так происходит?

а чёрт его знает отсюда не видно. Вы не привели ни единой строчки кода. Ну вообще GTT тоже накапливают версии и в них может происходить сборка мусора, хотя для них это не так критично как для постоянных таблиц.

demon1992Это такие проблемы архитектуры субд

это скорее проблемы архитектуры вашего алгоритма расчётов. Мне GTT для буферизации промежуточных расчётов потребовалась всего 2 раза. И эти процедуры работают пулей и не тормозят.

demon1992Или может быть в 4-ке будет лучше с этим.

там много чего улучшили, но опять же не увидев что вы именно делаете трудно сказать
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2020, 16:38
    #39999954
pastor
pastor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
demon1992,

если сбор данных во временные таблицы и их последующая обработка идут в одной транзакции, то индексы по этим таблицам не используются - на них нет статистики, а планы выполнения строятся на prepare.

обработку данных переписал на EXECUTE STATEMENT внутри процедуры - они препарятся перед выполнением.

на моих объемах с 30 мин до 2 сек.

на время создания и отладки - обычный текст, потом в ES
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2020, 17:03
    #39999976
demon1992
demon1992
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
pastor,
В одной транзакции.
Спасибо за подсказку, попробую.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2020, 17:18
    #39999986
hvlad
hvlad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
pastor
если сбор данных во временные таблицы и их последующая обработка идут в одной транзакции, то индексы по этим таблицам не используются - на них нет статистики, а планы выполнения строятся на prepare.
PK должны использоваться, iirc.

pastor
обработку данных переписал на EXECUTE STATEMENT внутри процедуры - они препарятся перед выполнением
Статистика от этого не появляется. Разве что кардинальность таблиц отличается от нулевой.

pastor
на моих объемах с 30 мин до 2 сек.
Верю. Но диагноз - не правильный ;)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2020, 18:28
    #39999999
kdv
kdv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
pastorто индексы по этим таблицам не используются - на них нет статистики
на них она нулевая, типа "ключей в индексе нет", но - индексы используются. По крайней мере на обычных таблицах.
Ну и да, оптимизатор может выкинуть индекс, если есть индекс по ПК "получше".
Короче, план надо смотреть.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 08:47
    #40000141
pastor
pastor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
hvlad
pastor
если сбор данных во временные таблицы и их последующая обработка идут в одной транзакции, то индексы по этим таблицам не используются - на них нет статистики, а планы выполнения строятся на prepare.
PK должны использоваться, iirc.

pastor
обработку данных переписал на EXECUTE STATEMENT внутри процедуры - они препарятся перед выполнением
Статистика от этого не появляется. Разве что кардинальность таблиц отличается от нулевой.

pastor
на моих объемах с 30 мин до 2 сек.
Верю. Но диагноз - не правильный ;)


это уже обсуждалось. в том числе с тобой.

https://www.sql.ru/forum/1198849/ispolzovanie-indeksov-na-vremennyh-tablicah-vnutri-procedur-s-i-bez-execute-statement

выводы я тогда сделал, возможно, неправильные, но эффективные.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 10:09
    #40000164
m7m
m7m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
pastor

если сбор данных во временные таблицы и их последующая обработка идут в одной транзакции, то индексы по этим таблицам не используются - на них нет статистики, а планы выполнения строятся на prepare.

Не знаю почему, но у меня индексы используются
 
откуда такой вывод Ситуация была аналогичная ситуации ТС
Решилось все ровно тем что посоветовал ТСу hvlad
hvlad
Если временные таблицы - это GTT, то
а) не надо ничего из них удалять - это пустая трата времени и памяти
б) нужно обеспечить чтобы ненужные на данном этапе (старые) записи не попадали в новые запросы

Для обеспечения пункта б)
добавил в таблицы новое поле ("номер порции") , добавил индекс по этому полю ну и во всех запросах добавил условие "номер порции" = :"номер порции"
(ну мне повезло, "номер порции" не надо было искуственно придумывать, он по сути и так существовал)
Все.
Этого было достаточно.

на всякий, решил сейчас все перепроверить
Убираю индекс - время выполнения ~30минут
Добавляю индекс - время выполнения ~2минуты

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 10:23
    #40000173
pastor
pastor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
m7m,

потому что я не совсем точно выразился

1. накопление и обработка - в одной процедуре

2. подхватывать нужно было не PK/FK, а специально сделанный индекс
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 10:47
    #40000182
m7m
m7m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
pastor
1. накопление и обработка - в одной процедуре

Возможно что как-бы в этом и проблема (Но у меня по этому поводу есть сомнения),
У меня процедура одна, в ней в цикле вызов другой процедуры с "номером порции", которая в свою очередь вызывает другие и т.д и т.п. "заполнение" и "чтение" в основном разнесены по разным процедурам
pastor
2. подхватывать нужно было не PK/FK, а специально сделанный индекс

Ну таки да, подхватывается специально сделанный индекс
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 11:06
    #40000188
hvlad
hvlad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
Здесь два принципиально разных сценария работы.

а) простой - у pastor'а - когда данные в GTT накапливаются и потом используются один (или пару) раз в тр-ции, причём использование данных требует их индексирования, отличного от ПК
Тут проблема с планом того запроса, который использует данные, и вынесение его препарирования (с помощью ES) на тот момент, когда таблицы не пусты, влияет на план в лучшую сторону.

Особо подчеркну - влияет не статистика индексов (которой нет в любом случае), а кардинальность таблицы - которая реально отличается.
Напомню: для маленьких таблиц оптимизатор может не использовать индексы, т.к. в этом часто нет смысла.
Но когда таблица чуть больше - оптимизатор может решить взять индекс, даже если статистика пуста.

б) чуть более сложный - у demon1992 и m7m - когда данные накапливаются, используются и удаляются многократно в одной тр-ции.
Тут проблема с накоплением мусора, который невозможно очистить в одной тр-ции, и поэтому каждый следующий select постоянно читает старые ненужные данные.
Может наложиться проблема и с планом запроса, как в (а), но проблему мусора решение из (а) не устранит.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 11:10
    #40000189
m7m
m7m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
hvlad
Напомню: для маленьких таблиц оптимизатор может не использовать индексы, т.к. в этом часто нет смысла.
Но когда таблица чуть больше - оптимизатор может решить взять индекс, даже если статистика пуста.

Ну вот и все для меня прояснилось.
Спасибо!!!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 14:24
    #40000266
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
hvladОсобо подчеркну - влияет не статистика индексов (которой нет в любом случае), а
кардинальность таблицы - которая реально отличается.

Статистики индексов нет только у того, кто поленился один раз её пересчитать с
заполненными временными таблицами.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 14:29
    #40000275
pastor
pastor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
Dimitry Sibiryakov

hvladОсобо подчеркну - влияет не статистика индексов (которой нет в любом случае), а
кардинальность таблицы - которая реально отличается.

Статистики индексов нет только у того, кто поленился один раз её пересчитать с
заполненными временными таблицами.


1. нормально совмещать DDL с DML в одной транзакции?

2. пока расчехлял склероз, уже напомнил, что речь об одном ОПЕРАТОРЕ (SP, EB).

да и проблема, грят, не в индексах, а кардинальная.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 14:34
    #40000279
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
pastor1. нормально совмещать DDL с DML в одной транзакции?

Нормально. Но нужно хорошо понимать что ты делаешь и не совать "SET STATISTICS" куда
попало. Повторяю медленно: ОДИН РАЗ .
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 14:39
    #40000284
pastor
pastor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
Dimitry Sibiryakov

pastor1. нормально совмещать DDL с DML в одной транзакции?

Нормально. Но нужно хорошо понимать что ты делаешь и не совать "SET STATISTICS" куда
попало. Повторяю медленно: ОДИН РАЗ .


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

только в присутствии сисадмина?


и каким местом это повлияет на кардинальность?
на момент старта ХП - таблицы пусты

при одновременной работе нескольких пользователей - что будет?


зачем?
обходных решений давно уже найдено. штатных не пока не проглядывается.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 14:43
    #40000286
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
pastorна момент старта ХП - таблицы пусты

Пофиг. Статистика индексов глобальна.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 14:46
    #40000287
pastor
pastor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
Dimitry Sibiryakov

pastorна момент старта ХП - таблицы пусты

Пофиг. Статистика индексов глобальна.


насколько я понял Влада - статистика не при чем.
оптимизатор кладет на нее, если таблицы пустые
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 16:10
    #40000315
m7m
m7m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
m7m
hvlad
Напомню: для маленьких таблиц оптимизатор может не использовать индексы, т.к. в этом часто нет смысла.
Но когда таблица чуть больше - оптимизатор может решить взять индекс, даже если статистика пуста.

Ну вот и все для меня прояснилось.
Спасибо!!!

Хотя, с прояснилось малость погорячился
Чего-то я думал что при препарировании процедуры, происходит препарирование
и всех используемых процедур, и это у меня теперь не укладывается с "когда таблица чуть больше - оптимизатор может решить взять индекс"
Когда-же происходит препарирование вложенных процедур??
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 16:23
    #40000318
pastor
pastor
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
m7m
m7m
пропущено...

Ну вот и все для меня прояснилось.
Спасибо!!!

Хотя, с прояснилось малость погорячился
Чего-то я думал что при препарировании процедуры, происходит препарирование
и всех используемых процедур, и это у меня теперь не укладывается с "когда таблица чуть больше - оптимизатор может решить взять индекс"
Когда-же происходит препарирование вложенных процедур??


все права, планы и пр. при препарировании запроса. в т.ч. вложенных, рекурсивных и пр.

в этом месте наступаем себе на эти самые.

логика, зависящая от данных, при отсутствии этих данных.

Dimitry Sibiryakov советует использовать память предыдущих перерождений, в нашем же случае, карма очищается полностью и колесо сансары делает полный оборот.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 16:45
    #40000326
hvlad
hvlad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
m7m
Чего-то я думал что при препарировании процедуры, происходит препарирование
и всех используемых процедур
Так и есть, только если они не были загружены в кеш раньше.
В чём сомнения ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.09.2020, 17:33
    #40000345
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запросов с большим количеством вставок и удалений
pastorDimitry Sibiryakov советует использовать память предыдущих перерождений, в нашем же
случае, карма очищается полностью и колесо сансары делает полный оборот.

И вот как тут не будешь грубым с такой вот общественностью?..

Я предложил ровно то, что предложил: предоставить оптимизатору статистику индексов,
собранную в подходящий момент на типичных данных. Что там у тебя очищается колесом -
циферке в системных таблицах сугубо всё равно.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Оптимизация запросов с большим количеством вставок и удалений / 25 сообщений из 37, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 490ms
Закрыть
start [/forum/topic.php?fid=40&mobile=1&tid=1560247]:
 0ms
get settings:
 10ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 147ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 55ms
get tp. blocked users:
 2ms
others: 246ms
total:  490ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]