Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оптимизация запроса  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
declare @table table (customer_id varchar(15), start datetime, result varchar(15), custom_data varchar(100))

insert into @table(customer_id, start, result, custom_data)
select 1, '20141101', 'Busy', null UNION ALL
select 1, '20141102', 'OK', '201411012999' UNION ALL
select 1, '20141103', 'NoAns', null UNION ALL
select 1, '20141104', 'NoAns', null UNION ALL
select 1, '20141105', 'OK', '20141105990' UNION ALL
select 1, '20141106', 'Busy', null UNION ALL
select 2, '20141102', 'OK', '20141102991' UNION ALL
select 2, '20141103', 'Busy', null UNION ALL
select 2, '20141104', 'OK', '20141104992' UNION ALL
select 3, '20141102', 'Busy', null UNION ALL
select 4, '20141102', 'OK', '20141102994'

select *
from @table


Желаемый результат:
customer_idstartresultcustom_data
12014-11-01BusyNULL
12014-11-02OK201411012999
12014-11-03NoAns201411012999
12014-11-04NoAns201411012999
12014-11-05OK20141105990
12014-11-06Busy20141105990
22014-11-02OK20141102991
22014-11-03Busy20141102991
22014-11-04OK20141104992
32014-11-02BusyNULL
42014-11-02OK20141102994


То есть требуется для каждого customer_id заполнить custom_data значением последней по времени строки с result = 'OK', если такая есть.

На таблице есть индекс по customer_id и кластерный на start.

Есть ли решение лучше, если в таблице около 30 млн. записей, из которой будут выбираться около 300 тыс. за несколько месяцев?
select (
		select top(1) custom_data
		from @table
		where result = 'OK'
			and customer_id = T.customer_id
			and start <= T.start
		order by start desc

		) data
		
		,customer_id
		,start
		,result
		
		,(
		select top(1) custom_data
		from @table
		where result = 'OK'
			and customer_id = T.customer_id
			and start <= T.start
		order by start desc

		) custom_data
from @table T
5 ноя 14, 14:24    [16800854]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
SQL 2005
5 ноя 14, 14:27    [16800871]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
А так и надо, что для полей data и custom_data выбираются одни и те же значения?
5 ноя 14, 14:28    [16800885]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Прошу прощения, одно из них лишнее
5 ноя 14, 14:49    [16801113]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8828
Может как-то ранжирование прикрутить,
FIRST_VALUE() OVER()
5 ноя 14, 15:06    [16801227]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
У меня SQL 2005.

Пробую добавить к таблице раг:
customer_idstartresultcustom_dataRN
12014-11-01BusyNULL1
12014-11-02OK2014110129992
12014-11-03NoAns2014110129992
12014-11-04NoAns2014110129992
12014-11-05OK201411059903
12014-11-06Busy201411059903
22014-11-02OK201411029911
22014-11-03Busy201411029911
22014-11-04OK201411049922
32014-11-02BusyNULL1
42014-11-02OK201411029941

, чтобы джойнить таблицу саму на себя по customer_id и по рангу, но простого решения не нашел.

Есть ощущение, что в два прохода будет быстрее, чем nested loop для top(1).
5 ноя 14, 15:24    [16801349]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4902
Это то, что доктор прописал?

declare @table table (customer_id varchar(15), start datetime, result varchar(15), custom_data varchar(100))

insert into @table(customer_id, start, result, custom_data)
select 1, '20141101', 'Busy', null UNION ALL
select 1, '20141102', 'OK', '201411012999' UNION ALL
select 1, '20141103', 'NoAns', null UNION ALL
select 1, '20141104', 'NoAns', null UNION ALL
select 1, '20141105', 'OK', '20141105990' UNION ALL
select 1, '20141106', 'Busy', null UNION ALL
select 2, '20141102', 'OK', '20141102991' UNION ALL
select 2, '20141103', 'Busy', null UNION ALL
select 2, '20141104', 'OK', '20141104992' UNION ALL
select 3, '20141102', 'Busy', null UNION ALL
select 4, '20141102', 'OK', '20141102994'

select T.*, 
	MAX(custom_data) OVER (PARTITION BY customer_id ORDER BY start)
from @table T
5 ноя 14, 15:30    [16801381]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
a_voronin
Это то, что доктор прописал?
virtuOS
SQL 2005
5 ноя 14, 15:39    [16801446]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Похоже, но для SQL 2005 не катит.
5 ноя 14, 15:39    [16801447]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8828
virtuOS, в этом случае придется составить таблицу старт-стопов.
5 ноя 14, 16:11    [16801622]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
o-o
Guest
select t1.customer_id, t1.start, t1.result, a.dt
from @table t1 outer apply (select t2.customer_id,
                                   max(t2.custom_data) as dt
                            from @table t2
                            where t2.result = 'OK'
                                  and t1.customer_id = t2.customer_id
                                  and t2.start <= t1.start
                            group by t2.customer_id
			                )a;

?
5 ноя 14, 16:26    [16801740]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
o-o
select t1.customer_id, t1.start, t1.result, a.dt
from @table t1 outer apply (select t2.customer_id,
                                   max(t2.custom_data) as dt
                            from @table t2
                            where t2.result = 'OK'
                                  and t1.customer_id = t2.customer_id
                                  and t2.start <= t1.start
                            group by t2.customer_id
			                )a;

?

А чем эти яйцы будут быстрее?

Правильный дао:

create view OKs
with schema_binding
as
select customer_id, start, custom_data from aTable where result = 'OK';

go

create unique clustered index idx_OKs on OKs (customer_id, start);

go

select (		customer_id
		,start
		,result
		,(
		select top(1) custom_data
  		  from OKs with(noexand)
 		  where customer_id = T.customer_id and start <= T.start
		  order by start desc

		) custom_data
from aTable T
5 ноя 14, 16:45    [16801881]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
o-o
Guest
aleks2,
у яиц план разный, не заметили?
и я не утверждаю, что мой вариант быстрее.
у меня там вопросительный знак, а не восклицательный
5 ноя 14, 16:56    [16801954]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
aleks2
А чем эти яйцы будут быстрее?

Получилось медленнее

Через TOP:

(строк обработано: 1624)
Scan count 1625, logical reads 1950821, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(строк обработано: 1)

SQL Server Execution Times:
   CPU time = 3015 ms,  elapsed time = 3237 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.



Через outer apply:

(строк обработано: 1624)
Scan count 1625, logical reads 5681955, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(строк обработано: 1)

SQL Server Execution Times:
   CPU time = 8438 ms,  elapsed time = 9312 ms.


Видимо придется оставлять мой первый вариант и ждать переезда на новую версию сервера
5 ноя 14, 16:58    [16801964]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
virtuOS
Видимо придется оставлять мой первый вариант и ждать переезда на новую версию сервера

Тредстартер склонен к иллюзиям.
Он думает оконные функции действуют исключительно святым духом.
5 ноя 14, 17:33    [16802159]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
o-o
Guest
по крайней мере, он тестит, прежде чем лалакать. и цифры приводит.
а что у вас аллергия на оконные функции, давно замечено.
может, разорюсь и из дома на 2012-ом протестирую воронинский вариант,
поди самый быстрый.
уж скан-то точно один
5 ноя 14, 18:03    [16802320]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8828
o-o,

с чего бы воронинский вариант работал корректно?
declare @table table (customer_id varchar(15), start datetime, result varchar(15), custom_data varchar(100))

insert into @table(customer_id, start, result, custom_data)
select 1, '20141101', 'Busy', null UNION ALL
select 1, '20141102', 'OK', '20141105990' UNION ALL
select 1, '20141103', 'NoAns', null UNION ALL
select 1, '20141104', 'NoAns', null UNION ALL
select 1, '20141105', 'OK', '201411012999' UNION ALL
select 1, '20141106', 'Busy', null UNION ALL
select 2, '20141102', 'OK', '20141102991' UNION ALL
select 2, '20141103', 'Busy', null UNION ALL
select 2, '20141104', 'OK', '20141104992' UNION ALL
select 3, '20141102', 'Busy', null UNION ALL
select 4, '20141102', 'OK', '20141102994'

select T.*, 
	MAX(custom_data) OVER (PARTITION BY customer_id ORDER BY start)
from @table T
5 ноя 14, 18:47    [16802538]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
o-o
Guest
Владислав Колосов,

во блин, я его вообще не имея 2012-ого сервера под рукой НЕ СМОТРЮ.
говорю, всяко быстрее выйдет, используя max() over (order by).
дома гляну. всем пока
5 ноя 14, 18:58    [16802582]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
o-o
Guest
глядя на воронинский вариант, меня осенило: мой вариант тоже неправильный
при чем там max(custom_data), когда надо top(1) custom_data order by start desc?
исправленное:
select t1.customer_id, t1.start, t1.result, a.dt
from @table t1 outer apply (select top(1) t2.custom_data as dt
			    from @table t2
			    where t2.result = 'OK'
				    and t2.customer_id = t1.customer_id
				    and t2.start <= t1.start
			    order by start desc)a;
5 ноя 14, 22:05    [16803079]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
o-o
по крайней мере, он тестит, прежде чем лалакать. и цифры приводит.
а что у вас аллергия на оконные функции, давно замечено.
может, разорюсь и из дома на 2012-ом протестирую воронинский вариант,
поди самый быстрый.
уж скан-то точно один


Цифры тредстартера демонстрируют тока полную безграмотность оного.
Индексами там даже не пахнет.
6 ноя 14, 06:18    [16804053]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
aleks2
Индексами там даже не пахнет.

Позор на мою голову, индекса на customer_id действительно нет и используется другой индекс. Этот индекс добавлен в новой версии разработчиками ПО на новом же сервере SQL 2012, который тестируется. На нём картина совсем иная.
6 ноя 14, 09:24    [16804394]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
o-o
Guest
отчет по яйцам (если правильный top искать, а не max, то действительно в профиль )
+ идея a_voronin-а, воплощенная в жизнь при условии, что значения из custom_data конвертируются в decimal
(кодовое название "привет из сауны")
+ демонстрация "что такое % в планах относительно batch-а" для Wlr-l

set statistics time on;
set statistics io on;

select t1.customer_id, t1.start, t1.result, a.custom_data
from dbo.virt t1 outer apply (select top(1) t2.custom_data as custom_data
							from dbo.virt t2
							where t2.result = 'OK'
								and t2.customer_id = t1.customer_id
								and t2.start <= t1.start
							order by start desc
			                )a;


select  customer_id
		,start
		,result
		
		,(
		select top(1) custom_data
		from dbo.virt
		where result = 'OK'
			and customer_id = T.customer_id
			and start <= T.start
		order by start desc

		) custom_data
from dbo.virt t;

select customer_id,
		start,
		result,
		cast( 
		max(nullif((year(start)*10000 + month(start) * 100 + day(start)) * case result when 'ok' then 1 else 0 end, 0) * 100000000000000 + cast(custom_data as decimal(20,0)))
		OVER (PARTITION BY customer_id ORDER BY start)
		-
		max(nullif((year(start)*10000 + month(start) * 100 + day(start)) * case result when 'ok' then 1 else 0 end, 0) * 100000000000000)
		OVER (PARTITION BY customer_id ORDER BY start)
	    as varchar(100)) as custom_data
from dbo.virt;

set statistics time off;
set statistics io off;


(10000000 row(s) affected)
Table 'virt'. Scan count 10000001, logical reads 32348633, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 98171 ms, elapsed time = 102837 ms.

(10000000 row(s) affected)
Table 'virt'. Scan count 10000001, logical reads 32348633, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 99466 ms, elapsed time = 105333 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(10000000 row(s) affected)
Table 'Worktable'. Scan count 10100000, logical reads 60000001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'virt'. Scan count 1, logical reads 51537, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 195922 ms, elapsed time = 206656 ms.

К сообщению приложен файл. Размер - 59Kb
6 ноя 14, 12:21    [16805364]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
Ну а типерь сделай фильтрованный индекс и подзапрос.
6 ноя 14, 12:34    [16805441]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
o-o
Guest
aleks2,

ну и падажди дарагой, у меня такая ... сидит пад носом,
как увидит включенный ноут так и лететь мне с 6-ого этажа вслед за ним
как только так сразу, не боись, все опубликую
6 ноя 14, 12:46    [16805498]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
o-o
Guest
вариант от aleks2:

(10000000 row(s) affected)
Table 'OKs'. Scan count 10000000, logical reads 31875027, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'virt'. Scan count 1, logical reads 51537, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 44367 ms, elapsed time = 78217 ms.

К сообщению приложен файл. Размер - 18Kb
6 ноя 14, 13:59    [16806033]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить