Существует таблица товаров Prdca и существует таблица продаж этого товара (с кодом из справочника Dsptch_Prdca_ID) с указанием количества (Dsptch_Plan) и даты продажи (Dsptch_Date). Надо выбрать товары с самой поздней датой продажи.
Сделал запрос:


Без строчки с ORDER BY выполняется около 15 сек., а с ORDER BY выполняется почти 15 МИНУТ(!!!)
Можно ли как-нибудь оптимизировать запрос для уменьшения времени выполнения?

Приведи детальный план запроса

План
PLAN (D2 NATURAL)
PLAN JOIN (D1 NATURAL,PRDCA INDEX (RDB$PRIMARY3))

Адаптированный план
PLAN (D2 NATURAL) PLAN JOIN (D1 NATURAL,PRDCA INDEX (PK_PRDCA))

------ Performance info ------
Prepare time = 0ms
Execute time = 14s 875ms
Avg fetch time = 619.79 ms
Current memory = 5 664 764
Max memory = 5 803 809
Memory buffers = 1 262
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 18 534 461

Я правильно понял?

Еще приведи статистику использования индексов при выполнении запроса

and D1.Dsptch_Date = (select max(Dsptch_Date)
                      from Dsptch D2
                      where D2.Dsptch_Prdca_ID=D1.Dsptch_Prdca_ID)
AND DSPTCH_MMYY = '1008'

заменить на
and D1.Dsptch_Date = (select max(Dsptch_Date)
                      from Dsptch D2
                      where D2.Dsptch_Prdca_ID=D1.Dsptch_Prdca_ID AND DSPTCH_MMYY = '1008')

будет не правильнее?

Таблицы у меня без индексов.
Работаю на IB Expert. Нашел еще вот это:
Query
------------------------------------------------
/*
Текст запроса для формирования новой разнарядки по НОВОМУ алгоритму.

Из справочника изданий нужно выбрать издания отмеченные как "ВКЛ."
Для этих изданий надо выбрать записи из разнарядки за прошлый заданный период DSPTCH_MMYY
с последней датой формирования Dsptch_Date и переписать значение Dsptch_Plan
*/

SELECT Dsptch_Prdca_ID, Dsptch_PostOffice_ID, Dsptch_Plan,
      Dsptch_MMYY, Dsptch_Date
FROM Dsptch D1, Prdca
WHERE Dsptch_Prdca_ID = prdca_id
and D1.Dsptch_Date = (select max(Dsptch_Date)
                       from Dsptch D2
                       where D2.dsptch_prdca_id = D1.dsptch_prdca_id)
AND DSPTCH_MMYY = '1008'
and Prdca_Pttrn = 'Вкл.'
/* ORDER BY Dsptch_Prdca_ID, Dsptch_PostOffice_ID   14мин 49 сек.*/
/* ORDER BY Dsptch_Prdca_ID, Dsptch_Date, Dsptch_PostOffice_ID  14мин 42 сек.*/

/*Текст запроса для РАЗНАРЯДКИ (и для TfrmWork.dbgWorkTitleClick разнарядки)*/
/*
SELECT Prdca_Index, Prdca_Name, PostOffice_Name, PostOffice_Tract,
      PrHsNN_Quantity, Dsptch_Plan*PrHsNN_Quantity  PlQn,
      PrHsInit_Initial, PrHsPrice_SumPblcHs, PrHsPrice_RetaPrc,
      Dsptch_ID, Dsptch_Prdca_ID, Dsptch_PostOffice_ID,
      Dsptch_Plan, Dsptch_PrHsInit_ID, Dsptch_PrHsPrice_ID,
      Dsptch_NotSent, Dsptch_WritOff_Act,
      Dsptch_MMYY, Dsptch_Date, Dsptch_Note
FROM Dsptch, Prdca, PostOffice,  PrHsPrice, PrHsInit, PrHsNN
WHERE Dsptch_Prdca_ID = Prdca_ID
AND Dsptch_PrHsPrice_ID  = PrHsPrice_ID
AND Dsptch_PrHsInit_ID   = PrHsInit_ID
AND Dsptch_PostOffice_ID = PostOffice_ID
AND Dsptch_PrHsNN_ID = PrHsNN_ID
AND DSPTCH_MMYY = '1008'
ORDER BY Dsptch_Date, Prdca_Name, PostOffice_Name
*/

Plan
------------------------------------------------
PLAN (D2 NATURAL)
PLAN JOIN (D1 NATURAL,PRDCA INDEX (RDB$PRIMARY3))

Adapted Plan
------------------------------------------------
PLAN (D2 NATURAL)

