возможно ли оптимизировать этот запрос?

[Алексей++]

имеется вот такой код встроенной процедуры для интербейсовской базы (СУБД- FireBird 1.5)
(на названия идентификаторов не смеяться, они мне по наследству достались )

Код:

CREATE PROCEDURE PR_SHOWLOG_GET_MESSAGES(
    ID_TOLOADRFOMEXCLUDING BIGINT,
    ID_MAX BIGINT,
    MESSFILTER_BARR VARCHAR(513),
    MESSFILTER_LARS VARCHAR(37),
    MESSFILTER_REST SMALLINT)
RETURNS (
    MESSAGE_ID BIGINT,
    QUIT_DATE TIMESTAMP,
    DMESSAGE TIMESTAMP,
    NOMER SMALLINT,
    RAZDEL SMALLINT,
    MESS_TYPE SMALLINT,
    QUIT_REASON SMALLINT,
    COLOR SMALLINT,
    NUA_TYPE SMALLINT,
    NEW_COD_X77_MESS SMALLINT,
    DATAWORD INTEGER,
    CANAL_NUMBER SMALLINT,
    CANALNUMBER_PER SMALLINT,
    POWER_LEVEL VARCHAR(2),
    USERID SMALLINT,
    OPERID SMALLINT,
    F_18_GUARDEDNOW SMALLINT,
    TAGPOINTID32 INTEGER,
    NON_TECH_SMS_COUNT SMALLINT,
    ADR_GUID_M4S VARCHAR(32),
    CHOP_NUM_M4S SMALLINT,
    DELTA_TIME_CHOP_M4S INTEGER)
AS
 begin
    -- :messfilter_barr имеет вид '~dec1~dec2~dec3~' (например, '~20~45~17~')
    -- :messfilter_Lars имеет вид '~dec1~dec2~dec3~'
   for
   SELECT 
         MESSAGE_ID
       , DMESSAGE
       , MESS_TYPE
       , NUA_TYPE
       , COLOR
       , NOMER
       , RAZDEL
       , USERID
       , OPERID
       , QUIT_DATE
       , QUIT_REASON
       , CANAL_NUMBER
       , CANALNUMBER_PER
       , POWER_LEVEL
       , DATAWORD
       , NEW_COD_X77_MESS
       , F_18_GUARDEDNOW
       , TAGPOINTID32
       , NON_TECH_SMS_COUNT
       , ADR_GUID_M4S
       , CHOP_NUM_M4S
       , DELTA_TIME_CHOP_M4S
   FROM MESS_LOG
   WHERE
        MESSAGE_ID>:ID_toLoadrfomExcluding
    AND MESSAGE_ID<=:ID_Max 
    AND (
                 (nua_type!=78) -- !='N'
             AND (nua_type!=85) -- !='U'
             AND (nua_type!=65) -- !='A'
           OR
                 (nua_type=85 OR nua_type=65) -- ='U' OR ='A'
             AND (:messfilter_Rest IS NULL OR :messfilter_Rest=1)
           OR
             nua_type=78 -- ='N'
             AND (mess_type!=92) -- != cNT_4p2_CMD_92_txt
             AND (:messfilter_barr IS NULL OR :messfilter_barr CONTAINING '~'||mess_type||'~') -- ... OR '~20~45~17~'CONTAINING ~17~
           OR
             nua_type=78 -- ='N'
             AND (mess_type =92) -- =cNT_4p2_CMD_92_txt
             AND (:messfilter_Lars is NULL or :messfilter_Lars containing '~'||typefor_clientfilter||'~')
        )
   ORDER BY MESSAGE_ID DESC
   into 
        :MESSAGE_ID
       ,:DMESSAGE
       ,:MESS_TYPE
       ,:NUA_TYPE
       ,:COLOR
       ,:NOMER
       ,:RAZDEL
       ,:USERID
       ,:OPERID
       ,:QUIT_DATE
       ,:QUIT_REASON
       ,:CANAL_NUMBER
       ,:CANALNUMBER_PER
       ,:POWER_LEVEL
       ,:DATAWORD
       ,:NEW_COD_X77_MESS
       ,:F_18_GUARDEDNOW
       ,:TAGPOINTID32
       ,:NON_TECH_SMS_COUNT
       ,:ADR_GUID_M4S
       ,:CHOP_NUM_M4S
       ,:DELTA_TIME_CHOP_M4S
   do suspend;
 
 end
 

 все RETURN параметры - это поля из таблицы MESS_LOG. Записей в таблице очень много, поэтому хочется узнать, можно ли оптимизировать запрос.


