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

Откуда:
Сообщений: 109
Здравствуйте.
Есть запрос.
+
	;WITH Quotes AS
	(
		SELECT a.CandleDateTime, a.PairID, a.AskHigh, a.AskLow, a.BidHigh, a.BidLow
		FROM
		(
			SELECT a.CandleDateTime, a.PairID, 
				CASE WHEN a.BrokerServerID IS NULL THEN (a.AskClose + a.BidClose) / 2 ELSE a.AskHigh END AS AskHigh, 
				CASE WHEN a.BrokerServerID IS NULL THEN (a.AskClose + a.BidClose) / 2 ELSE a.AskLow END AS AskLow, 
				CASE WHEN a.BrokerServerID IS NULL THEN (a.AskClose + a.BidClose) / 2 ELSE a.BidHigh END AS BidHigh, 
				CASE WHEN a.BrokerServerID IS NULL THEN (a.AskClose + a.BidClose) / 2 ELSE a.BidLow END AS BidLow
			FROM dbo.eConCandles1M AS a WITH(NOLOCK) 
			UNION ALL
			SELECT af.CandleDateTime, af.PairID, 
				CASE WHEN af.BrokerServerID IS NULL THEN (af.AskClose + af.BidClose) / 2 ELSE af.AskHigh END AS AskHigh, 
				CASE WHEN af.BrokerServerID IS NULL THEN (af.AskClose + af.BidClose) / 2 ELSE af.AskLow END AS AskLow, 
				CASE WHEN af.BrokerServerID IS NULL THEN (af.AskClose + af.BidClose) / 2 ELSE af.BidHigh END AS BidHigh, 
				CASE WHEN af.BrokerServerID IS NULL THEN (af.AskClose + af.BidClose) / 2 ELSE af.BidLow END AS BidLow
			FROM dbo.eConQuotesFlat1M AS af WITH(NOLOCK)
		) as a		 
	)
	------------------------------------------------------------------------------------
 
	--- Calculate equity low/high per each day ----------------------------------------------
	SELECT CAST(sa.CandleDateTime AS date) AS [DateValue], MIN(sa.ProfitLow) AS EquityLow, MAX(sa.ProfitHigh) AS EquityHigh, MIN(sa.ProfitLowMoney) AS EquityLowMoney, 
		MIN(sa.ProfitLowNoBalance) AS EquityLowNoBalance, MAX(sa.ProfitHighNoBalance) AS EquityHighNoBalance,
		dbo.MaxDrawdownBunch(sa.ProfitHigh, sa.ProfitLow, sa.CandleDateTime, 1, 0) AS IntradayDrawdownPercent,			
		dbo.MaxDrawdownBunch(sa.ProfitHighNoBalance, sa.ProfitLowNoBalance, sa.CandleDateTime, 0, 0) AS IntradayDrawdownMoney, sa.StatsAccountID
	INTO #Result
	FROM
	(
		SELECT a.CandleDateTime,
			100 * (t.EquityPrev * (((a.ProfitLow - t.Balance) / NULLIF(t.Balance, 0)) + 1) - 1) AS ProfitLow, 
			100 * (t.EquityPrev * (((a.ProfitHigh - t.Balance) / NULLIF(t.Balance, 0)) + 1) - 1) AS ProfitHigh,
			a.ProfitLowMoney, a.ProfitLowNoBalance, a.ProfitHighNoBalance, t.StatsAccountID
		FROM @TWR AS t
		INNER   JOIN
		(
			SELECT tk.CandleDateTime,
				tk.ProfitLow + ISNULL(st.CloseValue, 0.0) AS ProfitLow, 
				tk.ProfitHigh + ISNULL(st.CloseValue, 0.0) AS ProfitHigh,
				tk.ProfitLow + ISNULL(st.CloseValueMoney, 0.0) AS ProfitLowMoney,
				tk.ProfitLow + ISNULL(st.CloseValueMoney, 0.0) - ISNULL(b.Balance, 0.0) AS ProfitLowNoBalance,  
				tk.ProfitHigh + ISNULL(st.CloseValueMoney, 0.0) - ISNULL(b.Balance, 0.0) AS ProfitHighNoBalance, tk.StatsAccountID
			FROM 
			(
				SELECT tk.CandleDateTime, SUM(tk.ProfitLow) AS ProfitLow, SUM(tk.ProfitHigh) AS ProfitHigh, tk.StatsAccountID
				FROM 
				(
					SELECT ISNULL(q.CandleDateTime, st.CloseTimeUtc) AS CandleDateTime,
						CASE 
							WHEN ISNULL(q.CandleDateTime, st.CloseTimeUtc) < st.TicketEnd THEN 
								CASE 
									WHEN st.IsSell = 1 THEN (st.OpenPrice - q.AskHigh) * st.Units 
										* CASE WHEN st.IsCurrency = 1 THEN 1 ELSE ISNULL(q.AskLow1, 1/q.AskLow2) END 
									WHEN st.IsBuy = 1 THEN (q.BidLow - st.OpenPrice) * st.Units
										* CASE WHEN st.IsCurrency = 1 THEN 1 ELSE ISNULL(q.BidHigh1, 1/q.BidHigh2) END 
								END
							WHEN q.CandleDateTime = st.TicketEnd THEN 0.0
						END AS ProfitLow,
						CASE
							WHEN ISNULL(q.CandleDateTime, st.CloseTimeUtc) < st.TicketEnd THEN 
								CASE 
									WHEN st.IsSell = 1 THEN (st.OpenPrice - q.AskLow) * st.Units 
										* CASE WHEN st.IsCurrency = 1 THEN 1 ELSE ISNULL(q.AskHigh1, 1/q.AskHigh2) END 
									WHEN st.IsBuy = 1 THEN (q.BidHigh - st.OpenPrice) * st.Units
										* CASE WHEN st.IsCurrency = 1 THEN 1 ELSE ISNULL(q.BidLow1, 1/q.BidLow2) END 
								END
							WHEN q.CandleDateTime = st.TicketEnd THEN 0.0
						END AS ProfitHigh, st.StatsAccountID
					FROM #ttt0 AS st
					OUTER APPLY
					(
						SELECT q.CandleDateTime, q.AskHigh, q.BidLow, q.AskLow, q.BidHigh, 
								q1.AskLow AS AskLow1, q1.BidHigh AS BidHigh1, q1.AskHigh AS AskHigh1, q1.BidLow AS BidLow1,
								q2.AskLow AS AskLow2, q2.BidHigh AS BidHigh2, q2.AskHigh AS AskHigh2, q2.BidLow AS BidLow2
						FROM
						(							
							SELECT q.CandleDateTime, q.AskHigh, q.BidLow, q.AskLow, q.BidHigh
							FROM Quotes as q
							WHERE q.PairID = st.PairID AND q.CandleDateTime BETWEEN st.OpenTimeUtc AND st.CloseTimeUtc
						) AS q
						LEFT JOIN 
						(
							SELECT q.CandleDateTime, q.AskLow, q.BidHigh, q.AskHigh, q.BidLow
							FROM Quotes as q
							WHERE q.PairID = st.PairID1 AND q.CandleDateTime BETWEEN st.OpenTimeUtc AND st.CloseTimeUtc
						) AS q1
							ON q.CandleDateTime = q1.CandleDateTime 
						LEFT JOIN 
						(
							SELECT q.CandleDateTime, q.BidHigh, q.AskLow, q.AskHigh, q.BidLow
							FROM Quotes as q
							WHERE q.PairID = st.PairID2 AND q.CandleDateTime BETWEEN st.OpenTimeUtc AND st.CloseTimeUtc
						) AS q2
							ON q.CandleDateTime = q2.CandleDateTime 
					) AS q
					--where IsCurrency = 0
				) AS tk
				GROUP BY tk.CandleDateTime, tk.StatsAccountID	 
			) AS tk
			OUTER APPLY
			(
				SELECT TOP(1) CloseValue, CloseValueMoney
				FROM #CloseTimeBalances
				WHERE CloseTimeUtc <= tk.CandleDateTime AND IsBalance IN (0, 2)
				 AND #CloseTimeBalances.StatsAccountID = tk.StatsAccountID
				ORDER BY CloseTimeUtc DESC, IsBalance					
			) AS st
			OUTER APPLY
			(
				SELECT SUM(CloseValue) AS Balance
				FROM #CloseTimeBalances
				WHERE CloseTimeUtc <= tk.CandleDateTime AND IsBalance = 1 
				 AND #CloseTimeBalances.StatsAccountID = tk.StatsAccountID
			) AS b	
		) AS a
			ON a.CandleDateTime >= t.StartDate AND a.CandleDateTime < t.EndDate AND t.StatsAccountID = a.StatsAccountID
	) AS sa 
	GROUP BY CAST(sa.CandleDateTime AS date), sa.StatsAccountID

