Какой запрос лучше использовать?

Лысый · Сообщение **Лысый** » 13 янв 2005, 12:36

Привет всем!

FB 1.5.2

Имеем два варианта процедуры, которая возвращает одно и тоже (почти).
IDX_HISTORY_IUD_DATE_MDF - индекс по HISTORY_IUD.DATE_MODIFY

1 вариант:

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

FOR
SELECT MAX(U.FIO)
 , COUNT(CASE WHEN (H.TABLE_NAME = 'PERSON' AND H.OPERATION = 1) THEN H.ID_USER ELSE NULL END)
 , COUNT(CASE WHEN (H.TABLE_NAME = 'PERSON' AND H.OPERATION = 2) THEN H.ID_USER ELSE NULL END)
 , COUNT(CASE WHEN (H.TABLE_NAME = 'PERSON' AND H.OPERATION = 3) THEN H.ID_USER ELSE NULL END)
 , COUNT(CASE WHEN (H.TABLE_NAME = 'ANSWER' AND H.OPERATION = 1) THEN H.ID_USER ELSE NULL END)
 , COUNT(CASE WHEN (H.TABLE_NAME = 'ANSWER' AND H.OPERATION = 2) THEN H.ID_USER ELSE NULL END)
 , COUNT(CASE WHEN (H.TABLE_NAME = 'ANSWER' AND H.OPERATION = 3) THEN H.ID_USER ELSE NULL END)
  FROM USERS U LEFT JOIN HISTORY_IUD H ON U.ID_USER = H.ID_USER
 WHERE U.ID_USER NOT IN (1, 5, 6)
   AND H.DATE_MODIFY BETWEEN :DATBEG AND :DATEND
 GROUP BY U.ID_USER
 ORDER BY 1
  INTO :FIO
     , :CNT_PERSON_I
     , :CNT_PERSON_U
     , :CNT_PERSON_D
     , :CNT_ANSWER_I
     , :CNT_ANSWER_U
     , :CNT_ANSWER_D
DO BEGIN
  SUSPEND;
END

------ Performance info ------
Prepare time = 31ms
Execute time = 94ms
Avg fetch time = 6,27 ms
Current memory = 3 146 276
Max memory = 3 493 148
Memory buffers = 2 048
Reads from disk to cache = 64
Writes from cache to disk = 3
Fetches from cache = 11 028

PLAN SORT (SORT (JOIN (U NATURAL,H INDEX (FK_USERS__OLD_PERSON,IDX_HISTORY_IUD_DATE_MDF))))

2 вариант:

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

FOR
  SELECT U.ID_USER
       , U.FIO
    FROM USERS U
   WHERE U.ID_USER NOT IN (1, 5, 6)
   ORDER BY 2
    INTO :ID_USER
       , :FIO
DO BEGIN

  SELECT COUNT(CASE WHEN (H.TABLE_NAME = 'PERSON' AND H.OPERATION = 1) THEN H.ID_USER ELSE NULL END)
       , COUNT(CASE WHEN (H.TABLE_NAME = 'PERSON' AND H.OPERATION = 2) THEN H.ID_USER ELSE NULL END)
       , COUNT(CASE WHEN (H.TABLE_NAME = 'PERSON' AND H.OPERATION = 3) THEN H.ID_USER ELSE NULL END)
       , COUNT(CASE WHEN (H.TABLE_NAME = 'ANSWER' AND H.OPERATION = 1) THEN H.ID_USER ELSE NULL END)
       , COUNT(CASE WHEN (H.TABLE_NAME = 'ANSWER' AND H.OPERATION = 2) THEN H.ID_USER ELSE NULL END)
       , COUNT(CASE WHEN (H.TABLE_NAME = 'ANSWER' AND H.OPERATION = 3) THEN H.ID_USER ELSE NULL END)
    FROM HISTORY_IUD H
   WHERE H.ID_USER = :ID_USER
     AND H.DATE_MODIFY BETWEEN :DATBEG AND :DATEND
    INTO :CNT_PERSON_I
       , :CNT_PERSON_U
       , :CNT_PERSON_D
       , :CNT_ANSWER_I
       , :CNT_ANSWER_U
       , :CNT_ANSWER_D;

  SUSPEND;
END

------ Performance info ------
Prepare time = 15ms
Execute time = 47ms
Avg fetch time = 1,62 ms
Current memory = 2 923 556
Max memory = 3 493 148
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 3
Fetches from cache = 14 699

PLAN (H INDEX (FK_USERS__OLD_PERSON,IDX_HISTORY_IUD_DATE_MDF))SORT ((U NATURAL))