Собственно, интересует именно громоздкий момент условия

Код:

    -- :messfilter_barr имеет вид '~dec1~dec2~dec3~...~' (например, '~20~45~17~')
    -- :messfilter_Lars имеет вид '~dec1~dec2~dec3~...~'
...
...
    AND (
                 (nua_type!=78) -- !='N'
             AND (nua_type!=85) -- !='U'
             AND (nua_type!=65) -- !='A'
           OR
                 (nua_type=85 OR nua_type=65) -- ='U' OR ='A'
             AND (:messfilter_Rest IS NULL OR :messfilter_Rest=1)
           OR
             nua_type=78 -- ='N'
             AND (mess_type!=92) -- != cNT_4p2_CMD_92_txt
             AND (:messfilter_barr IS NULL OR :messfilter_barr CONTAINING '~'||mess_type||'~') -- ... OR '~20~45~17~'CONTAINING ~17~
           OR
             nua_type=78 -- ='N'
             AND (mess_type =92) -- =cNT_4p2_CMD_92_txt
             AND (:messfilter_Lars IS NULL OR :messfilter_Lars CONTAINING '~'||typefor_clientfilter||'~')
        )

строки messfilter_barr и messfilter_Lars формируются программой и передаются как параметры процедуры. Они задают фильтрацию записей по полю mess_type


сам не знаю, как запрос можно оптимизировать, но чувствую, что это возможно

PS вложенные запросы тут, в FB1.5, не работают.
 

[Dale]

Если здешний диалект SQL поддерживает операцию IN, можно заменить

Код:

        (nua_type!=78) -- !='N'
    AND (nua_type!=85) -- !='U'
    AND (nua_type!=65) -- !='A'

на

Код:

    nua_type NOT IN (78, 85, 65)

и, сооответственно,

Код:

    (nua_type=85 OR nua_type=65) -- ='U' OR ='A'

   

на

Код:

    (nua_type IN (85, 65))

   

Если есть функция ISNULL, как в T-SQL, или ее аналог, то
 

Код:

    (:messfilter_Rest IS NULL OR :messfilter_Rest=1)

можно заменить на

Код:

    (ISNULL(:messfilter_Rest, 1) = 1)

А здесь:

Код:

    nua_type=78 -- ='N'
    AND (mess_type!=92) -- != cNT_4p2_CMD_92_txt
    AND (:messfilter_barr IS NULL OR :messfilter_barr CONTAINING '~'||mess_type||'~') -- ... OR '~20~45~17~'CONTAINING ~17~
OR
    nua_type=78 -- ='N'
    AND (mess_type =92) -- =cNT_4p2_CMD_92_txt
    AND (:messfilter_Lars IS NULL OR :messfilter_Lars CONTAINING '~'||typefor_clientfilter||'~')

имеем логическое выражение типа:

Код:

(A & ~B & C) | (A & B & D)

Очевидно, A можно вынести за скобку, чтобы не считать дважды:

Код:

A & ((~B & C) | (B & D))

[Dimka]

Dale, лично я не уверен, что in будет быстрее цепочки or-условий.

Алексей1153++, оптимизировать - чтобы быстрее работало?

[Алексей++]

спасибо, замечания опробовал (только isnull нету)

Код:

   WHERE
        MESSAGE_ID>:ID_toLoadrfomExcluding
    AND MESSAGE_ID<=:ID_Max 
    AND (
                 nua_type not in(78,85,65) -- nua_type!=('N','A','U')
           OR
                 nua_type in(85,65) -- nua_type=('U','A')
             AND (:messfilter_Rest IS NULL OR :messfilter_Rest=1)
           OR
             nua_type=78 -- ='N'
             AND
             (
                     (mess_type!=92) -- != cNT_4p2_CMD_92_txt
                 AND (:messfilter_barr IS NULL OR :messfilter_barr CONTAINING '~'||mess_type||'~') -- ... OR '~20~45~17~'CONTAINING ~17~
               OR
                     (mess_type =92) -- =cNT_4p2_CMD_92_txt
                 AND (:messfilter_Lars is NULL or :messfilter_Lars containing '~'||typefor_clientfilter||'~')
             )
        )

а вот насчёт containing - тут ничего ускорить нельзя ? Оно, конечно, оно понятно, что можно было бы фильтр делать не в виде
 ~1~2~3~

 а в виде
 in(1,2,3)

Но тут есть одно НО - containing позволяет делать процедуру встроенной (как и сделано), а с in() придётся запускать запрос из программы. Вот вопрос - что быстрее отработает, встроенная с containing, или запрос с in ?

Добавлено через 21 секунду:

Dale, лично я не уверен, что in будет быстрее цепочки or-условий.

Алексей1153++, оптимизировать - чтобы быстрее работало?

да, скорость очень приветствуется

[HandKot]

я конечно может что-то и не догоняю, НО
либо если со скобками все верно, тогда весь кусок можно выкинуть (OR съедает весь AND)
либо там какая-то хитрая проверка на условия (до первого TRUE)

[Алексей++]

HandKot, не могу понять, где именно ?

[HandKot]

тута, внутри скобок

Код:

   AND (
                 nua_type not in(78,85,65) -- nua_type!=('N','A','U')
           OR
                 nua_type in(85,65) -- nua_type=('U','A')
             AND (:messfilter_Rest IS NULL OR :messfilter_Rest=1)
           OR
             nua_type=78 -- ='N'
             AND
             (
                     (mess_type!=92) -- != cNT_4p2_CMD_92_txt
                 AND (:messfilter_barr IS NULL OR :messfilter_barr CONTAINING '~'||mess_type||'~') -- ... OR '~20~45~17~'CONTAINING ~17~
               OR
                     (mess_type =92) -- =cNT_4p2_CMD_92_txt
                 AND (:messfilter_Lars is NULL or :messfilter_Lars containing '~'||typefor_clientfilter||'~')
             )
        )

имеем

Код:

nua_type not in (78,85,65)

и тут же

Код:

nua_type in(85,65)

и

Код:

nua_type=78 

что, по моему мнению, убивает все AND
получается, что мы можем ими принебречь

или же запись должна быть такая (добавлены скобки)

Код:

                nua_type not in(78,85,65) -- nua_type!=('N','A','U')
           OR 
           (
                 nua_type in(85,65) -- nua_type=('U','A')
             AND (:messfilter_Rest IS NULL OR :messfilter_Rest=1)
           )
           OR
           (
             nua_type=78 -- ='N'
             ...
            )

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

[Алексей++]

HandKot, но на практике это всё разное

nua_type==1...64 первое условие
nua_type==65  второе условие
nua_type==66...77 первое условие
nua_type==78 третье условие
nua_type==79...84 первое условие
nua_type==85 второе условие
nua_type==86...+ первое условие


Добавлено через 3 минуты и 38 секунд:
правда, диапазонов нет. Есть только эти три значения и всё остальное

[HandKot]

HandKot, но на практике это всё разное

nua_type==1...64 первое условие
nua_type==65  второе условие
nua_type==66...77 первое условие
nua_type==78 третье условие
nua_type==79...84 первое условие
nua_type==85 второе условие
nua_type==86...+ первое условие


Добавлено через 3 минуты и 38 секунд:
правда, диапазонов нет. Есть только эти три значения и всё остальное

а вот с этого момента, поподробнее

[Алексей++]

ну, дык, так и есть:

если nua_type - не равно 78, или 85 или 65 (то есть, другое любое значение) - это первая ветка
иначе
если nua_type - равно 85 или 65 и (фильтр_пуст или применять_фильтр_не_надо) - это вторая ветка
иначе третья ветка
        если nua_type - равно 78
            то для кода mess_type=92 применить фильтр 1 , а для остальных кодов фильтр 2
       



Добавлено через 1 минуту и 18 секунд:
блин, домой приду - надо будет всё через буквы обозначить и упростить, если возможно ))

Добавлено через 5 минут и 6 секунд:
вот это надо будет упростить надо и станет видно. Это я вечером, щас не хочу грузиться

Код:

      (
               (  !A78 & !A85 & !A65 )
           |
               (  A85 | A65 )
             & (B1 | B2)
           |
             A78
             &
             (
                     (!C92)
                 & (D1 | D2)
               |
                     (C92) 
                 & (E1 | E2)
             )
        )

[RXL]

Еще:

Код:

   WHERE
        MESSAGE_ID>:ID_toLoadrfomExcluding
    AND MESSAGE_ID<=:ID_Max 

Код:

   WHERE
        MESSAGE_ID BETWEEN :ID_toLoadrfomExcluding + 1 AND :ID_Max 

Добавлено через 6 минут и 30 секунд:
Логические блоки надо окружать скобками - тогда путаницы будет меньше.

Добавлено через 2 минуты и 56 секунд:

Код:

                 (nua_type!=78) -- !='N'
             AND (nua_type!=85) -- !='U'
             AND (nua_type!=65) -- !='A'

Называть поле по контенту - это как-то странно.

[HandKot]

можно попробовать испоьзовать CASE (если такой есть)

[Алексей++]

RXL, по названиям - это наследие, тяжёлое. Исправлять нельзя

Добавлено через 20 часов, 34 минуты и 15 секунд:
ещё промежуточный вопрос

Код:

select
(case MESS_FILTER      when null then MESS_FILTER_N     when '' then MESS_FILTER_N    else MESS_FILTER end)MESS_FILTER_N
 from ws_rights

происходит ругань на "when null " , а как указать условие, когда значение поля = NULL ?

Собственно, тут нужно: если поле MESS_FILTER - пустая строка или NULL, то выбрать MESS_FILTER_N, инаяе выбирается MESS_FILTER

Добавлено через 28 минут и 21 секунду:
кстати, насчёт скорости ещё такой вопрос:

допустим, выборка содержит 1000000 записей. Будет ли быстрее, если в программе я буду выбирать не сразу всю кучу записей, а частями, скажем , по 20% ?

[Алексей++]

а условие у меня окончательно в таком виде

Код:

   FROM MESS_LOG WHERE (MESSAGE_ID between:ID_toLoadrfomExcluding+1 and :ID_Max) 
   and 
    ( 
        (
                  -- 'N'
             NUA_TYPE=78
        )
        and
        (
             -- cNT_4p2_CMD_92_txt 
            (MESS_TYPE!=92) and (:messfilter_barr is null or :messfilter_barr containing '~'||MESS_TYPE||'~')
         or
            (MESS_TYPE =92) and (:messfilter_Lars is null or :messfilter_Lars containing '~'||TYPEFOR_CLIENTFILTER||'~')
        )
      
      or
             --  'U'    'A'
        (NUA_TYPE=85 or NUA_TYPE=65) AND (:messfilter_Rest is null or :messfilter_Rest=1)
      
      or
             --    'N'             'U'              'A'
        (NUA_TYPE!=78 and NUA_TYPE!=85 and NUA_TYPE!=65)
    )

расставил по вероятности - наиболее вероятно, что NUA_TYPE=78 , затем, что NUA_TYPE=85 or NUA_TYPE=65, затем только всё остальное.

Имеет ли смысл в порядке вероятности выражения между OR расставлять ?

[Dale]

Имеет ли смысл в порядке вероятности выражения между OR расставлять ?

Однозначно - имеет. Ведь в выражении "A OR B", если A истинно, то проверять B уже не имеет смысла - все равно выражение в целом истинно, независимо от значения B. Поэтому если A вероятнее, чем B, лучше его поставить первым.

А насчет AND ситуация противоположная. В выражении "A AND B", если A ложно, то все выражение в целом ложно, и B можно не вычислять. Поэтому выгоднее поставить первым сомножитель с меньшей вероятностью истинности.

P.S. Конечно, рекомендация имеет смысл лишь в том случае, если затраты на вычисление A и B одного порядка. Если, допустим, истинность A имеет в 10 раз более высокую вероятность, чем B, но при этом A вычисляется в 1000 раз дольше B, лучше считать в таком порядке: "B OR A". Лучше 10 раз вычислить B, потратив 10 условных тактов, и один раз из 10 сэкономить дорогостоящее вычисление A, чем считать A каждый раз, время от времени экономя такт на ненужное вычисление B.

[RXL]

Леш, после BETWEEN положен пробел!

Добавлено через 3 минуты и 38 секунд:
Dale, но если на сервере есть оптимизатор, то он может переставлять части выражения. Не знаю как в FB, а в MySQL (если рассматривать СУБД одного класса) он есть и подавно есть в более продвинутых СУБД. Но принцип самоогранизации разработчика - поддерживаю!

[Sla]

Имеет ли смысл в порядке вероятности выражения между OR расставлять ?

Однозначно - имеет. Ведь в выражении "A OR B", если A истинно, то проверять B уже не имеет смысла - все равно выражение в целом истинно, независимо от значения B. Поэтому если A вероятнее, чем B, лучше его поставить первым.

А насчет AND ситуация противоположная. В выражении "A AND B", если A ложно, то все выражение в целом ложно, и B можно не вычислять. Поэтому выгоднее поставить первым сомножитель с меньшей вероятностью истинности.

