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

Откуда:
Сообщений: 106
БД - MSSQL 2008 R2 х64
OS - Windows Server 2008 R2 (16гигов оперативы)

Представим что у нас есть запрос в котором используется 3 подзапроса
Эти подзапросы оформлены с помощью видов.

Общий запрос выглядит вот так
select 
	*
from
	[dbo].__AccountsSummaryPart1 as t1 -- подзапрос1
left join
	[dbo].__Equity as t2 -- подзапрос2
	on
		t1.ServerId = t2.ServerId
		and t1.AccountId = t2.AccountId
		and t1.BeginDate = t2.CreationTime
left join
	[dbo].__ExchangeRates as t4 -- подзапрос3
	on
		t1.ServerId = t4.ServerId
		and t1.BeginDate = t4.BeginDate
		and t1.EndDate = t4.EndDate		
		and t1.ServerCurrency = t4.currencyTo
		and t1.Currency = t4.currencyFrom

Первоначальный план
Картинка с другого сайта.

Как видно самое долгое время приходится на сортировку, причем как я понимаю сортировка происходит не в подзапросе а на какомто более высоком уровне, потому она такая длительная.

Привожу скорости работы подзапросов в отдельности
Подзапрос1 - 38118 строк, 0 сек
Картинка с другого сайта.

Подзапрос2 - 10млн строк, 12сек
Картинка с другого сайта.

Подзапрос3 - 220 строк, 2 сек
Картинка с другого сайта.

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

В результате общий запрос просто вешается от числа данных (я не дождался окончания).

Если сделать так

select * into #temp from __ExchangeRates

и подставить в общий запрос

left join
	#temp as t4
	on 
                t1.ServerId = t4.ServerId
		and t1.BeginDate = t4.BeginDate
		and t1.EndDate = t4.EndDate		
		and t1.ServerCurrency = t4.currencyTo
		and t1.Currency = t4.currencyFrom

Время выполнения составит 14секунд, что вполне приемлемо.
Картинка с другого сайта.

Если уберем 3й подзапрос из основного запроса
select 
	*
from
	[dbo].__AccountsSummaryPart1 as t1 -- подзапрос1
left join
	[dbo].__Equity as t2 -- подзапрос2
	on
		t1.ServerId = t2.ServerId
		and t1.AccountId = t2.AccountId
		and t1.BeginDate = t2.CreationTime
38118 строк, время выполнения 10сек
Картинка с другого сайта.


Можно ли както сказать оптимизатору чтобы он не оптимизировал подзапросы, а вначале выполнил их, а потом уже и общий запрос?

ЗЫ. Уже не первый раз встречаюсь с этой проблемой в SQL SERVER.
2 авг 11, 11:13    [11057261]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
truper
Можно ли както сказать оптимизатору чтобы он не оптимизировал подзапросы, а вначале выполнил их, а потом уже и общий запрос?

Оптимизатор выполняет весь запрос целиком, а не по частям

truper
ЗЫ. Уже не первый раз встречаюсь с этой проблемой в SQL SERVER.

Если вы считаете, что 3 запроса по отдельности и вместе есть одно и тоже, то это ваша проблема
2 авг 11, 11:20    [11057326]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
truper
Member

Откуда:
Сообщений: 106
Glory, Я ничего не считаю, я лишь спрашиваю возможно ли выйти из такой ситуации, если нет, придется городить огороды...
2 авг 11, 11:26    [11057380]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
truper
Glory, Я ничего не считаю, я лишь спрашиваю возможно ли выйти из такой ситуации, если нет, придется городить огороды...

Для получения нужного плана существует масса способов
- переписывание запроса
- создание нужных индексов
- использование хинтов
- использование принудительного плана
- разбиние запроса
2 авг 11, 11:30    [11057406]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
truper,

попробуйте force order, но не факт что поможет
а вобще Glory прав, это вам известно что в запросе делается, а сервер видит кучу, а не три отдельных запроса и эту кучу он обрабатывает последовательно.
2 авг 11, 11:33    [11057425]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
truper,

тексты VIEW не показали. Как же можно что-то конкретное сказать?
2 авг 11, 11:34    [11057435]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
1
Guest
а * в Select - это новая мода или как?
2 авг 11, 11:53    [11057654]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
fashionMan
Guest
1
а * в Select - это новая мода или как?


Это оптимизация времени написания текста запроса
2 авг 11, 11:59    [11057704]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
truper
Member

Откуда:
Сообщений: 106
CREATE VIEW __AccountsSummaryPart1
AS
select 
	ad.ServerId
	,ad.AccountId
	,ad.AccountType
	,ad.ServerCurrency
	,ad.Currency
	--,ad.ConvertCource
	,cast(dateadd(day, -day(ad.CreationTime) + 1, ad.CreationTime) as date) as BeginDate
	,case when ad.isDaily = 1 then cast(dateadd(day, 1, ad.CreationTime) as date) else ad.CreationTime end as EndDate -- дневные отчеты приводим к началу след дня
	,ad.Equity as EndEquity
	,ad.isDaily
from
	[dbo].AccountsDaily as ad --WITH (INDEX(AccountsDaily_main))
where
	ad.IsUsed = 0
	and ad.Currency != ''

Определяем конечный эквити за расчетный период.

CREATE VIEW __Equity
AS
select 
	ad.ServerId
	,ad.AccountId
	,ad.AccountType
	--,ad.ConvertCource
	,ad.Currency
	,case when ad.isDaily = 1 then cast(dateadd(day, 1, ad.CreationTime) as date) else ad.CreationTime end as CreationTime -- дневные отчеты приводим к началу след дня
	,ad.Equity
	,ad.isDaily
from
	[dbo].AccountsDaily as ad
where
	ad.Currency != ''
	and ad.Currency is not null

Вид в котором содержатся все наборы эквити, по сути с помощью него мы находим начальный эквити.


CREATE VIEW __ExchangeRates
AS
select top 1 with ties
	t1.BeginDate
	,t1.EndDate
	,t1.ServerId
	,t2.CurrencyFrom
	,t2.CurrencyTo
	,t2.ExchangeRate
from 
	[dbo].__Periods as t1
left join
	[dbo].CurrenciesRates as t2
	on
		t1.ServerId = t2.ServerId
		and t2.TickDatetime <= t1.EndDate
order by
	ROW_NUMBER() over(partition by t1.BeginDate, t1.EndDate, t1.ServerId, t2.CurrencyFrom, t2.CurrencyTo order by t2.TickDatetime desc)
	

Курсы конвертации различных валют.


create VIEW __Periods
WITH SCHEMABINDING
AS
select 
	ServerId
	,cast(dateadd(day, -day(ad.CreationTime) + 1, ad.CreationTime) as date) as BeginDate
	,case when ad.isDaily = 1 then cast(dateadd(day, 1, ad.CreationTime) as date) else ad.CreationTime end as EndDate -- дневные отчеты приводим к началу след дня
	--,ad.IsDaily
	,0 as IsDaily
	,COUNT_BIG(*) as cnt
from
	[dbo].AccountsDaily as ad
where
	ad.IsUsed = 0
	and ad.Currency != ''
group by 
	ServerId
	,cast(dateadd(day, -day(ad.CreationTime) + 1, ad.CreationTime) as date) 
	,case when ad.isDaily = 1 then cast(dateadd(day, 1, ad.CreationTime) as date) else ad.CreationTime end -- дневные отчеты приводим к началу след дня

Периоды которые необходимо расчитать.


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

ЗЫ. * - чтобы много кода небыло.
Force order - помог, запрос выполняется 30 сек. Вот только этот запрос также является видом, а в виде не дает указывать OPTION (FORCE ORDER)
2 авг 11, 12:09    [11057794]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
Crimean
Member

Откуда:
Сообщений: 13147
уберите row_number() из "до" и поставьте его "после" и будет вам щасте
или замените ЭТО табличной UDF, все равно возвращается 1 строка
если эффективно реализуете табличную UDF, результат отдадите табличной переменной с ПК то возможно будет "оно"
2 авг 11, 12:22    [11057953]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
truper
Member

Откуда:
Сообщений: 106
Crimean
уберите row_number() из "до" и поставьте его "после" и будет вам щасте
или замените ЭТО табличной UDF, все равно возвращается 1 строка
если эффективно реализуете табличную UDF, результат отдадите табличной переменной с ПК то возможно будет "оно"


К сожалению там возвращается не одна строка а 100 ~ 1000
2 авг 11, 12:41    [11058099]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
Мелочь
Guest
если во вьюхе __ExchangeRates можно сразу отфильтровать Currency != ''

И смысл в ней ранжировать записи, если вы не собираетесь делать merge join ?
2 авг 11, 12:59    [11058258]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
truper
Member

Откуда:
Сообщений: 106
Мелочь
если во вьюхе __ExchangeRates можно сразу отфильтровать Currency != ''

И смысл в ней ранжировать записи, если вы не собираетесь делать merge join ?


Ну там не просто ранжирование а определение последнего курса за расчетых период, по сути там делается только сортировка по каждой валютной паре в заданном интервале.
2 авг 11, 13:08    [11058330]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
А_ТАК_КАК
Guest
select 
	*
from
	[dbo].__AccountsSummaryPart1 as t1 -- подзапрос1
left join
	[dbo].__Equity as t2 -- подзапрос2
	on
		t1.ServerId = t2.ServerId
		and t1.AccountId = t2.AccountId
		and t1.BeginDate = t2.CreationTime
LEFT OUTER HASH JOIN
	[dbo].__ExchangeRates as t4 -- подзапрос3
	on
		t1.ServerId = t4.ServerId
		and t1.BeginDate = t4.BeginDate
		and t1.EndDate = t4.EndDate		
		and t1.ServerCurrency = t4.currencyTo
		and t1.Currency = t4.currencyFrom

2 авг 11, 13:38    [11058555]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
truper
Member

Откуда:
Сообщений: 106
А_ТАК_КАК
select 
	*
from
	[dbo].__AccountsSummaryPart1 as t1 -- подзапрос1
left join
	[dbo].__Equity as t2 -- подзапрос2
	on
		t1.ServerId = t2.ServerId
		and t1.AccountId = t2.AccountId
		and t1.BeginDate = t2.CreationTime
LEFT OUTER HASH JOIN
	[dbo].__ExchangeRates as t4 -- подзапрос3
	on
		t1.ServerId = t4.ServerId
		and t1.BeginDate = t4.BeginDate
		and t1.EndDate = t4.EndDate		
		and t1.ServerCurrency = t4.currencyTo
		and t1.Currency = t4.currencyFrom



Этот вариант очень хорош, спасибо человечище)
Теперь запрос выполняется за 10сек.
Как я понимаю HASH JOIN не позволяет оптимизатору использовать таблицу для внутренних связей, а использует ее как набор хэшей т.е. временную.
2 авг 11, 14:06    [11058803]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при автоматической оптимизации запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
truper
Как я понимаю HASH JOIN не позволяет оптимизатору использовать таблицу для внутренних связей, а использует ее как набор хэшей т.е. временную.

Он всего лишь меняет стратегию соединения
2 авг 11, 14:09    [11058823]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить