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

[Алексей++]

продолжение

известно, что для каждого типа ('N','U','A',...) не нужны все записи таблицы лога. То есть, к примеру, для первого типа нужны 10 полей, для второго 5 полей, для третьего 7 полей. Как-то с таким знанием можно ещё оптимизацию провести? Тут дело в чём - клиент тащит по локальной сети из БД записи для себя, а сделано немного коряво, так что записей тащится вагон

[Sla]

а есть что-нибудь типа limit, top, rownums?

[Алексей++]

а это что, зачем

[baldr]

Мне кажется это можно через HAVING сделать.. Лех, приведи запрос как он сейчас есть?

[RXL]

Леш, попробуй в SELECT выражение типа:

Код: (SQL)

  CASE nua_XYZ
    WHEN 'N' THEN field_XYZ
    WHEN 'U' THEN field_XYZ
    ELSE NULL
  END AS field_XYZ

Длинновато, но для 'N' и 'U' вернется полноценное поле, а для остальных - только NULL.

Т.е. выводи все 10 полей, но ненужные в ненужных условиях заменяй на NULL.

[Алексей++]

baldr, щас скомпилю, изображу Дома машинка не шустрая.

RXL, длинно - это ладно, больше интересует, скажется ли это на производительности выборки ?

[Алексей++]

baldr, вот

Код:

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_Rest имеет значение NULL, 0 или 1 - чисто для U и A сообщений
    -- :messfilter_barr имеет вид '~dec1~dec2~dec3~'
    -- :messfilter_barr VARCHAR(e_m_cs_FilterForMail_Param_Barr_LEN_tx)
    -- :messfilter_Lars имеет вид '~dec1~dec2~dec3~'
    -- :messfilter_Lars VARCHAR(e_m_cs_FilterForMail_Param_Lars_LEN_tx)
   for
   SELECT 
  MESSAGE_ID
, QUIT_DATE
, DMESSAGE
, NOMER                 
, RAZDEL                
, MESS_TYPE             
, QUIT_REASON           
, COLOR                 
, NUA_TYPE              
, NEW_COD_X77_MESS      
, DATAWORD              
, CANAL_NUMBER          
, CANALNUMBER_PER       
, POWER_LEVEL           
, USERID                
, OPERID                
, F_18_GUARDEDNOW       
, TAGPOINTID32          
, NON_TECH_SMS_COUNT    
, ADR_GUID_M4S          
, CHOP_NUM_M4S          
, DELTA_TIME_CHOP_M4S   
   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)
    )
   ORDER BY MESSAGE_ID DESC
   into 
 :MESSAGE_ID
,:QUIT_DATE
,:DMESSAGE
,:NOMER                 
,:RAZDEL                
,:MESS_TYPE             
,:QUIT_REASON           
,:COLOR                 
,:NUA_TYPE              
,:NEW_COD_X77_MESS      
,:DATAWORD              
,:CANAL_NUMBER          
,:CANALNUMBER_PER       
,:POWER_LEVEL           
,:USERID                
,:OPERID                
,:F_18_GUARDEDNOW       
,:TAGPOINTID32          
,:NON_TECH_SMS_COUNT    
,:ADR_GUID_M4S          
,:CHOP_NUM_M4S          
,:DELTA_TIME_CHOP_M4S   
   do suspend;
 
 end

[RXL]

Леш, в чем тут оптимизация?

[Алексей++]

тут её ещё нету, это по просьбе Лёхи я привёл текст процедуры

[RXL]

Offtopic:

Поле NEW_COD_X77_MESS часом не в честь Игоря названо?

Поставлю в угол.

[Алексей++]

RXL, хм, я даже не обращал внимания )))) А ведь, вполне может быть.  Это флаг, показывающий, что используется новая кодировка, он как-то раньше назывался вообще незапоминаемо, я его года 3 назад переназвал, чтоб точно помнить  - вполне может быть, что по нику Игоря ))) Наверное, был очередной аврал

[baldr]

Алексей1153++, а в условии тебе советовали CASE использовать - есть такой оператор в ОгнеПтице?
А чтобы уменьшить кол-во записей.. Сорри что может немного не работать - писал для MySQL, но идея должна быть понятна:

Код:

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_Rest имеет значение NULL, 0 или 1 - чисто для U и A сообщений
    -- :messfilter_barr имеет вид '~dec1~dec2~dec3~'
    -- :messfilter_barr VARCHAR(e_m_cs_FilterForMail_Param_Barr_LEN_tx)
    -- :messfilter_Lars имеет вид '~dec1~dec2~dec3~'
    -- :messfilter_Lars VARCHAR(e_m_cs_FilterForMail_Param_Lars_LEN_tx)
    SET @a=0;
    SET @b=-1;
   for
   SELECT MESSAGE_ID
, QUIT_DATE
, DMESSAGE
, NOMER                 
, RAZDEL                
, MESS_TYPE             
, QUIT_REASON           
, COLOR                 
, NUA_TYPE              
, NEW_COD_X77_MESS      
, DATAWORD              
, CANAL_NUMBER          
, CANALNUMBER_PER       
, POWER_LEVEL           
, USERID                
, OPERID                
, F_18_GUARDEDNOW       
, TAGPOINTID32          
, NON_TECH_SMS_COUNT    
, ADR_GUID_M4S          
, CHOP_NUM_M4S          
, DELTA_TIME_CHOP_M4S FROM (
   SELECT 
  MESSAGE_ID
, QUIT_DATE
, DMESSAGE
, NOMER                 
, RAZDEL                
, MESS_TYPE             
, QUIT_REASON           
, COLOR                 
, NUA_TYPE              
, NEW_COD_X77_MESS      
, DATAWORD              
, CANAL_NUMBER          
, CANALNUMBER_PER       
, POWER_LEVEL           
, USERID                
, OPERID                
, F_18_GUARDEDNOW       
, TAGPOINTID32          
, NON_TECH_SMS_COUNT    
, ADR_GUID_M4S          
, CHOP_NUM_M4S          
, DELTA_TIME_CHOP_M4S
, @a:=if(@b=NUA_TYPE, @a, 0)+1 a, @b:=NUA_TYPE b
, case NUA_TYPE 
    when 1 then 10 
    when 2 then 5
    when 3 then 7
    end typelimit
   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)
    )
   ORDER BY NUA_TYPE) Q1 where a<typelimit ORDER BY MESSAGE_ID DESC
   into 
 :MESSAGE_ID
,:QUIT_DATE
,:DMESSAGE
,:NOMER                 
,:RAZDEL                
,:MESS_TYPE             
,:QUIT_REASON           
,:COLOR                 
,:NUA_TYPE              
,:NEW_COD_X77_MESS      
,:DATAWORD              
,:CANAL_NUMBER          
,:CANALNUMBER_PER       
,:POWER_LEVEL           
,:USERID                
,:OPERID                
,:F_18_GUARDEDNOW       
,:TAGPOINTID32          
,:NON_TECH_SMS_COUNT    
,:ADR_GUID_M4S          
,:CHOP_NUM_M4S          
,:DELTA_TIME_CHOP_M4S   
   do suspend;
 
 end

Для начала можно тут убрать typelimit (во внешнем select заменить его на 10 в where, например) и case - и посмотреть будет ли возвращать по 10 записей

[Алексей++]

подзапрос тут точно не работает

case есть, работает. Но с ним по быстрому у меня не переделать, я пока не стал. Дело в том, что этот длинный список параметров повторяется много где, и я сделал его генерируемым из мапы (знаки препинания только нужные подставляются где надо - для select одни, для update и insert - другие)
Тут с разбегу не поменяешь на case

[baldr]

подзапрос тут точно не работает

Опа... А почему?? А какие есть альтернативы?

[Алексей++]

В данном случае - никаких, видимо.

Добавлено через 2 минуты и 32 секунды:
ладно, я думаю, что данный этап - это предел, дальше уже особо не соптимизируешь ЭТО )))
Посмотрим на реакцию пользователей. Если не будут жаловаться, пущай так бегает

[Dimka]

Если нет подзапроса, есть ли временные таблицы или поддержка динамического SQL?

[Алексей++]

>>временные таблицы
не знаю, как проверить ?

>>динамического SQL
- я же правильно понимаю, что это запрос, который вызывается не внутри встроенной процедуры, а из внешней программы? Такое есть, конечно

[Dimka]

не знаю, как проверить ?

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

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

Например, где-то нужно из таблицы с ~1M записей получить выборку ~100 записей. И для всяких расчётов этот подзапрос нужно сделать 2 или более раз в рамках одного большого запроса. В таком случае временная таблица, кэширующая 100 записей, радикально повышает производительность. Оптимизаторы не всегда хорошо с этим справляются, а у FB, наверно, и оптимизаторы посредственные.

Общее правило: как можно раньше по ходу выполнения запроса (или серии запросов) отсекать все ненужные данные.

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

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

[Алексей++]

Dimka, ну, FB умеет читать запрос из файла, но сильно сомневаюсь, что тут будет космическая скорость

а количество записей у меня, к сожалению, выбирается не 100, а обычно от нескольких сотен тысяч до несколько миллионов (настраивается под резвость машины)

[baldr]

Алексей1153++, если не поддерживаются подзапросы (%плохое слово%!), то можно использовать View?
И рассматривался ли вопрос об обновлении FireBird до, хотя бы, версии 2?

[Алексей++]

Код:

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

я не знаю. Как это определить ?

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

[RXL]

baldr, если твой пример для MySQL, то у тебя есть некритичная ошибка: собаками предваряются только переменные сессии и глобальные для всего сервера (типа переменных окружения). Локальные переменные процедур объявляются через DECLARE и не имеют префиксов.

подзапрос тут точно не работает

Опа... А почему?? А какие есть альтернативы?

Без подзапросов чаще всего можно обойтись (ведь работали же с MySQL 3.23 много лет и не страдали). Внутреннее или внешнее объединение поможет.
В редких случаях - как сказал Димка - через временные таблицы.
Еще реже - через несколько запросов и перенос результатов через переменные.

[Алексей++]

да, можно организовать "подзапрос" в процедуре так:

Код:

CREATE PROCEDURE PR
returns(
   a integer
  ,b integer
  ,c integer
)
as
begin
 for
   select a from T where ... into :a
 do
   for
     select b,c  from T where a=:a into :b,:c
   do
   begin
     suspend;--вертаем очередную запись в результате работы процедуры
   end;
end;

[RXL]

Леш, если пример не совсем абстрактный, то смысла в двух запросах нет: "a = :a" - просто переносим WHERE из первого запроса во второй.

[Алексей++]

абстрактный. Вместо многоточия перед where ещё что-то может быть