P.S. Конечно, рекомендация имеет смысл лишь в том случае, если затраты на вычисление A и B одного порядка. Если, допустим, истинность A имеет в 10 раз более высокую вероятность, чем B, но при этом A вычисляется в 1000 раз дольше B, лучше считать в таком порядке: "B OR A". Лучше 10 раз вычислить B, потратив 10 условных тактов, и один раз из 10 сэкономить дорогостоящее вычисление A, чем считать A каждый раз, время от времени экономя такт на ненужное вычисление B.

Блин... Как знакомо!!! Иногда с пеной у рта доказывал человеку, что так правильно (правда там шло чтение с медленных девайсов)

[Dale]

Dale, но если на сервере есть оптимизатор, то он может переставлять части выражения.

Ключевое слово - может. А захочет ли, сие никому не ведомо. Поэтому, как говорится, на оптимизатор надейся...

К тому же есть основания сомневаться, что оптимизируется порядок вычисления логических выражений. Например, насколько я помню стандарт C99, там оговаривается строгий порядок вычисления слева направо (в отличие от арифметических), и если мне совсем уж не изменяет память, между первым и вторым операндами имеется sequence point. За последнее не ручаюсь, знатоки стандарта - поправьте, пожалуйста, если ошибаюсь.

Есть сильное подозрение, что это не фича C99, а стандартный подход для всех языков.

[RXL]

Dale, при чем тут C99, если речь о SQL?..

[Dale]

Dale, при чем тут C99, если речь о SQL?..

Ибо:

Есть сильное подозрение, что это не фича C99, а стандартный подход для всех языков.

P.S. Подразумевается, что SQL входит в понятие "все языки".

[RXL]

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

[Dale]

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

[RXL]

Если говорить за Oracle, то при заранее собранной статистике он представляет себе средний объем обрабатываемых данных еще до выборки.

[Dale]

Я не про объем, а про конкретные значения параметров. Если данные будут поступать от клиентской программы, то статистику их распределения заранее узнать невозможно. А значит, любая заранее выполненная оптимизация на этот счет несостоятельна.

[Алексей++]

Dale, ну, вроде, я и расставил так, где меньше вычислять

Леш, после BETWEEN положен пробел!

ага, я добавил. Но, похоже, двоеточие тоже за разделитель канает ))

[Dale]

Dale, ну, вроде, я и расставил так, где меньше вычислять

К сожалению, не нашел документального подтверждения нашему предположению.

Перерыл стандарт ISO/IEC 9075:1992, но там ни единого слова насчет вычисления булевых выражений. Вообще стандарт какой-то невразумительный в целом. А более нового у меня, к сожалению, нет.

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

Так что можем решать этот вопрос голосованием, перетягиванием каната или гаданием на кофейной гуще. Более научного способа мне найти не удалось. Но хуже от такого упорядочения наверняка не будет.

[Алексей++]

я попробую попозже потестировать. Сейчас пока так оставлю - лопатить ещё много все

[RXL]

Я не про объем, а про конкретные значения параметров. Если данные будут поступать от клиентской программы, то статистику их распределения заранее узнать невозможно. А значит, любая заранее выполненная оптимизация на этот счет несостоятельна.

Не понял? Речь о вставке? К поиску данных и оптимизации это никакого отношения не имеет.

А параметры - это поддается машинному пониманию.

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

[Dale]

Не понял? Речь о вставке? К поиску данных и оптимизации это никакого отношения не имеет.

О выборке из данных, поступающих в реальном времени. Во время компиляции представления или хранимой процедуры этих данных еще нет, поэтому предсказать распределение движок базы не может. Да и впоследствии поддержание статистики - довольно затратная процедура, не дешевле индексирования. По крайней мере, у MS SQL 2005 так. Помню, потому что не так давно зубрил оптимизацию запросов для сдачи экзаменов, там как раз были подобные вопросы.

Не может быть универсальной оптимизации.

Универсальной - нет. Любая оптимизация основана на наших предварительных знаниях о распределении данных, которые впоследствии поступят в систему. Но насколько я понял, Алексей1153++ такой статистикой располагает, поэтому вполне может организовать более рациональную обработку. Лишь бы предположение о замыкании логических связок оправдалось, явного подтверждения у меня пока нет.

[Алексей++]

верятности тут очевидны (для меня) и вряд ли поменяются:

'N' - это сообщение от прибора (их туча)
'A' - сообщение, сгенерированное программой (их немного)
'U' - сообщение, сгенерированное пользолвателем (тоже немного)
'...' - другие сообщения, вообще редко

код !=92, тип 'N' - сообщения от наших контрорских приборов. Их преобладающее количество в эфире
код ==92, тип 'N' - сообщения от Lars - их гораздо меньше