Оптимизация запросов к БД в коде

[Ace of Database]

----------------
sukhanchik Обсуждение вынесено из ветки Нужен совет: Oracle или MS SQL
----------------

5 лет назад я говорил, что "таких нет", кто будет выбирать в пользу MS SQL. Теперь я изменил свое мнение.
Работаю на базе данных MS SQL с 9 млн. проводок и 448 тыс. накладных. Никаких блокировок и тормозов нет. Все летает. Размер базы 150 ГБ.
Не парюсь со всякими хинтами типа forceplaceholders и forcenestedloops.
Везде использую exist join - очень удобно.

Чтобы база работала быстро, надо:
1) Большое количество памяти на сервере, чтобы информацию о блокировках записей SQL-сервер целиком помещал в памяти. Тогда не будет возникать страничных и табличных блокировок.
2) периодически перестраивать индексы и обновлять статистику запросов
3) программисту всегда проверять, есть ли индекс по полям, которые перечислены в выражении отбора "where". достаточно иметь индекс по первому полю, идущему в выражении отбора "where"
3)в выражении отбора "where" сначала перечислять более уникальные поля, а потом менее уникальные - например надо писать "where inventTrans.TransRefId == 'journalId' && inventTrans.TransType == InventTransType.InventTransfer". А так писать нельзя: "where inventTrans.TransType == InventTransType.InventTransfer && inventTrans.TransRefId == 'journalId' ".

[S.Kuskov]

Цитата:

Сообщение от Ace of Database

3)в выражении отбора "where" сначала перечислять более уникальные поля, а потом менее уникальные - например надо писать "where inventTrans.TransRefId == 'journalId' && inventTrans.TransType == InventTransType.InventTransfer". А так писать нельзя: "where inventTrans.TransType == InventTransType.InventTransfer && inventTrans.TransRefId == 'journalId' ".

Вы это серьёзно?

[Ace of Database]

Цитата:

Сообщение от S.Kuskov

Вы это серьёзно?

Абсолютно серьезно. Много таких мест переделал, когда возникали тормоза - менял местами поля в условии. Я сугубо практик, не теоретик. База медленно работает тогда, когда программисты не следят за тем, что у них в условии "where" написано. По более уникальному полю SQL-сервер сразу отсекает кучу ненужной информации.
В приведенном мною примере сервер быстро найдет складские проводки по коду журнала - их всего несколько десятков штук, а затем из этих нескольких десятков проводок выберет те, у которых тип - "Перенос". А если написать запрос наоборот, то сервер сначала будет искать все проводки с типом "Перенос" - их могут быть миллиноны.

[belugin]

Цитата:

Сообщение от Ace of Database

А если написать запрос наоборот, то сервер сначала будет искать все проводки с типом "Перенос" - их могут быть миллиноны.

Можете написать скрипт, который воспроизведет такое поведение?

[Ace of Database]

Цитата:

Сообщение от belugin

Можете написать скрипт, который воспроизведет такое поведение?

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

Вот недавно оптимизированный в моей базе запрос, который зависал.

Запрос до оптимизации

PHP код:

static void Job296(Args _args)
{
    someLine                            someLine;
    someTable                           someTable;
    WMSJournalTrans                     WMSJournalTrans;
    purchLine                           purchLine;
    boolean                             _OnlyPosted = true;
    ;
        Select Sum(NetWeight), Sum(InventQtyBoxes), Sum(InventQtyUnits) From someLine
            Where someLine.ItemId == "ItemId"
            exists join someTable
                //group by TableId
                Where
                      (someTable.SomeType  == SomeEnum::FirstVariant ||
                       someTable.SomeType  == SomeEnum::SecondVariant   ||
                       someTable.SomeType  == SomeEnum::ThirdVariant) &&
                      !someTable.IsCanceled &&
                      someTable.RecId == someLine.ParentRecId //индексированное поле, уникальное для someTable
            exists join WMSJournalTrans
                //group by TableId
                Where
                      (WMSJournalTrans.fPosted  ||  !_OnlyPosted) &&
                       WMSJournalTrans.someTableRecId  == someTable.RecId &&
                       WMSJournalTrans.inventTransId == purchLine.InventTransId  //индексированное поле
;
} 


