Оптимизация запроса

Кузнецов Евгений

S.H.S писал(а):в goods - price-цена продажи на сегодня (меня не интересует вчерашняя цена)

Ну что ж, видимо, здесь я не прав, прошу прощения.
Привык, что цена всегда в отдельной таблице идет (c указанием
периода действия), и попытался навязать свои представления в качестве стандарта

S.H.S писал(а):Ваш запрос на FB2 - отрабатывает на 210 ms дольше последнего.

А вот это интересно. Можно ли план моего варианта и Вашего? - хотелось
бы разобраться - неужели сервер настолько умнее меня

Сообщение **kdv** » 12 мар 2007, 21:54

Цена товара вроде бы зависит также от времени, не только от идентификатора товара.
Можно, конечно, хранить только действующую цену - но какая же это БД,
если в ней нельзя посмотреть ситуацию на "вчера"?

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

но вместо "цена" можно написать "анец" и к этому атрибуту будут применяться те же самые правила.

Кузнецов Евгений

kdv писал(а):все не так. если мы рассматриваем цену и наименование как атрибуты товара, то все здесь замечательно вплоть до третьей НФ.

Это понятно, просто я свой частный случай - цена товара всегда с периодом действия -
обобщил до "должно быть так у всех"

To S.H.S

Что тут не то...
Вот я создал простенький тест на FB 1.5.3 -
таблицы Category, Goods и Ваse c примерно похожей структурой.
первичные и внешние ключи (никаких дополнительных индексов)
Залил тестовым генератором IBExpert в Category 100, в Goods - 1000,
в Base - 100000 записей.
В запросах IIF заменил на CASE, ROUND выбросил (особой разницы
не сыграет)
Что получилось - запрос вида

Код: Выделить всё

select b.goods_id,min(g.name),
         min(c.category),
         SUM(case when (b.beznal=0 AND b1.war_base_id IS NULL) then 1 else 0 end),
         SUM(case when (b.beznal=1 AND b1.war_base_id IS NULL) then 1 else 0 end),
         min(((g.price-g.price/100*g.discount_pr)/100)*100),
         min(g.discount_pr),
         min(g.color_sell),
         min(g.price),
         min(g.warranty),
         min(c.category ||' '|| g.name) as fullname,
         min(c.category_id)
    from base b left join base b1 
             ON b1.war_base_id=b.base_id 
         inner join goods g 
             ON b.goods_id=g.goods_id
         inner join category c
             ON c.category_id=g.category_id 
    where b.sell_date IS NULL 
      AND b.warranty_date IS NULL 
      AND b.del_date IS NULL 
      AND b.reserved_id IS NULL 
      AND g.inprice=1 
    group by b.goods_id

    union all

    select g.goods_id,g.name,
          c.category,
          CAST(0 AS INTEGER),
          CAST(0 AS INTEGER),
          ((g.price-g.price/100*g.discount_pr)/100)*100,
          g.discount_pr,
          g.color_sell,
          g.price,
          g.warranty,
          c.category ||' '|| g.name as fullname,
          c.category_id
    from goods g inner join  category c
             ON c.category_id=g.category_id 
      LEFT JOIN base b 
              ON b.goods_id=g.goods_id 
                AND b.sell_date IS NULL 
                AND b.warranty_date IS NULL 
                AND b.del_date IS NULL 
                AND b.reserved_id IS NULL 
    where g.inprice=1 and b.goods_id IS NULL
    order by 2,3

дает план

Код: Выделить всё

PLAN SORT (JOIN (JOIN (B NATURAL,B1 INDEX (FK_BASE_BASE)),JOIN (G INDEX (PK_GOODS),C INDEX (PK_CATEGORY)))) 
PLAN JOIN (JOIN (C NATURAL,G INDEX (FK_GOODS_CATEGORY)),B INDEX (FK_BASE_GOODS))

------ Performance info ------
Prepare time = 16ms
Execute time = 1s 344ms
Avg fetch time = 1 344,00 ms
Current memory = 743 484
Max memory = 938 396
Memory buffers = 75
Reads from disk to cache = 34 363
Writes from cache to disk = 0
Fetches from cache = 288 602

Для Вашего последнего запроса получаем план

Код: Выделить всё

PLAN SORT (SORT (JOIN (JOIN (JOIN (C NATURAL,G INDEX (FK_GOODS_CATEGORY)),B INDEX (FK_BASE_GOODS)),B1 INDEX (FK_BASE_BASE))))

------ Performance info ------
Prepare time = 0ms (запрос уже был подготовлен)
Execute time = 52s 390ms
Avg fetch time = 105,84 ms
Current memory = 727 772
Max memory = 938 476
Memory buffers = 75
Reads from disk to cache = 571 725
Writes from cache to disk = 0
Fetches from cache = 49 656 08

Так что еще раз прошу показать планы по обоим запросам и
статистику выполнения

S.H.S · Сообщение **S.H.S** » 13 мар 2007, 11:37

Plan

Код: Выделить всё

PLAN SORT (JOIN (JOIN (JOIN (B INDEX (FK_BASE_3), B1 INDEX (FK_BASE_4)), G INDEX (RDB$PRIMARY133)), C INDEX (RDB$PRIMARY127)))
PLAN JOIN (SORT (JOIN (C NATURAL, G INDEX (RDB$FOREIGN156))), B INDEX (RDB$FOREIGN147))

Adapted Plan

Код: Выделить всё

PLAN SORT (JOIN (JOIN (JOIN (B INDEX (FK_BASE_3), B1 INDEX (FK_BASE_4)), G INDEX (INTEG_132)), C INDEX (INTEG_126))) PLAN JOIN (SORT (JOIN (C NATURAL, G INDEX (INTEG_155))), B INDEX (INTEG_146))

S.H.S · Сообщение **S.H.S** » 13 мар 2007, 11:50

В Вашем запросе долго выполняется первая часть, из-за всязки base->base.
Когда я переходил с FB1.5 на FB2.0 90% запросов пришлось переделать. Уж очень тормозили.

S.H.S · Сообщение **S.H.S** » 13 мар 2007, 11:59

Простите нет у меня вчерашней базы, по этому время выполнения запроса будет отличать от приведенного мной вчера.
1. Это самый первый запрос.

Код: Выделить всё

SELECT goods_id, c.category_id, c.category, name, price, warranty, 
      (SELECT count(*) FROM base b 
                       LEFT JOIN base b1 ON b1.war_base_id=b.base_id 
                       WHERE g.goods_id=b.goods_id 
                         AND b.sell_date IS NULL 
                         AND b.warranty_date IS NULL 
                         AND b.del_date IS NULL 
                         AND b1.war_base_id IS NULL 
                         AND b.reserved_id IS NULL 
                         AND b.beznal='0' 
                          ), 
      (SELECT count(*) FROM base b 
                       LEFT JOIN base b1 ON b1.war_base_id=b.base_id 
                       WHERE g.goods_id=b.goods_id 
                         AND b.sell_date IS NULL 
                         AND b.warranty_date IS NULL 
                         AND b.del_date IS NULL 
                         AND b1.war_base_id IS NULL 
                         AND b.reserved_id IS NULL 
                         AND b.beznal='1' 
                          ) as beznal, 
   category ||' '|| name as fullname, (ROUND((price-price/100*discount_pr)/100))*100, 
   discount_pr, g.color_sell, '' as c 
FROM goods g, category c 
WHERE g.category_id=c.category_id AND 
      g.inprice=1 
ORDER BY c.category, name

Планы:

Код: Выделить всё

Plan
PLAN JOIN (B INDEX (RDB$FOREIGN147), B1 INDEX (FK_BASE_4))
PLAN JOIN (B INDEX (RDB$FOREIGN147), B1 INDEX (FK_BASE_4))
PLAN SORT (JOIN (C NATURAL, G INDEX (RDB$FOREIGN156)))

Adapted Plan
PLAN JOIN (B INDEX (INTEG_146), B1 INDEX (FK_BASE_4)) PLAN JOIN (B INDEX (INTEG_146), B1 INDEX (FK_BASE_4)) PLAN SORT (JOIN (C NATURAL, G INDEX (INTEG_155)))

------ Performance info ------
Prepare time = 0ms
Execute time = 30ms
Avg fetch time = 1,15 ms
Current memory = 836 592
Max memory = 844 420
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 9 694

2.Это второй запрос:

Код: Выделить всё

SELECT g.goods_id, c.category_id, c.category, g.name, g.price, g.warranty, 
       SUM(iif((b.beznal=0 AND b1.war_base_id IS NULL), 1, 0)), 
       SUM(iif((b.beznal=1 AND b1.war_base_id IS NULL), 1, 0)), 
       c.category ||' '|| g.name as fullname, 
       (ROUND((g.price-g.price/100*g.discount_pr)/100))*100, 
      g.discount_pr, g.color_sell, '' as c 
FROM goods g 
JOIN category c ON c.category_id=g.category_id 
LEFT JOIN base b ON b.goods_id=g.goods_id 
                AND b.sell_date IS NULL 
                AND b.warranty_date IS NULL 
                AND b.del_date IS NULL 
                AND b.reserved_id IS NULL 
LEFT JOIN base b1 ON b1.war_base_id=b.base_id 
WHERE g.inprice=1 
GROUP BY 1,2,3,4,5,6,9,10,11,12,13 
ORDER BY c.category, g.name

Планы:

Код: Выделить всё

Plan
PLAN SORT (SORT (JOIN (JOIN (JOIN (C NATURAL, G INDEX (RDB$FOREIGN156)), B INDEX (RDB$FOREIGN147)), B1 INDEX (FK_BASE_4))))

Adapted Plan
PLAN SORT (SORT (JOIN (JOIN (JOIN (C NATURAL, G INDEX (INTEG_155)), B INDEX (INTEG_146)), B1 INDEX (FK_BASE_4))))

------ Performance info ------
Prepare time = 0ms
Execute time = 40ms
Avg fetch time = 1,54 ms
Current memory = 837 596
Max memory = 844 420
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 6 016

3. И это планы на Ваш запрос (запрос не привожу он уже написан Вами выше.)
Планы:

Код: Выделить всё

Plan
PLAN SORT (JOIN (JOIN (JOIN (B INDEX (FK_BASE_3), B1 INDEX (FK_BASE_4)), G INDEX (RDB$PRIMARY133)), C INDEX (RDB$PRIMARY127)))
PLAN JOIN (JOIN (C NATURAL, G INDEX (RDB$FOREIGN156)), B INDEX (RDB$FOREIGN147))

Adapted Plan
PLAN SORT (JOIN (JOIN (JOIN (B INDEX (FK_BASE_3), B1 INDEX (FK_BASE_4)), G INDEX (INTEG_132)), C INDEX (INTEG_126))) PLAN JOIN (JOIN (C NATURAL, G INDEX (INTEG_155)), B INDEX (INTEG_146))

------ Performance info ------
Prepare time = 0ms
Execute time = 110ms
Avg fetch time = 4,23 ms
Current memory = 837 796
Max memory = 845 752
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 90 532

S.H.S · Сообщение **S.H.S** » 13 мар 2007, 12:05

Category - 17 записей
Goods - 507 записей
Base - 22267 записей

Кузнецов Евгений

Доброго времени суток!

To S.H.S

Спасибо за информацию.
Хотел бы уточнить:

1) Правильно ли я понимаю, что копья ломались вокруг
запроса с Execute time = 30ms?

И это называются тормоза?

2) Впрочем, у Вас SS и так все сидит уже в кэше сервера или файловом кэше
(везде Reads from disk to cache = 0), так что
цифры не совсем показательны. Ну да ладно, Вам слишком
много возни, чтобы определить число чтений с диска - сервер
надо каждый раз перезапускать.

3) По поводу планов - В Вашем втором варианте мой план и Ваш план идентичны
(с точностью до наименования индексов

).
Кстати, а что не везде используете именованные ограничения - база старая?

А вот Ваш план моего варианта вызывает некоторые вопросы:
Что за индекс FK_BASE_3 - внешний ключ по Base.reserved_id?
Возможно, если бы были хорошие индексы по одному из полей
sell_date, warranty_date, del_date, то запрос выполнялся бы быстрее.

Почему такая разница во времени выполнения (даже с учетом Buffers)
сказать не могу - видимо, особенность распределения данных.

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

P.S.
Пока копался, интересную вещь на FB 1.5.3 обнаружил - если указать
план запроса явно в секции PLAN (причем тот же, что и возвратил сервер),
то он начинает ругаться на недопустимость использования одного из индексов -
что позволено Юпитеру ...

Сообщение **kdv** » 13 мар 2007, 21:37

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

на fetches глянь. он вообще микроскопический. ни диск не грузится из-за переполнения кэша, ни процессор из-за низкого числа fetches.

Кузнецов Евгений

kdv писал(а):на fetches глянь. он вообще микроскопический.

Да, правда. Только я понять не могу, почему в моем варианте он на
порядок выше - на FB 1.5.3 соотношение обратное при практически идентичных планах (ну распределение данных в тестовом примере и у S.H.S, естественно, не совпадают, но не на порядки же)

По поводу P.S. из предыдущего поста - имеет ли смысл верещать в "Багах и падениях" или только я об этом не в курсе?

S.H.S · Сообщение **S.H.S** » 14 мар 2007, 12:30

1) Правильно ли я понимаю, что копья ломались вокруг
запроса с Execute time = 30ms? И это называются тормоза?

Я не хотел оптимизировать запрос, я хотел привести его в человеческий вид. Запрос вида SELECT (SELECT COUNT(*)...), (SELECT COUNT(*)...), ..., выполняется у меня достаточно быстро, но при добавлении каждого COUNT, скорость падает в 1,5.
Я писал:

Простите нет у меня вчерашней базы, по этому время выполнения запроса будет отличать от приведенного мной вчера.

На сервере с полной базой время выполнения в несколько раз больше.

Приведу количество обращений к таблица, может будет интересно:
1.
GOOD - 507
CATEGORY - 17 (non-indexed read)
BASE - 2713
2.
GOOD - 507
CATEGORY - 17 (non-indexed read)
BASE - 1357 (!!! и это запрос выполняется медленней 1-го)
3. (Ваш запрос)
GOOD - 756
CATEGORY - 249
CATEGORY - 17 (non-indexed read)
BASE - 23617

Кстати, а что не везде используете именованные ограничения - база старая?

Простите, не совсем понимаю (возможно по этому не везде используете), можно объяснить.

Возможно, если бы были хорошие индексы по одному из полей
sell_date, warranty_date, del_date, то запрос выполнялся бы быстрее.

Я уже пробовал, скорость в 10ms не стоит лишнего индекса, тем более по полю дата.

Кузнецов Евгений

Доброго времени суток!

S.H.S писал(а):Я не хотел оптимизировать запрос, я хотел привести его в человеческий вид.

А кто писал " Работает он крайне медленно, так как таблица base в 1000 раз больше таблицы goods. Помогите его оптимизировать."?

S.H.S писал(а):На сервере с полной базой время выполнения в несколько раз больше.

Да, хоть в 10 - это не цифры, ради которых стоит беспокоиться.

S.H.S писал(а):Простите, не совсем понимаю (возможно по этому не везде используете), можно объяснить.

Если не задать имя ограничения, то сервер именует его автоматически - INTEG_XXX
(смотрите Ваши планы). При этом имя индекса, поддерживающего данное ограничение
в FB 1.0 будет RDB$PRIMARYnnn или RDB$FOREIGNnnn. В FB 1.5 и более поздних поддерживающий
индекс будет иметь то же самое имя, что и ограничение.
Например, у Вас FK_BASE_4 - ограничение внешнего ключа по полю Base.war_base_id,
и имя индекса - то же самое (в PLAN сервер указывает имена индексов, в Adapted Plan -
соответствующие имена ограничений).

Читайте Х. Борри!

Почему рекомендуется задавать имена ограничений явно
1) Легче читается план - сравните Ваши планы и мои в посте от Пн Мар 12, 2007 11:06 pm
2) При задании ручного плана требуется указать именно имена индексов - а последовательность
создания, и следовательно, имя индекса в случае RDB$PRIMARYnnn, вроде бы может
меняться при Backup\Restore.

О статистике чтений
Мой запрос

Код: Выделить всё

Enchanced Info:
+--------------------------+-----------+-----------+-------------+
|        Table Name        |  Records  |  Indexed  | Non-Indexed |
|                          |   Total   |   reads   |    reads    |
+--------------------------+-----------+-----------+-------------+
|                      BASE|         0 |     40177 |      100000 |
|                  CATEGORY|         0 |       386 |         100 |
|                     GOODS|         0 |      2000 |           0 |
+--------------------------+-----------+-----------+-------------+

FK_BASE3 у меня нет - поэтому такое количество неиндексированных чтений

Ваш запрос

Код: Выделить всё

Enchanced Info:
+--------------------------+-----------+-----------+-------------+
|        Table Name        |  Records  |  Indexed  | Non-Indexed |
|                          |   Total   |   reads   |    reads    |
+--------------------------+-----------+-----------+-------------+
|                      BASE|         0 |  22789722 |           0 |
|                  CATEGORY|         0 |         0 |         100 |
|                     GOODS|         0 |      1000 |           0 |
+--------------------------+-----------+-----------+-------------+

В общем, поведение FB 1.5.3 CS и FB 2.0 SS сильно различается
Буду иметь в виду.

S.H.S · Сообщение **S.H.S** » 14 мар 2007, 14:06

Спасибо за разъяснение.

Кузнецов Евгений

To S.H.S.

Да, и при переносе с IB 6/FB 1.0 имена ограничений не изменяются.
Видимо, и имена индексов остаются в старом формате.

Сообщение **kdv** » 14 мар 2007, 17:27

Видимо, и имена индексов остаются в старом формате.

разумеется. имя индекса поменяется только если пересоздать constraint.