В обоих вариантах 32 не индексных чтения по USERS и 5000 индексных чтений по ANSWER. Другими словами выполняя одно и тоже второй вариант оказывается эффективнее.

1. Это происходит из-за группировки?

Ожидается что ANSWER будет рости очень быстро.

2. Что будет эффективнее при большом количестве записей?

McArty · Сообщение **McArty** » 14 янв 2005, 09:41

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

Создавать дополнительные View и т.п.

McArty · Сообщение **McArty** » 14 янв 2005, 10:51

Да,еще. [not in] лучше заменить на [not ((=) or (=) or (=))]
И структуру таблиц,тоже было бы не плохо знать, для ответов.

Лысый · Сообщение **Лысый** » 14 янв 2005, 11:23

Как я и сказал рост ANSWER идет очень быстро. Уже сейчас стало видно, что первый вариант значительно отстает. Жаль. Можно было обойтись без процедуры.

McArty · Сообщение **McArty** » 14 янв 2005, 11:40

Лысый писал(а):Как я и сказал рост ANSWER идет очень быстро. Уже сейчас стало видно, что первый вариант значительно отстает. Жаль. Можно было обойтись без процедуры.

Вопрос - зачем в первом варианте MAX(U.FIO) ???

Сообщение **kdv** » 14 янв 2005, 12:17

а это он так fio получает вместо id. трюк такой.

Лысый · Сообщение **Лысый** » 14 янв 2005, 12:29

дабы не делать лишнюю группировку

sag · Сообщение **sag** » 14 янв 2005, 12:31

с группировкой завместо max-а получилось медленнее?

Лысый · Сообщение **Лысый** » 14 янв 2005, 12:42

sag писал(а):с группировкой завместо max-а получилось медленнее?

Да. Приблизительно на треть медленнее.

McArty · Сообщение **McArty** » 14 янв 2005, 12:54

Лысый писал(а):
sag писал(а):с группировкой завместо max-а получилось медленнее?
Да. Приблизительно на треть медленнее.

А если в первом запросе получить id и потом inner join результат запроса с User пo ID_USER

sag · Сообщение **sag** » 14 янв 2005, 13:09

Если смотреть на два варианта в первом письме, то, чисто по моим собственным эстетическим соображениям, вариант без внутреннего цикла (№1) мне нравится больше.
Попробуй в первом варианте извлекать FIO по другому:
SELECT (select u2.FIO from USERS U2 where U2.ID_USER=U.ID_USER) FIO,
...
FROM USERS U
LEFT JOIN HISTORY_IUD H ON U.ID_USER = H.ID_USER
WHERE U.ID_USER NOT IN (1, 5, 6)
AND H.DATE_MODIFY BETWEEN

ATBEG AND

ATEND
GROUP BY U.ID_USER
ORDER BY 1

McArty · Сообщение **McArty** » 14 янв 2005, 13:13

А если создать View
SELECT ID_USER,
COUNT(CASE WHEN (H.TABLE_NAME = 'PERSON' AND H.OPERATION = 1) THEN H.ID_USER ELSE NULL END)
, COUNT(CASE WHEN (H.TABLE_NAME = 'PERSON' AND H.OPERATION = 2) THEN H.ID_USER ELSE NULL END)
, COUNT(CASE WHEN (H.TABLE_NAME = 'PERSON' AND H.OPERATION = 3) THEN H.ID_USER ELSE NULL END)
, COUNT(CASE WHEN (H.TABLE_NAME = 'ANSWER' AND H.OPERATION = 1) THEN H.ID_USER ELSE NULL END)
, COUNT(CASE WHEN (H.TABLE_NAME = 'ANSWER' AND H.OPERATION = 2) THEN H.ID_USER ELSE NULL END)
, COUNT(CASE WHEN (H.TABLE_NAME = 'ANSWER' AND H.OPERATION = 3) THEN H.ID_USER ELSE NULL END)
FROM HISTORY_IUD
where ID_USER not in (1,5,6)
group by IDUSER

а затем соединить результаты с USERS

Лысый · Сообщение **Лысый** » 14 янв 2005, 16:26

>saq
Попробовал.
1-ый вариант выполняется 140 ms, после модификации 78 ms.
2-й вариант 46 ms.

>McArty
Вариант с вьюхой не покатит, тормозов не оберешься.

McArty · Сообщение **McArty** » 14 янв 2005, 16:46

Лысый писал(а):>saq
Попробовал.
1-ый вариант выполняется 140 ms, после модификации 78 ms.
2-й вариант 46 ms.

>McArty
Вариант с вьюхой не покатит, тормозов не оберешься.

