MSSql: Использование оператора APPLY в TSql

[abyrvalg]

Сравните планы выполнения, мне кажется, так будет оптимальней:

select t.rn
	 , t.dealid deal_id
	 , t.customerid cust_id, c.customername cust_name
	 , t.nomenclatureid nom_id, nom.nomenclaturename nom_name
	 , t.dealdate, t.qty
from (
	select d.DealID, d.customerid, nomenclatureid
		 , [COUNT] qty -- нехорошо :(
		 , dealdate 
		 -- нумеруем отдельно заказы каждого клиента с сортировкой по дате
		 , ROW_NUMBER() over( partition by customerid order by dealdate desc ) rn
	from Deal d
	) t
	join customer c on( c.customerid = t.customerid )
	join nomenclature nom on( nom.nomenclatureid = t.nomenclatureid )
where t.rn <= 10
order by cust_id, dealdate

Или же я не правильно понял задачу.

[hyperlam]

Все правильно.
Большое спасибо, ваш вариент работает быстрее.

[kuda78]

Сравнил план выполнения.

В таблице customer 5000 записей, в таблице Deal около 10 миллионов.

Используя OUTER APPLY:
Время исполнения: 1 сек
Estimated subtree cost: 0,17.
Profiler — CPU: 765
Profiler — Reads: 34973
Profiler — Duration: 783

Используя ROW_NUMBER estimated subtree cost 450.00.
Время исполнения: 48 сек
Estimated subtree cost: 450,00.
Profiler — CPU: 125979
Profiler — Reads: 41707
Profiler — Duration: 47346

[pieceofsummer]

CREATE INDEX I_Deal ON Deal(CustomerID, DealDate, NomenclatureID)
Индекс построен по возрастанию даты, а везде используется сортировка по убыванию. Предположу, что партишену более западло просматривать 10 миллионов строк в столь непотребном порядке.

[kuda78]

Попробовал использовать 4 разных индекса:

CREATE INDEX I_Deal1 ON Deal (CustomerID, DealDate)
CREATE INDEX I_Deal2 ON Deal (CustomerID, DealDate desc)
CREATE INDEX I_Deal3 ON Deal (DealDate desc, CustomerID)
CREATE INDEX I_Deal3 ON Deal (DealDate, CustomerID)

Разницы никакой не обнаружил.
По результатам изучения Execution Plan для скриптов, которые приложены к примеру, в любом случае при использовании ROW_NUMBER сначала производится расставление номеров для всех строк, т.е. до фильтра «t.rn <= 10» доходит больше миллиона строк, а уже только после фильтрации получается 30 строк.

[hyperlam]

Сгенерировал 1M записей в табличку Deal следующим кодом:

USE Claims 
GO

SET NOCOUNT ON
GO

DECLARE @i INT

SET @i = 1

WHILE @i < 1000000
BEGIN
	INSERT INTO Deal(CustomerID, NomenclatureID, [Count], DealDate)
	SELECT (@i % 3) + 1, (@i % 3) + 1, 250, DateADD(n, -@i, '2010-01-01')
	
	SET @i = @i + 1
END

Результат сравним с результатом kuda78
Если судить по ExecutionPlan, cервер сначала нумерует ВСЕ строки таблицы DEALS и только потом начинает перемножать её с другими таблицами.

[hyperlam]

Люди, которые считают что они занют все, сильно раздражают нас, людей которые действительно знают все.

[acerv]

согласен с предыдущим товарищем.

к аффтару: а вы чего такой поверхностный? хоть бы написали, чем cross apply от outer apply отличается.

еще могу порекомендовать прочитать книжку:
www.amazon.com/Microsoft%C2%AE-Server%C2%AE-T-SQL-Fundamentals-PRO-Developer/dp/0735626014

в интернете полно где лежит.

[hyperlam]

Может быть, но я не ставил целью дать описание оператора, это можно прочитать в MSDN, ссылку я дал.
Просто хотел поделиться с сообществом интересной находкой. Лично я про этот оператор не знал, и он помог мне элегантно решить проблему, которую мы раньше решали неудобно и громоздко.

Если комунибудь из коллег, этот оператор (семейство операторов) поможет решать их задачи, значит я писал этот пост не зря.

Вот, например, решение от abyrvalg я так же считаю интресным, и возму его себе на заметку.

За книжку большое спасибо, на досуге почитаю.

[Iamzet]

Вы попали пальцем в небо, преподносите давно существующую и относительно известную фичу как открытие Америки. Например, кто работает с xml столбцами знает, что без cross apply xml.nodes никуда…

Из примеров кода, которые видел в последнее время, больше всего восхитила остроумная выборка значений поля в таблице в строку с разделителями в один запрос

select (
select [column] + [delimeter] as 'data()'
from [table] as A
for xml path(''))


[hyperlam]

Никакой Америки. Просто поиск оптимального решения конкретной задачи.

P.S Коллеги, давайте вопрос известности/неизвестности оператора APPLY оставим вне рамок этой темы, тем более, как показывает статистика, далеко не все, кто применяет TSql, знакомы со всеми его возможностями.

[Iamzet]

Ну в таком случае, давайте весь BOL сюда скопируем, ведь не все знакомы со всеми возможностями, кому то может пригодится.

[Zerkella]

> Правый вход оценивается

Имелось в виду вычисляется

[hyperlam]

Перевод я тоже взял с MSDN:
http://technet.microsoft.com/ru-ru/library/ms175156.aspx

[Zerkella]

Мелкомягкие не поправили ПРОМТ :)