в таблице #ttt0 700 записей, сервер строит план (в архиве называется 700.xml)
потом выполняю тот же запрос, но подставляю туда #ttt1, которую получил профильтровав записи из #ttt0, 350 записей всего (в архиве план 350.xml).
Несмотря на то, что стоимость плана 700 в раза больше, чем 350 (очевидно, из-за того, что количество обрабатываемых записей в 2 раза меньше), время выполнения запроа почти в 2 раза меньше!
Изучил планы и пришел к выводу, что различие в скорости из-за того, что в плане 700 сервер выбрал Hash Match Aggregate, а в 350 - Stream Aggregate. Во всяком случае это единственное существенное различие в планах, которое я заметил. Решил проверить свою догадку, добавив option(hash group). Но не тут-то было. Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
В общем вопрос классический, кто виноват и что делать?
25 янв 13, 12:59    [13827027]     Ответить | Цитировать Сообщить модератору
 Re: Stream Aggregate vs Hash Match Aggregate  [new]
AnaceH
Member

Откуда:
Сообщений: 109
Что-то файл не прикрепляется. Еще раз.

К сообщению приложен файл (планы.7z - 23Kb) cкачать
25 янв 13, 13:02    [13827053]     Ответить | Цитировать Сообщить модератору
 Re: Stream Aggregate vs Hash Match Aggregate  [new]
