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

[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' ".

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

[Logger]

Цитата:

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

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

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

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

[Ace of Database]

Цитата:

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

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

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

[belugin]

Цитата:

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

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

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

[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.

[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]

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

[Ace of Database]

Намеренно оставил в комментариях кода //group by TableId .
Просто у нас тут тоже были спорщики, которые утверждали, что exists join тормозит. И пытались ускорить путем устранения exist join'а.
Пока не переставил местами поля в условиях отбора, ничего не помогало

[Logger]

Цитата:

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

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

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

Если все именно так и есть как вы говорите, то я сильно разочаруюсь в MS SQL

[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: по-моему, когда данные на диске сжимаются в разы, это должно как-то очень существенно повлиять на стратегии оптимизации запросов и различные весовые коэффициенты, в них используемые.

[Logger]

Цитата:

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

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

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

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

[S.Kuskov]

Цитата:

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

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

Ничего личного. Стратегия оптимизации логична и ясна. Просто необходимость проделывать её вручную ствится под сомнение. Нужно подобрать хороший пример и провести исследование данного вопроса.

P.S.: А как вы представляете себе соблюдение 'оптимальной' последовательности условий при конструировании запроса при помощи Query?

[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

[Ace of Database]

Цитата:

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

Ничего личного. Стратегия оптимизации логична и ясна. Просто необходимость проделывать её вручную ствится под сомнение. Нужно подобрать хороший пример и провести исследование данного вопроса.

P.S.: А как вы представляете себе соблюдение 'оптимальной' последовательности условий при конструировании запроса при помощи Query?

Query - ни в коем случае не использую в критических случаях. Использую только в отчетах и в тех местах, где прогон по всем записям осуществляется всего один раз. Для мелких функций, которые вызываются сотни раз, и для которых задержка в 1 секунду уже фатальна, Query не использую.

[Logger]

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

[Ace of Database]

Завтра скажу

[S.Kuskov]

Цитата:

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

Query - ни в коем случае не использую в критических случаях...

Согласен, с Query есть свои проблемы, но при желании их можно обойти. Помогите с Query

[raz]

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