PLAN JOIN (D1 NATURAL,PRDCA INDEX (PK_PRDCA))

Query Time
------------------------------------------------
Prepare       : 0.00 ms
Execute       : 14 875.00 ms
Avg fetch time: 619.79 ms

Memory
------------------------------------------------
Current: 5 704 700
Max    : 5 838 940
Buffers: 1 262

Operations
------------------------------------------------
Read   : 0
Writes : 0
Fetches: 18 534 461

Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
|        Table Name        |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts |
|                          |   Total   |   reads   |    reads    |         |         |         |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
|                    DSPTCH|         0 |         0 |     9071600 |       0 |       0 |       0 |
|                     PRDCA|         0 |       328 |           0 |       0 |       0 |       0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+

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

Извините лишнее скопировалось :о((

> Sergey13 ©   (21.11.08 12:07) [4]

Без ORDER BY время выполнения увеличилось с 15 сек, до почти 18 сек.
С ORDER BY пока не рискнул запускать. Чтобы долго не ждать.

> Еще приведи статистику использования индексов при выполнении
> запроса

Прошу прощения. Я сделал "большую" вставку с быстрого запроса, т.е. без ORDER BY.
Вы мне скажите, я правильно хотя бы выбрал информацию  с закладки: Анализ производительности -> 2.Additional  
Если это то, о чем Вы просили, то я запущу "долгий" запрос и пришлю это же снова.

> Таблицы у меня без индексов


Каких же чудес ты тогда ждешь, если их нет ?!
Чудес не жди, их не будет, пока не будет нужных индексов.

> я правильно хотя бы выбрал информацию  с закладки: Анализ
> производительности -> 2.Additional  


Да, правильно.

> пока не будет нужных индексов.

Посоветуйте, пожалуйста, какой индекс тогда лучше сделать?
По Dsptch_Prdca_ID, или Dsptch_Prdca_ID + Dsptch_Date?
И как их тогда надо использовать в запросе?

индексы нужны нато, по каким полям сортировка + по каким полям соединение таблиц

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

а вот такого вида запрос в IB6.x(кстати ????) пройдет



конечно неясна структура таблиц, т.что связи наверняка неправильные, и непонятно зачем "присобачивается" Prdca, поэтому ее игнорировал, но по смыслу, сам стиль, объединение с подзапросом пройдет?
(если пройдет, то все лучше/быстрее будет один вызов подзапроса чем на каждую интерацию)

Извините за задержку, поздравляли сотрудника с днем рождения :о)
Однако...
Структуру привожу из скрипта таблиц:
CREATE TABLE DSPTCH (
   DSPTCH_ID             INTEGER,
   DSPTCH_PRDCA_ID       SMALLINT,
   DSPTCH_PRHSPRICE_ID   INTEGER,
   DSPTCH_PRHSINIT_ID    INTEGER,
   DSPTCH_PRHSNN_ID      INTEGER,
   DSPTCH_POSTOFFICE_ID  SMALLINT,
   DSPTCH_PLAN           SMALLINT,
   DSPTCH_NOTSENT        SMALLINT,
   DSPTCH_WRITOFF        SMALLINT,
   DSPTCH_WRITOFF_ACT    INTEGER,
   DSPTCH_MMYY           VARCHAR(4),
   DSPTCH_DATE           TIMESTAMP,
   DSPTCH_NOTE           VARCHAR(15)
);

CREATE TABLE PRDCA (
   PRDCA_ID        INTEGER,
   PRDCA_INDEX     VARCHAR(5),
   PRDCA_NAME      VARCHAR(35),
   PUBLICHOUSE_ID  INTEGER,
   PRDCA_PTTRN     VARCHAR(6)
);

А где описание и назначение каждого из полей ?

Dsptch_Prdca_ID - поле ссылки на таблицу справочника товара Prdca
Dsptch_PostOffice_ID - поле ссылки на таблицу справочника торговой точки. Она в результате запроса нужна для оценки реализации товара по точкам.
Dsptch_Plan - количество товара (план) для реализации в торговой точке.
Dsptch_MMYY - отчетный период (месяц+год) для реализации товара
Dsptch_Date - дата "внутри" отчетного периода, для которого заполняется количество реализованного товара

Prdca_ID - код товара из справочника товара Prdca
Prdca_Pttrn - поле из справочника товара Prdca, для отметки товара, по которым будет происходить выборка для отчета.


> sniknik ©   (21.11.08 13:50) [14]

Увы, запрос не прошел. Курсор останавливается после join и пишет:
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 11, char 13.
select.

> sniknik  (21.11.2008 13:50:14)  [14]

А ИБ6 поддерживает вложеные запросы.

> sniknik  (21.11.2008 13:50:14)  [14]

Кстати ты помнишь в ветке где категорически рекомендуют начинающим ИБ и его клоны, так вот отсутствие вложеных запросов одназначно закрыло мне путь к этой СУБД.

> Anatoly Podgoretsky ©   (21.11.08 15:05) [19]

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

> Увы, запрос не прошел. Курсор останавливается после join и пишет:
тогда забудь.

> ИБ и его клоны
IB ладно, черт с ним, но вот клоны... например про Firebird 2, читал/говорили что уже почти всю функциональность mssql поддерживает, и + что то исконно свое.

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

> sniknik  (21.11.2008 15:14:21)  [21]

Врут, но бог с ними, а мне что надо было ждать более 10 лет?
Вроде только где то в 7 версии появились встроеные запросы? Я точно не помню.
FireBird хоть он и не применим в данном случае, тоже не сразу обзавелся встроеными запросами.

ждать это конечно ;(...
но вот тем кто начинает сейчас это повод хотя бы не использовать старые версии (если не говорить о переходе на что то иное ;), не представляю, как без них?
не умом конечно понимаю, можно сделать процедуру, в ней цикл по курсору, т.е. добиться того же самого другими методами, но... это уже будет не язык общения с субд, а какой то суррогат с подменой самых нужных слов чем то искусственным.

Я выбираю СУБД не по номеру версии, а под задачу, подойдет ли, и при том по очень многим факторам. Иногда выбирать не приходится, когда покупная программа, от которой нельзя отказаться или альтернативы еще хуже.

> Посоветуйте, пожалуйста, какой индекс тогда лучше сделать?
>


Как минимум на поля DSPTCH_PRDCA_ID, DSPTCH_DATE (это - обязательно!). По правилам разработки баз данных нужны первычные ключи DSPTCH_ID и PRDCA_ID.

Кстати, почему DSPTCH_PRDCA_ID - SMALLINT, а PRDCA_ID - INTEGER?

> sniknik ©   (21.11.08 15:14) [21]
>
> > Увы, запрос не прошел. Курсор останавливается после join
> и пишет:
> тогда забудь.
>
> > ИБ и его клоны
> IB ладно, черт с ним, но вот клоны... например про Firebird
> 2, читал/говорили что уже почти всю функциональность mssql
> поддерживает, и + что то исконно свое.

в 2,5 встроили регулярные выражения. правда, она пока альфа(эта версия)

а по теме: убери подзапрос. сделай либо ХП, либо перейди на ФБ 2,х, там можно выполнить блок. тогда запрос можно переделать так
execute block
as
declare variable d date;
begin
select max(Dsptch_Date)
                      from Dsptch D2
                      where D2.Dsptch_Prdca_ID=D1.Dsptch_Prdca_ID into :d;

SELECT Dsptch_Prdca_ID, Dsptch_PostOffice_ID, Dsptch_Plan,
     Dsptch_MMYY, Dsptch_Date
FROM Dsptch D1, Prdca
WHERE Dsptch_Prdca_ID = Prdca_ID
and D1.Dsptch_Date = :d
AND DSPTCH_MMYY = '1008'
and Prdca_Pttrn = 'Вкл.'
ORDER BY Dsptch_Prdca_ID, Dsptch_PostOffice_ID;
suspend;
end;
Примерно так, но тут неправильно 100%..:-)

> либо перейди на ФБ 2,х
и проверь там возможность нормальных подзапросов, объединений с ними.

> Примерно так, но тут неправильно 100%..:-)
не знаю как синтаксис, но тут и по логике не то, тут выбираются все записи на одну максимальную дату. и вполне вероятно что это будет всего одна запись, либо все из одной накладной (например), смотря как там, при каких условиях,  у него идет изменение этого поля индивидуально карточками товара либо накладными.
а по задаче, имхо, должны выбираться все товары с максимальной датой изменения каждого.
если не так, и нужно именно то, что ты написал, то легко просто выполнить 2 запроса подряд, первым получить дату, и передать ее во второй параметром, и ничего менять в смысле версии будет не надо.

> sniknik ©   (22.11.08 11:11) [29]

я просто привел макет. а вся тормознутось оригинального запроса - из-за подзапроса...это "больное" место у ФБ(IB)... в плане скорости. лучше переписать в конструкцию for select do... .а в 2,х уже спокойно можно выполнить
> sniknik ©   (21.11.08 13:50) [14]