Запрос после оптимизации

PHP код:

static void Job296(Args _args)
{
    someLine                            someLine;
    someTable                           someTable;
    WMSJournalTrans                     WMSJournalTrans;
    purchLine                           purchLine;
    boolean                             _OnlyPosted = true;
    ;
        Select Sum(NetWeight), Sum(InventQtyBoxes), Sum(InventQtyUnits) From someLine
            Where someLine.ItemId == "ItemId"
            exists join someTable
                //group by TableId
                Where
                      someTable.RecId == someLine.ParentRecId  && //индексированное поле, уникальное для someTable
                      (someTable.SomeType  == SomeEnum::FirstVariant ||
                       someTable.SomeType  == SomeEnum::SecondVariant   ||
                       someTable.SomeType  == SomeEnum::ThirdVariant) &&
                      !someTable.IsCanceled
            exists join WMSJournalTrans
                //group by TableId
                Where
                       WMSJournalTrans.inventTransId == purchLine.InventTransId && //индексированное поле
                       WMSJournalTrans.someTableRecId  == someTable.RecId &&
                      (WMSJournalTrans.fPosted  ||  !_OnlyPosted);
} 


[Ace of Database]

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

[Logger]

Цитата:

Сообщение от Ace of Database

Вот недавно оптимизированный в моей базе запрос, который зависал.

Запрос до оптимизации
....
Запрос после оптимизации
....

а можно привести ваши планы запросов в обоих случаях ?

[belugin]

Цитата:

Сообщение от Ace of Database

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

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

Если в следующий раз встретится, не могли бы вы привести SQL, который уходит на сервер и план запроса?

[Logger]

Цитата:

Сообщение от fed

Я не уверен что ее лечить надо. Я просто начинал работать на Informix, дык вот там когда место в таблице блокировок кончалось (при массовых обновлениях), просто генерировалась ошибка и транзакция откатывалась. В MS SQL за счет эскалации эта проблема преврашается в несколько менее тяжелую - проблему конфликтов из за страничных блокировок. В оракле проблема исчерпания таблицы блокировок не возникает, поскольку информация о блокировках храниться собственно в страницах БД. Однако такой способ хранения информации о блокировках чреват некоторым падением производительности при работе с блокировками.
Так что:
а) не факт что надо лечить
б) не факт что побочные эффекты от лекарства не окажуться тяжелее болезни.

Но ведь если так рассуждать, то получится что для MS SQL нам нужен сервер с намного большим объемом памяти, чтобы гарантировать что её хватает и мы не скатываемся в эскалацию блокировок.

Реально конечно нужно смотреть схожие по объемы и числу активных сессий инсталляции и сравнивать.

[SolNik]

Цитата:

3)в выражении отбора "where" сначала перечислять более уникальные поля, а потом менее уникальные - например надо писать "where inventTrans.TransRefId == 'journalId' && inventTrans.TransType == InventTransType.InventTransfer". А так писать нельзя: "where inventTrans.TransType == InventTransType.InventTransfer && inventTrans.TransRefId == 'journalId' ".

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

[Wamr]

Раз человек говорит, значит у него так и было и перестановка where помогла.
Только я подозреваю, что проблема не в where, а в последующем order by, который построен по этим же полям и включается соответствующей галкой.

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

Цитата:

Работаю на базе данных MS SQL с 9 млн. проводок и 448 тыс. накладных. Никаких блокировок и тормозов нет. Все летает. Размер базы 150 ГБ.
Не парюсь со всякими хинтами типа forceplaceholders и forcenestedloops.

[gl00mie]

Цитата:

Сообщение от Ace of Database

5 лет назад я говорил, что "таких нет", кто будет выбирать в пользу MS SQL. Теперь я изменил свое мнение.
Работаю на базе данных MS SQL с 9 млн. проводок и 448 тыс. накладных. Никаких блокировок и тормозов нет. Все летает. Размер базы 150 ГБ.
Не парюсь со всякими хинтами типа forceplaceholders и forcenestedloops.
Везде использую exist join - очень удобно.

