Прошу наставить меня на путь истинный

Запросы, планы, оптимизация запросов, ...

Модераторы: kdv, CyberMax

Ответить
stix-s
Заслуженный разработчик
Сообщения: 557
Зарегистрирован: 13 дек 2005, 11:52

Прошу наставить меня на путь истинный

Сообщение stix-s » 19 июл 2006, 14:02

Прошу наставить меня на путь истинный, как правильно составить запрос :oops:
Дано:
FB 1.5.3
Таблица:

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

CREATE TABLE TB1 (
    RID  INTEGER NOT NULL PRIMARY KEY ,
    UID  INTEGER,
    F1   VARCHAR(10),
    F2   VARCHAR(10),
    V1   INTEGER,
    V2   INTEGER
);
Заполняется для теста

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

CREATE PROCEDURE CREATE_TEST 
AS
DECLARE VARIABLE PID INTEGER;
DECLARE VARIABLE PUID INTEGER;
DECLARE VARIABLE PV1 INTEGER;
DECLARE VARIABLE PV2 INTEGER;
begin
  /* Procedure Text */
  pid=0;
  puid=0;
  pv1=0;
  pv2=0;
  while (:pid<=10000) do
  begin
  pid=gen_id(gen_rec_id,1);
   if (puid=0)
   then
     begin
      insert
       into tb1 (rid,uid,f1,f2,v1,v2)
       values (:pid, :puid,'a'||cast(:pv1 as varchar(9)),'fff',:pv1+1,:pv2+1);
      puid=1;
     end
   else
    begin
      insert
       into tb1 (rid,uid,f1,f2,v1,v2)
       values (:pid, :puid,'ccc','e'||cast(:pv2 as varchar(9)),:pv1+1,:pv2+1);
      puid=0;
     end
   pv1=pv1+1;
   pv2=pv2+1;
   end
  exit;
end
10000 записей, индексов, кроме как по первичному ключу нет
Необходимо выбрать записи с уникальными значениями по UID, где значения V1 и V2 имеют максимальные значения
Используется запрос

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

select t1.rid, t1.uid, t1.f1,t1.f2,t1.v1,t1.v2
from tb1 t1
where (select t2.uid
 from tb1 t2
 group by uid
 having t1.v1=max(t2.v1) and t1.v2=max(t2.v2) and t1.uid=t2.uid
 ) is not null
План
PLAN SORT ((T2 NATURAL))
PLAN (T1 NATURAL)

Адаптированный план
PLAN SORT ((T2 NATURAL)) PLAN (T1 NATURAL)

------ Performance info ------
Prepare time = 0ms
Execute time = 6m 40s 390ms
Avg fetch time = 66 731,67 ms
Current memory = 2 989 108
Max memory = 3 124 004
Memory buffers = 10 240
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 201 950 497
Также пробовал использовать процедуру

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

CREATE PROCEDURE PR_TB1 
RETURNS (
    PRID INTEGER,
    PUID INTEGER,
    PF1 VARCHAR(10),
    PF2 VARCHAR(10),
    PV1 INTEGER,
    PV2 INTEGER)
AS
DECLARE VARIABLE VUID INTEGER;
DECLARE VARIABLE VV1 INTEGER;
DECLARE VARIABLE VV2 INTEGER;
begin
  /* Procedure Text */
  for select t1.uid, max(t1.v1), max(t1.v2) from tb1 t1
   group by t1.uid  into :vuid,:vv1,:vv2
     do
     begin
      select t2.rid, t2.uid,t2.f1,t2.f2,t2.v1,t2.v2 from tb1 t2
       where t2.uid=:vuid and t2.v1=:vv1 and t2.v2=:vv2
       into :prid,:puid,:pf1,:pf2,:pv1,:pv2;
        suspend;
     end
end
------ Performance info ------
Prepare time = 47ms
Execute time = 125ms
Avg fetch time = 20,83 ms
Current memory = 2 998 628
Max memory = 3 143 032
Memory buffers = 10 240
Reads from disk to cache = 0
Writes from cache to disk = 3
Fetches from cache = 141 345

Dimitry Sibiryakov
Заслуженный разработчик
Сообщения: 1436
Зарегистрирован: 15 сен 2005, 09:05

Сообщение Dimitry Sibiryakov » 19 июл 2006, 15:23

Ну и чем процедура не устраивает? Время выполнения считанные миллисекунды.

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Сообщение kdv » 19 июл 2006, 16:10

а F3, F4... и V3, V4, V5 будут? :-)

stix-s
Заслуженный разработчик
Сообщения: 557
Зарегистрирован: 13 дек 2005, 11:52

Сообщение stix-s » 20 июл 2006, 05:48

kdv писал(а):а F3, F4... и V3, V4, V5 будут? :-)
вполне возможно, поскольку это таблица тестовая, ожидается передача подобной таблицы в будущем для всякого рода анализа (биллинговая система грубо говоря:UID - код клиента, F1,F2 - ФИО, V1, V2 - версии записей, поскольку в расчетном периоде клиент может поменять своё ФИО) и нет никакого желания ждать выполнения запроса, поскольку в тестовую таблицу я запихал 10000 записей, а в реальной их может быть от 100000 и более :(
и хочется все же разобраться в подобной разнице скорости выполнения запроса и процедуры
Сразу скажу, что структура исходной таблицы разработана не мной и менять я ее не смогу.

Ivan_Pisarevsky
Заслуженный разработчик
Сообщения: 644
Зарегистрирован: 15 фев 2005, 11:34

Сообщение Ivan_Pisarevsky » 20 июл 2006, 08:22

... биллинговая система грубо говоря:UID - код клиента, F1,F2 - ФИО, V1, V2 - версии записей, поскольку в расчетном периоде клиент может поменять своё ФИО ...

Декомпозицию никак? Проектирование не то что хромает, оно скорее с перломаными ногами пытается двигаться :shock:

stix-s
Заслуженный разработчик
Сообщения: 557
Зарегистрирован: 13 дек 2005, 11:52

Сообщение stix-s » 20 июл 2006, 09:14

Ivan_Pisarevsky писал(а):... биллинговая система грубо говоря:UID - код клиента, F1,F2 - ФИО, V1, V2 - версии записей, поскольку в расчетном периоде клиент может поменять своё ФИО ...

Декомпозицию никак? Проектирование не то что хромает, оно скорее с перломаными ногами пытается двигаться :shock:
Я же говорю - таблица не моя, я могу по ней только запросы запускать.
кстати, приблизив тестовую таблицу к реалиям жилни, то есть сделав примерно 5 изменений на каждого клиента в расчетном периоде

получаем при запросе

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

План
PLAN SORT ((T2 NATURAL))
PLAN (T1 NATURAL)

Адаптированный план
PLAN SORT ((T2 NATURAL)) PLAN (T1 NATURAL)

------ Performance info ------
Prepare time = 0ms
Execute time = 23m 19s 390ms
Avg fetch time = 69 969,50 ms
Current memory = 2 701 880
Max memory = 2 836 776
Memory buffers = 10 240
Reads from disk to cache = 97
Writes from cache to disk = 6
Fetches from cache = 193 103 814
при процедуре

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

------ Performance info ------
Prepare time = 156ms
Execute time = 1s 406ms
Avg fetch time = 54,08 ms
Current memory = 2 767 500
Max memory = 2 855 804
Memory buffers = 10 240
Reads from disk to cache = 1
Writes from cache to disk = 3
Fetches from cache = 31 524 523
С грустью думаю, что же будет при 100000 записей :(

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Сообщение kdv » 20 июл 2006, 09:55

"таблица не моя, но я буду продолжать кривое проектирование дальше..." - так, что ли?
Ты чего хочешь-то - чтобы тебе помогли в ускорении кривого решения? Ответ уже был - перепроектировать.

stix-s
Заслуженный разработчик
Сообщения: 557
Зарегистрирован: 13 дек 2005, 11:52

Сообщение stix-s » 20 июл 2006, 10:03

kdv писал(а):"таблица не моя, но я буду продолжать кривое проектирование дальше..." - так, что ли?
Ты чего хочешь-то - чтобы тебе помогли в ускорении кривого решения? Ответ уже был - перепроектировать.
уточню :) мало того, что таблица не моя, БД тоже не моя :) и физического доступа к компу с ней я к ней не имею
проектировать данную таблицу и БД я не буду :)
меня лишь интересует вопрос, как мне жизть облегчить и заставить запрос работать быстрее, поскольку могут понадобиться и другие запросы или же плюнуть на все и делать на процедурах?

eugeney
Сообщения: 79
Зарегистрирован: 29 окт 2004, 18:51

Сообщение eugeney » 20 июл 2006, 10:25

stix-s писал(а):уточню :) мало того, что таблица не моя, БД тоже не моя :) и физического доступа к компу с ней я к ней не имею
проектировать данную таблицу и БД я не буду :)
меня лишь интересует вопрос, как мне жизть облегчить и заставить запрос работать быстрее, поскольку могут понадобиться и другие запросы или же плюнуть на все и делать на процедурах?
Если сможеш поставить 2-ку то тогда пожно через select from select

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

select t1.rid, t1.uid, t1.f1,t1.f2,t1.v1,t1.v2
from tb1 t1 join (
t2.uid,max(t2.v1) as v1 ,t1.max(t2.v2) as v2
 from tb1 t2) as j2 on j2.iud=t1.iud and t1.v1=j2.v1 and t1.v2=j2.v2
Но в твоем варианте лучше использовать процедуры, или вообще выкачивай БД локально и там выполняй запросы.

stix-s
Заслуженный разработчик
Сообщения: 557
Зарегистрирован: 13 дек 2005, 11:52

Сообщение stix-s » 20 июл 2006, 12:14

eugeney писал(а):Если сможеш поставить 2-ку то тогда пожно через select from select

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

select t1.rid, t1.uid, t1.f1,t1.f2,t1.v1,t1.v2
from tb1 t1 join (
t2.uid,max(t2.v1) as v1 ,t1.max(t2.v2) as v2
 from tb1 t2) as j2 on j2.iud=t1.iud and t1.v1=j2.v1 and t1.v2=j2.v2
Надо будет попробовать, главное чтобы выигрыш во времени побольше получить.
eugeney писал(а):Но в твоем варианте лучше использовать процедуры, или вообще выкачивай БД локально и там выполняй запросы.
Видимо так и придется делать, если конечно удастся производить закачку в приемлемые сроки.
В данном случае интересовал вопрос, правильно ли я наваял запрос исходя из первоначальных условий. Насколько понял - вроде верно, все дело в самой структуре таблицы.
ЗЫ Я полагаю ее получают сджойниванием нескольких таблиц, отсюда и несколько полей с версиями, каждое от своей таблицы :)

Ivan_Pisarevsky
Заслуженный разработчик
Сообщения: 644
Зарегистрирован: 15 фев 2005, 11:34

Сообщение Ivan_Pisarevsky » 20 июл 2006, 12:46

А индексы добавить, план запросов процедуры поглядеть?
Или индексы тоже нельзя добавлять?

stix-s
Заслуженный разработчик
Сообщения: 557
Зарегистрирован: 13 дек 2005, 11:52

Сообщение stix-s » 20 июл 2006, 13:28

Ivan_Pisarevsky писал(а):А индексы добавить, план запросов процедуры поглядеть?
Или индексы тоже нельзя добавлять?
Вот если удастся мне все это к себе закачать - тогда, шо хочу, то и ворочу :)
А если придется к чужой базе запрос делать, то вряд ли они по моему хотению будут индексы добавлять - убирать.
добавил индекс ради интереса

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

CREATE INDEX TB1_IDX1 ON TB1 (UID);
на процедуре:

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

PLAN (T2 INDEX (TB1_IDX1))(T1 ORDER TB1_IDX1)
------ Performance info ------
Prepare time = 0ms
Execute time = 16ms
Avg fetch time = 0,62 ms
Current memory = 2 975 068
Max memory = 3 119 388
Memory buffers = 10 240
Reads from disk to cache = 0
Writes from cache to disk = 3
Fetches from cache = 43 708
на запросе

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

План
PLAN (T2 ORDER TB1_IDX1)
PLAN (T1 NATURAL)

Адаптированный план
PLAN (T2 ORDER TB1_IDX1) PLAN (T1 NATURAL)

------ Performance info ------
Prepare time = 0ms
Execute time = 3m 36s 484ms
Avg fetch time = 10 824,20 ms
Current memory = 2 997 096
Max memory = 3 131 992
Memory buffers = 10 240
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 286 973 712

dimitr
Разработчик Firebird
Сообщения: 888
Зарегистрирован: 26 окт 2004, 16:20

Сообщение dimitr » 20 июл 2006, 14:01

перенеси
t1.uid=t2.uid
из HAVING-а в WHERE. С индексом по UID это должно дать результат.

stix-s
Заслуженный разработчик
Сообщения: 557
Зарегистрирован: 13 дек 2005, 11:52

Сообщение stix-s » 21 июл 2006, 06:02

dimitr писал(а):перенеси
t1.uid=t2.uid
из HAVING-а в WHERE. С индексом по UID это должно дать результат.
Замечательная подсказка! группировать не всю таблицу, а лишь нужную часть. Спасибо :D

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

select t1.rid, t1.uid, t1.f1,t1.f2,t1.v1,t1.v2
from tb1 t1
where (select t2.uid
 from tb1 t2
 where t1.uid=t2.uid
 group by uid
 having t1.v1=max(t2.v1) and t1.v2=max(t2.v2)
 ) is not null

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

План
PLAN (T2 ORDER TB1_IDX1)
PLAN (T1 NATURAL)

Адаптированный план
PLAN (T2 ORDER TB1_IDX1) PLAN (T1 NATURAL)

------ Performance info ------
Prepare time = 0ms
Execute time = 328ms
Avg fetch time = 16,40 ms
Current memory = 2 711 820
Max memory = 2 852 656
Memory buffers = 10 240
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 191 574
Разница весьма ощутима :)

и напоследок:
создал из нездорового интереса такую же таблицу на MS SQL 2000, залил ее аналогичными данными и увидел антыресную вещь:
запрос

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

 select t1.rid, t1.uid, t1.f1,t1.f2,t1.v1,t1.v2
from tb1 t1
where (select t2.uid
 from tb1 t2
 where t1.uid=t2.uid
 group by uid
 having t1.v1=max(t2.v1) and t1.v2=max(t2.v2)
 ) is not null
выполняется порядка 5 сек
а запрос

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

 select t1.rid, t1.uid, t1.f1,t1.f2,t1.v1,t1.v2
from tb1 t1
where (select t2.uid
 from tb1 t2
 group by uid
 having t1.v1=max(t2.v1) and t1.v2=max(t2.v2) and t1.uid=t2.uid
 ) is not null
порядка 100 миллисекунд :)
т.е. с точностью до наоборот по сравнению с FB.
примечание - количество записей в таблице при этом было увеличено до 100000, 20000 уникальных UID - итого по 5 записей на 1 UID

Ответить