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

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

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

[Ace of Database]

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

[Logger]

Цитата:

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

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

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

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

[S.Kuskov]

Цитата:

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

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

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

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

[Ace of Database]

Цитата:

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

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

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

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

[S.Kuskov]

Цитата:

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

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

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

[Ace of Database]

Первые два пункта "теории" разве не соответствуют взглядам на принципы оптимизации БД?
А 3-й и 4-й пункт кому-то наносят вред?

PHP код:

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


[sukhanchik]

Цитата:

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

Первые два пункта "теории" разве не соответствуют взглядам на принципы оптимизации БД?
А 3-й и 4-й пункт кому-то наносят вред?

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

[egorych]

Цитата:

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

Первые два пункта "теории" разве не соответствуют взглядам на принципы оптимизации БД?
А 3-й и 4-й пункт кому-то наносят вред?

PHP код:

Чтобы база работала быстро, надо:
1) Большое количество памяти на сервере, чтобы информацию о блокировках записей SQL-сервер целиком помещал в памяти. Тогда не будет возникать страничных и табличных блокировок. 


Из того что я читал о блокировках в MSSQL - таблица блокировок ВСЕГДА находится в памяти и ни при каких условиях не свопится. Объем памяти безусловно полезен, но для другой цели - закэшировать больше данных.

PHP код:

2) периодически перестраивать индексы и обновлять статистику запросов 


К этому пункту нет притензий

PHP код:

3) программисту всегда проверять, есть ли индекс по полям, которые перечислены в выражении отбора "where". достаточно иметь индекс по первому полю, идущему в выражении отбора "where" 


Не все так однозначно, особенно по последнему утверждению

PHP код:

3)в выражении отбора "where" сначала перечислять более уникальные поля, а потом менее уникальные - 


А случайно с построением индексов не перепутали?

[Logger]

Цитата:

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

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

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

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

[belugin]

Цитата:

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

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

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

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