Вы бы хоть рассказали, какая используется версия Ms SQL (2005/2008), используется ли сжатие, если речь о 2008-м, какое у вас железо - очень было бы интересно это узнать. Потому что с точки зрения DBA, возможно, все и однозначно: лучше Oracle, потому что там больше настроек, потому что там есть RAC, потому что его можно поставить на (чей-нибудь любимый) *nix и все такое. Но с точки зрения компании в контексте TCO, по-моему, все не так однозначно: железо дешевеет и при этом становится мощнее и шустрее, а специалисты по ораклу, во-первых, "не растут на деревьях", а во-вторых, как отмечалось, "стоят типа дорого".
Еще, конечно, очень интересно было бы узнать про чей-нибудь опыт использования сжатия данных в Ms SQL 2008: по-моему, когда данные на диске сжимаются в разы, это должно как-то очень существенно повлиять на стратегии оптимизации запросов и различные весовые коэффициенты, в них используемые.

[Ace of Database]

Цитата:

Сообщение от gl00mie

Вы бы хоть рассказали, какая используется версия Ms SQL (2005/2008), используется ли сжатие, если речь о 2008-м, какое у вас железо - очень было бы интересно это узнать. Потому что с точки зрения DBA, возможно, все и однозначно: лучше Oracle, потому что там больше настроек, потому что там есть RAC, потому что его можно поставить на (чей-нибудь любимый) *nix и все такое. Но с точки зрения компании в контексте TCO, по-моему, все не так однозначно: железо дешевеет и при этом становится мощнее и шустрее, а специалисты по ораклу, во-первых, "не растут на деревьях", а во-вторых, как отмечалось, "стоят типа дорого".
Еще, конечно, очень интересно было бы узнать про чей-нибудь опыт использования сжатия данных в Ms SQL 2008: по-моему, когда данные на диске сжимаются в разы, это должно как-то очень существенно повлиять на стратегии оптимизации запросов и различные весовые коэффициенты, в них используемые.

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

Здесь я попытался акцентировать внимание на том, что от программирования очень много зависит - не меньше чем от железа. Запрос, который я здесь привел, выполнялся за 2 секунды до оптимизации. Но так как он вызывался столько раз, сколько строк в закупке, то система висела по несколько минут. После оптимизации закупки открываются моментально, независимо от количества строк в них.

И я не в коем случае не говорю, что MS SQL лучше, чем Oracle! Просто, MS SQL тоже пригоден для работы с большими объемами данных.


Забыл сказать: версия SQL - 2005

[Logger]

Ну хотя бы сколько памяти на сервере БД вы можете сказать ?

[Ace of Database]

Завтра скажу

[Ace of Database]

Цитата:

Сообщение от Logger

Ну хотя бы сколько памяти на сервере БД вы можете сказать ?

64 гига. SQL 2005.

[Logger]

Цитата:

Сообщение от Ace of Database

64 гига. SQL 2005.

А сколько активных сессий в БД в пиковой нагрузке ?
Вопрос связан с тем что, как я понимаю, просто объем памяти обо всем не говорит. Его может быть достаточно если у вас 100 одновременных сессий работает и недостаточно если их 1000.

[Ace of Database]

Видите, сколько много разных вопросов возникает?
Мне проще писать запросы в соответствии с соблюдением порядка полей в индексах. Чем думать обо всех остальных нюансах.
Да, возможно устарела стастистика. Но после исправления уже 2 месяца нет тормозов - перестала устаревать значит.
И, повторюсь, критично, чтобы запрос отрабатывал моментально, даже задержка в 1 секунду смертельна.

Я не претендую на последнюю инстанцию.

И еще, это не единственный такой исправленный запрос. Было исправлено десятки разных мест. И нигде сейчас тормозов не наблюдается.

[Logger]

Цитата:

Сообщение от Ace of Database

Видите, сколько много разных вопросов возникает?

Не так уже много.
Приведите запросы которые у вас уходят на сервер БД в обоих случаях.
Наверняка хинты какие нить ядро вставило.

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

[raz]

Вероятнее всего проблема с изменением порядка полей и последующим ускорением/замедлением запросов лежит в плоскости оптимизатора аксапты, а не sql. В dax3 с какого-то kr оптимизатор аксапты начал сам подставлят индексы в запросы согласно условиям where.