А у меня c View быстрее запрос (аля твой первый вариант) заработал раза 4.
Странное дело

Лысый · Сообщение **Лысый** » 14 янв 2005, 17:01

McArty писал(а):А у меня c View быстрее запрос (аля твой первый вариант) заработал раза 4.
Странное дело

Данных поди мало залил?

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

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

H.DATE_MODIFY BETWEEN :DATBEG AND :DATEND

Соответственно чем больше данных будет в History, тем

тормозов не оберешься

Да, в коде что ты привел даты не хватает и группировки по ней.

sag · Сообщение **sag** » 17 янв 2005, 12:47

фух. Праздники как бы уже совсем закончились

, с этим всех и поздравляю ! Как бы не начать отмечать окончание праздников.

> Имеем два варианта процедуры, которая возвращает
> одно и тоже (почти).

До меня, наконец, дошло почему "почти". То есть LEFT JOIN тебе нафик не нужен, получается. А коли так, то и не джойнь HISTORY_IUD с USERS совсем.

SELECT (select u.FIO from USERS U where U.ID_USER=H.ID_USER) FIO,
... /*тут твои каунтокейсовые манипуляции */...
FROM HISTORY_IUD H
WHERE H.ID_USER NOT IN (1, 5, 6)
AND H.DATE_MODIFY BETWEEN

ATBEG AND

ATEND
GROUP BY H.ID_USER
ORDER BY 1

> Попробовал.
> 1-ый вариант выполняется 140 ms, после модификации 78 ms.
> 2-й вариант 46 ms.

Тенденция понятна, но абсолютная разница уж очень минимальная (+-десяток миллисекунд). Или эти мс очень критичны? Данных в таблицы налил достаточно?

Лысый · Сообщение **Лысый** » 17 янв 2005, 14:56

sag писал(а):До меня, наконец, дошло почему "почти". То есть LEFT JOIN тебе нафик не нужен, получается. А коли так, то и не джойнь HISTORY_IUD с USERS совсем.
Код: Выделить всё
SELECT (select u.FIO from USERS U where U.ID_USER=H.ID_USER) FIO, 
... /*тут твои каунтокейсовые манипуляции */...
FROM HISTORY_IUD H
WHERE H.ID_USER  NOT IN (1, 5, 6) 
AND H.DATE_MODIFY BETWEEN :DATBEG AND :DATEND 
GROUP BY H.ID_USER
ORDER BY 1
> Попробовал.
> 1-ый вариант выполняется 140 ms, после модификации 78 ms.
> 2-й вариант 46 ms.

Результат вышеприведенного запроса на тех же данных - 63 ms.
Кстати для меня было новостью такое использование запроса. Не знал что запрос select (select ...) воспринимается как агригирующая функция и не надо делать по нему группировку.

Тенденция понятна, но абсолютная разница уж очень минимальная (+-десяток миллисекунд). Или эти мс очень критичны? Данных в таблицы налил достаточно?

В общем то критичности нет, просто мне не хочется писать на каждый чих ХП и в тоже время заведомо слабый вариант тоже использовать не хочется. Вот я и сижу пробую, благо время позволяет

Сообщение **kdv** » 17 янв 2005, 15:13

он не как агрегирующая функция воспринимается. Как раз по агрегатам обычно и надо делать группировку. А это просто выборка связанного значения из другой таблицы "по месту". почти эквивалент left join.

Лысый · Сообщение **Лысый** » 17 янв 2005, 15:18

kdv писал(а):он не как агрегирующая функция воспринимается. Как раз по агрегатам обычно и надо делать группировку. А это просто выборка связанного значения из другой таблицы "по месту". почти эквивалент left join.

На счет почти left join - это понятно, я просто такие запросы в совокупности с агрегатами не использовал.

sag · Сообщение **sag** » 17 янв 2005, 15:19

> Результат вышеприведенного запроса на тех же данных - 63 ms.

Хм. Странно.

> В общем то критичности нет, просто мне не хочется писать
> на каждый чих ХП и в тоже время заведомо слабый вариант
> тоже использовать не хочется.

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

.

> Кстати для меня было новостью такое использование запроса.
> Не знал что запрос select (select ...) воспринимается как
> агригирующая функция и не надо делать по нему группировку

не совсем так. Здесь в основном ”верхнем” запросе идет группировка по полю, по которому привязывается ”вложенный” запрос “(select…”. См. readme.aggregate_tracing к полуторке.

iBase.ru Forum

Какой запрос лучше использовать?

Какой запрос лучше использовать?

Re: Какой запрос лучше использовать?

Re: Какой запрос лучше использовать?