Гость333
Member

Откуда:
Сообщений: 3683
AnaceH
Изучил планы и пришел к выводу, что различие в скорости из-за того, что в плане 700 сервер выбрал Hash Match Aggregate, а в 350 - Stream Aggregate.

Нууу... это весьма зыбкое предположение. Планы ещё много чем отличаются, например местом обработки таблицы @TWR.

Попробуйте, например, создать индекс на #CloseTimeBalances(StatsAccountID). Много ресурсов уходит на Lazy Spool'ы из этой таблицы.

Что, кстати, возвращает
select count(distinct StatsAccountID)
from #CloseTimeBalances
?
25 янв 13, 13:52    [13827620]     Ответить | Цитировать Сообщить модератору
 Re: Stream Aggregate vs Hash Match Aggregate  [new]
AnaceH
Member

Откуда:
Сообщений: 109
Гость333
AnaceH
Изучил планы и пришел к выводу, что различие в скорости из-за того, что в плане 700 сервер выбрал Hash Match Aggregate, а в 350 - Stream Aggregate.

Нууу... это весьма зыбкое предположение. Планы ещё много чем отличаются, например местом обработки таблицы @TWR.

Попробуйте, например, создать индекс на #CloseTimeBalances(StatsAccountID). Много ресурсов уходит на Lazy Spool'ы из этой таблицы.

Индекс создал, используется, немного ускорился запрос, но пропорция осталась прежней. Даже решил для теста исключить оба спула, убрал апплаи, работать оба запроса стали быстрее где-то на четверть, но пропорция осталась прежней: запрос с большим количеством строк отрабатывает быстрее. Я не могу понять, почему сервер не может создать план с хинтом option(hash group)? Ведь он и так создает план с хэш группом в случае 700-а строк. Если бы он все же создал план, то можно было бы уже с точностью сказать, дело в способе агрегации или нет.
Гость333
Что, кстати, возвращает
select count(distinct StatsAccountID)
from #CloseTimeBalances
?

10 (всего записей 300)
25 янв 13, 14:19    [13827944]     Ответить | Цитировать Сообщить модератору
 Re: Stream Aggregate vs Hash Match Aggregate  [new]
Гость333
Member

Откуда:
Сообщений: 3683
AnaceH
почему сервер не может создать план с хинтом option(hash group)?

С этим хинтом оптимизатор пытается все операторы Stream Aggregate (Aggregate) заменить на Hash Match (Aggregate).
С убранными apply'ами — хинт option(hash group) проходит?
Можно увидеть запрос и планы без apply'ев?
25 янв 13, 14:36    [13828144]     Ответить | Цитировать Сообщить модератору
 Re: Stream Aggregate vs Hash Match Aggregate  [new]
AnaceH
Member

Откуда:
Сообщений: 109
Гость333,

Большое спасибо за наводку, дело действительно было в спулах. Добавил индекс
CREATE NONCLUSTERED INDEX idx_CloseTimeBalances_ ON #CloseTimeBalances (StatsAccountID, CloseTimeUtc, IsBalance) INCLUDE(CloseValue, CloseValueMoney) where IsBalance IN (0, 2), заработало все куда веселее.

Теперь меня смущает только то, что при чтении записей из eConCandles1M действительное количество строк превышает предполагаемое в 450 раз (3045442/6739). Это ведь скорее всего сказывается на производительности. С этим можно что-нибудь поделать?
25 янв 13, 17:45    [13829762]     Ответить | Цитировать Сообщить модератору
 Re: Stream Aggregate vs Hash Match Aggregate  [new]
Гость333
Member

Откуда:
Сообщений: 3683
AnaceH
Теперь меня смущает только то, что при чтении записей из eConCandles1M действительное количество строк превышает предполагаемое в 450 раз (3045442/6739).

При loop join'ах это штатное явление. Предполагаемое количество строк рассчитывается для одного прохода вложенного цикла, а действительное — суммарно для всех проходов цикла.

Простой пример. Подготовка тестовых данных ("главная" таблица tab_1 на 100 записей и "подчинённая" tab_2, в которой есть по две записи на каждую запись главной таблицы):
set nocount on;
use tempdb;
go
create table tab_1 (id_1 int identity primary key);
create table tab_2 (id_2 int identity primary key, id_1 int);
create index i on tab_2(id_1);
go
insert tab_1 default values;
insert tab_2(id_1) select @@identity union all select @@identity;
go 100

Дальше включаем опцию "Include Actual Execution Plan" и выполняем запрос:
select *
from tab_1 t1 inner loop join tab_2 t2 on t1.id_1 = t2.id_1;

Смотрим в план выполнения. От таблицы tab_2 идёт стрелка, в которой написано "Estimated Number of Rows = 2" (что верно для одного прохода цикла) и "Actual Number of Rows = 200" (что верно для всех проходов цикла).

В ваших планах выполнения есть loop join с таблицами #ttt0/#ttt1, отсюда и разница.
25 янв 13, 18:23    [13829946]     Ответить | Цитировать Сообщить модератору
 Re: Stream Aggregate vs Hash Match Aggregate  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Гость333
AnaceH
Теперь меня смущает только то, что при чтении записей из eConCandles1M действительное количество строк превышает предполагаемое в 450 раз (3045442/6739).

При loop join'ах это штатное явление. Предполагаемое количество строк рассчитывается для одного прохода вложенного цикла, а действительное — суммарно для всех проходов цикла.
А еще можно смотреть планы с помощью SQL Sentry Plan Explorer, там нет такой проблемы.
26 янв 13, 00:33    [13831505]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить