for flooders
:: Главная :: Решения :: Статьи :: Сайт М. Дроздова :: Файловый архив :: Книга по VFP 9 :: Русский Help Online :: OFF-LINE Форум
   Л и с о в о д ы   в с е х   с т р а н,  о б ъ е д и н я й т е с ь !!!  

Список Форумов  :: Игры Разума
   :: Помощь сайту :: 

Запрос - спортивный интерес
Extortioner
Автор

Сообщений: 854
Откуда: Новосибирск
Дата: 03.10.11 11:59:14ОтветитьЦитировать
Добрый день, хочу поделиться с вами задачей, которую уже решил, но хотел бы оптимизировать просто ради спортивного интереса:
Итак, есть табличка tbl_num_np_order (id_order, base_num) - ид. заказа и сквозной номер издания, в котором этот заказ выходит.
И есть табличка tbl_num_np_issue (base_num, num_newspaper) сквозной номер издания и годовой номер издания
Сквозной номер это номер, который идёт начиная с первого выхода газеты, годовой номер это номер в пределах одного года, то есть к примеру может быть такое, что сквозной номер 1000, а годовой 1.

Необходимо извлечь следующие данные:
идентификатор заказа, его ГОДОВОЙ номер, и ГОДОВОЙ номер, соответствующий максимальному сквозному номеру, в котором этот заказ выходит

то есть к примеру заказ выходит в сквозных номерах 999(годовой 999), 1000 (годовой 1), 1001 (годовой 2)
нужно показать следующую выборку

1 999 2
1 1 2
1 2 2

Для тренировки предлагаю следующие данные:
  
  CREATE CURSOR tbl_num_np_order (id_order INT, base_num INT)  
  CREATE CURSOR tbl_num_np_issue (base_num INT, num_newspaper INT)  
    
  INSERT INTO tbl_num_np_order VALUES (1, 996)  
  INSERT INTO tbl_num_np_order VALUES (1, 997)  
  INSERT INTO tbl_num_np_order VALUES (1, 998)  
  INSERT INTO tbl_num_np_order VALUES (1, 999)  
  INSERT INTO tbl_num_np_order VALUES (2, 996)  
  INSERT INTO tbl_num_np_order VALUES (2, 997)  
  INSERT INTO tbl_num_np_order VALUES (2, 998)  
  INSERT INTO tbl_num_np_order VALUES (2, 999)  
  INSERT INTO tbl_num_np_order VALUES (2, 1000)  
    
    
  INSERT INTO tbl_num_np_issue VALUES ( 996, 49)  
  INSERT INTO tbl_num_np_issue VALUES ( 997, 50)  
  INSERT INTO tbl_num_np_issue VALUES ( 998, 1)  
  INSERT INTO tbl_num_np_issue VALUES ( 999, 2)  
  INSERT INTO tbl_num_np_issue VALUES (1000, 3)

Ну и мой вариант решения:
  
  SELECT t3.id_order, tbni.num_newspaper, t3.max_n FROM ;  
    (SELECT t1.id_order, tbno.base_num, tbni.num_newspaper as max_n ;  
    FROM ;  
    	(SELECT id_order, MAX(base_num) max_n ;  
    	 FROM tbl_num_np_order ;  
    	 GROUP BY id_order) t1 ;   
    	inner JOIN tbl_num_np_issue tbni on t1.max_n = tbni.base_num ;  
    	inner join tbl_num_np_order tbno on t1.id_order = tbno.id_order) t3 ;  
    inner join tbl_num_np_issue tbni on t3.base_num = tbni.base_num

Мой запрос на большой базе отрабатывает за 8,7 сек., коллега проникся задачей и сумел нарисовать запрос, который на моей же базе отрабатывает за 7,8 сек.

PS - слегка исправил свой запрос, теперь его можно запускать на фоксе.



Исправлено: Extortioner, 03.10.11 12:27
Ratings: 0 negative/0 positive

Re: Запрос - спортивный интерес
Igor Korolyov

Сообщений: 32507
Дата: 03.10.11 18:57:34ОтветитьЦитировать
Твой же по сути запрос записывается несколько проще
SELECT tbno.id_order, ;  
    tbni.num_newspaper, ;  
    tbni2.num_newspaper ;  
   FROM tbl_num_np_order tbno ;  
    INNER JOIN tbl_num_np_issue tbni ;  
     ON tbni.base_num = tbno.base_num ;  
   INNER JOIN (SELECT id_order, ;  
                 MAX(base_num) max_n ;  
                FROM tbl_num_np_order ;  
                GROUP BY id_order) t1 ;  
    ON t1.id_order = tbno.id_order ;  
   INNER JOIN tbl_num_np_issue tbni2 ;  
    ON tbni2.base_num = t1.max_n
Для сравнения разных вариантов оптимизации нужно знать параметры рабочих таблиц - число записей, размер полей, среднее количество записей с одним и тем-же id_order в первой таблице... Вышеприведенный запрос вполне понятен и в принципе я бы сказал что он достаточно оптимален для фокса (он будет использовать индексы для соединения таблиц, если таковые индексы будут созданы). Для другой СУБД вполне возможно есть и лучшие варианты - учитывая что твоё хитро вычисляемое поле по сути является результатом работы аналитической функции "последнее значение num_newspaper в группе по id_order, упорядоченной по base_num" над тривиальным запросом сцепляющим эти 2 таблицы. В СУБД имеющей аналитические функции запрос бы выглядел например так (синтаксис Oracle)
SELECT tbno.id_order,  
           tbni.num_newspaper,  
           MAX (tbni.num_newspaper)  
              KEEP (DENSE_RANK LAST ORDER BY tbno.base_num)  
              OVER (PARTITION BY tbno.id_order)  
              max_num_newspaper  
      FROM    tbl_num_np_order tbno  
           INNER JOIN  
              tbl_num_np_issue tbni  
           ON tbni.base_num = tbno.base_num
И выполнялся бы, естественно, быстрее чем первый вариант.


------------------
WBR, Igor
Ratings: 0 negative/0 positive

Re: Запрос - спортивный интерес
Extortioner
Автор

Сообщений: 854
Откуда: Новосибирск
Дата: 04.10.11 05:09:48ОтветитьЦитировать
tbl_num_np_order ~ 223 000 записей
Далее идут описания в терминах FireBird
id_order - integer (32 бита)
base_num - smallint (16 бит)
num_newspaper - smallint (16 бит)
среднее количество выходов одного заказа - 2 (там есть, конечно разница между видами заказов, например, если брать табличные объявления, то они в принципе выходят только один раз, ну а обычные объявления или модульная реклама могут выходить очень много раз максимум, который сейчас есть в таблице - 115 раз)

Ваш запрос отработал за 9,4 сек.
Ratings: 0 negative/0 positive

Re: Запрос - спортивный интерес
Extortioner
Автор

Сообщений: 854
Откуда: Новосибирск
Дата: 04.10.11 05:26:40ОтветитьЦитировать
select tbno.id_order, tbni1.num_newspaper,  tbni2.num_newspaper  
  from tbl_num_np_order tbno  
      inner join (select id_order, max(base_num) max_n from tbl_num_np_order group by id_order) t1 on tbno.id_order = t1.id_order  
      inner join tbl_num_np_issue tbni1 on tbno.base_num = tbni1.base_num  
      inner join tbl_num_np_issue tbni2 on t1.max_n = tbni2.base_num

На моей же базе отрабатывает за 6,532 сек. Мне кажется это предел.
Ratings: 0 negative/0 positive

Re: Запрос - спортивный интерес
medstrax

Сообщений: 4474
Дата: 04.10.11 06:31:18ОтветитьЦитировать
Ты выложи всю БД, а мы потренируемся Иначе на чем тестить?
Ratings: 0 negative/0 positive

Re: Запрос - спортивный интерес
Extortioner
Автор

Сообщений: 854
Откуда: Новосибирск
Дата: 04.10.11 06:49:08ОтветитьЦитировать
Да пожалуйста!
Только файл получился 361Кб - к теме не прикрепится - выложу на файлообменник. ifolder.ru

В архиве 2 файла:
t0 - создаёт таблицы и индексы
t1 - производит вставку данных
Ну и раз уж выложил все данные то придётся небольшое описание сделать.
  
  CREATE TABLE TBL_NUM_NP_ISSUE (         - таблица, содержащая данные о выходах изданий  
         ID_NEWSPAPER Smallint NOT NULL,  - идентификатор издания  
         NUM_NEWSPAPER Smallint NOT NULL, - годовой номер издания  
         DATE_ISSUE Date NOT NULL,        - дата выхода номера  
         BASE_NUM Smallint NOT NULL       - сквозной номер издания  
    
  CREATE TABLE TBL_NUM_NP_ORDER (         - таблица, содержащая данные о выходах заказов  
         ID_NEWSPAPER Smallint NOT NULL,  - идентификатор издания, в котором выходит заказ  
         ID_ORDER Integer NOT NULL,       - идентификатор заказа  
         NUM_NEWSPAPER Smallint NOT NULL, - вот этого поля здесь не должно быть, я как раз занимаюсь тем, что убираю  
               его отсюда, (в прошлой версии программы num_newspaper как раз и был сквозным номером).  
         D Smallint DEFAULT 0 NOT NULL,   - удалён ли выход, если 0 - выход будет, если 1 - значит выход убрали  
         BASE_NUM Smallint NOT NULL       - сквозной номер



Исправлено: Extortioner, 04.10.11 06:51
Ratings: 0 negative/0 positive

Re: Запрос - спортивный интерес
medstrax

Сообщений: 4474
Дата: 04.10.11 08:06:22ОтветитьЦитировать
Эх, все равно померяться письками не получится. На каком железе сравнивать скорость запросов? Можно ведь всю БД кинуть в рам-диск например...
Ratings: 0 negative/0 positive

Re: Запрос - спортивный интерес
Extortioner
Автор

Сообщений: 854
Откуда: Новосибирск
Дата: 04.10.11 11:12:16ОтветитьЦитировать
medstrax
Эх, все равно померяться письками не получится. На каком железе сравнивать скорость запросов? Можно ведь всю БД кинуть в рам-диск например...
Вовсе не обязательно мериться письками на одном и том же железе - выполните мой первоначальный запрос на моей базе - вы получите какое-то число секунд, мой результат вы знаете, выполнив ещё пару запросов вы сможете определить на сколько ваше железо производительнее моего.
Ratings: 0 negative/0 positive

Re: Запрос - спортивный интерес
Igor Korolyov

Сообщений: 32507
Дата: 04.10.11 13:09:40ОтветитьЦитировать
Если результат зависит от порядка следования таблиц в запросе, при том без всяких хинтов, это лишь говорит о том что оптимизатор в твоей СУБД крайне убогий. Даже в фоксе как правило (можно судить по косвенным признакам) порядок следования таблиц/подзапросов не сильно влияет на результат - ну, конечно, если не использовать хинт FORCE.
Кстати, при переводе на фокс (все числовые поля в обычный 32-битный Integer) приведенные запросы с твоими данными отрабатывают у меня быстрее чем за 1.5 секунды. БОльшая DBF занимает всего то чуть более 4.5Мб.
На оракле (да, не спорю, сервер достаточно мощный) ВСЕ 3 приведенных стандартных запроса (кроме использующего аналитическую функцию) выполняются (и выдают результат - что для данного запроса есть существенная часть времени) за абсолютно одинаковое время (примерно 1 секунду) - при том оптимизатор (что вполне логично) строит для них абсолютно идентичные планы исполнения. Запрос с аналитической функцией выполняется несколько быстрее, он более оптимален с точки зрения доступа к данным (всего 2 полных сканирования обоих таблиц, против 4-х сканирований для остальных запросов), однако несколько тяжелее по затратам CPU и памяти.
Для некоторой оптимизации можно предложить индекс по TBL_NUM_NP_ORDER(ID_ORDER, BASE_NUM) - он позволит немного улучшить как стандартный запрос, так и запрос с аналитикой (т.к. по сути только эти 2 поля из таблицы и нужны в запросе) - ну и практически нулевая польза от индекса по TBL_NUM_NP_ISSUE(BASE_NUM, NUM_NEWSPAPER) - просто потому что сама таблица очень маленькая.
Однако, ситуация может существеннейшим образом изменится (и скорость работы, и планы исполнения тоже кардинально поменяются), если в запрос будет добавлено условие - например WHERE tbno.id_order < 100. По логике задачи, я думаю, запрос с ограничением того или иного рода более логичен, чем запрос всего-всего-всего


------------------
WBR, Igor
Ratings: 0 negative/0 positive

Re: Запрос - спортивный интерес
Extortioner
Автор

Сообщений: 854
Откуда: Новосибирск
Дата: 04.10.11 19:08:00ОтветитьЦитировать
Да, Игорь, появление индекса TBL_NUM_NP_ORDER(ID_ORDER, BASE_NUM) позволило отыграть некоторое количество времени.
Ну а по поводу появления Where - естественно, это же ведь только небольшая часть большого запроса, в котором происходит объединение 7ми таблиц
Просто одним из факторов тормозящих выполнение этого большого запроса был этот запрос. Теперь благодаря этому обсуждению и вам в частности запрос стал работать быстрее.
Спасибо за подробный анализ задачи.
Ну а по поводу оптимизатора FireBird - думаю, что я просто не умею его готовить



Исправлено: Extortioner, 04.10.11 19:10
Ratings: 0 negative/0 positive



Извините, только зарегистрированные пользователи могут писать в этом форуме.

On-line: 35 Pliskin Божья_коровка Taran  and Guests: 32


© 2000-2019 Fox Club 
Яндекс.Метрика