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

Откуда: Прага
Сообщений: 776
Добрый день, коллеги.

Не сильно увлекаюсь хинтами, потому и спрашиваю: чем это вредно? И как правельно готовить :)

Есть запрос, оптимизатор его не понимает правильно, и начинает мудрить. Тогда я ему приколотил маленькую табличку с датами на loop join. Производительность поднялась в 10 раз (2 секунды вместо 20), но появилось 2 сообщения от сервера:
Сервер
Warning: The join order has been enforced because a local join hint is used.
Warning: The join order has been enforced because a local join hint is used.
Поскольку процедуру дёргает Excel, он этих сообщение пугается и выводит в меседжбокс, от чего, в свою очередь, пугаются юзеры. Причём, когда в эксцеле возникает месаджбокс, данные не обновляются (вылазит следующие сообщение о проблемах с данными), но это происходит только при первом запуске первого юзера с утра (потом, очевидно, план уже накеширован и до вечера работает). Но всё равно не приятно как-то, не аккуратно.

Чтобы Excel не пугался, в процедуре стоит
set nocount on
SET ANSI_WARNINGS off;
Чтоб ещё поставить, чтобы победить warning на хинтование? Если добавить
option (recompile) 
warning сообщение от сервера остаётся только одно, производительность возрастает до 1 секунды, но это ещё не победа.
24 июл 17, 11:47    [20669183]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Шыфл,

Проблема в хинтах в том что могут поменятся данные и хинт будет уже не помогать, а вредить. По моему мнению хинты можно добавлять если SQL немного тупит (именно он, а не админ), но во первых это должно быть задокументировано, чтобы об этом знали другие и человек который придет после вас, а во вторых проверять иногда, может хинт уже и не нужен.
У вас же судя по всему класический parameter sniffing, о котором есть огромное количество статей.
24 июл 17, 11:57    [20669251]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Шыфл,

warning от хинта join не убрать. Оставьте Recompile и всё, или переназначьте переменные и т/п
24 июл 17, 12:12    [20669336]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
aleksrov
Шыфл,

Проблема в хинтах в том что могут поменятся данные и хинт будет уже не помогать, а вредить. По моему мнению хинты можно добавлять если SQL немного тупит (именно он, а не админ), но во первых это должно быть задокументировано, чтобы об этом знали другие и человек который придет после вас, а во вторых проверять иногда, может хинт уже и не нужен.
У вас же судя по всему класический parameter sniffing, о котором есть огромное количество статей.


Хрустальный шар вас подвёл на этот раз, в процедуре нет внешних параметров, единственный параметр - текущая дата, из которой и формируется диапазон выборки (история за 8 кварталов) в локальной @table на 8 строк.

Эксперименты и гугль подсказали, что оптимальный вариант
option (RECOMPILE, FORCE ORDER) 
, и сервер перестаёт ругаться, потому что ты ему честно с самого начала заявляешь, что одна из табличек приколочена. Похоже, что это победа
24 июл 17, 12:13    [20669337]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Шыфл,
автор
в локальной @table на 8 строк.
никто не любит обходить грабли, все на них прыгают с улыбкой на лице
24 июл 17, 12:14    [20669346]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
архивариус
Member

Откуда:
Сообщений: 150
TaPaK
Шыфл,
автор
в локальной @table на 8 строк.
никто не любит обходить грабли, все на них прыгают с улыбкой на лице

из личного опыта, при количестве строк несколько десятков, замена @ на # (или ## не важно) дает выигрыш в разы
24 июл 17, 14:00    [20669843]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
TaPaK
Шыфл,
автор
в локальной @table на 8 строк.
никто не любит обходить грабли, все на них прыгают с улыбкой на лице


А есть предложения по существу? ТЗ - сделать групировку двух указателей и пивотировать её на 8 периодов.

Что-то типа

BusinessUnitName	NO1Q16	NS1Q16	NO2Q16	NS2Q16	NO3Q16	NS3Q16	NO4Q16	NS4Q16	NO1Q17	NS1Q17	NO2Q17	NS2Q17	NO3Q17	NS3Q17	NO4Q17	NS4Q17

Оригинальный вариант - Customer cross join BusinessUnit и 8 left join'ов c cуммами.
Мой вариант - группировка по 8 периодам через табличную переменную с последующим пивотированием.

Где здесь грабли то? Кроме того, что пивот - это лучше на клиенте, это я и сам знаю.

+
if OBJECT_ID('tempdb..#dataset') is not null
begin drop table #dataset end;

select 
		BU.BusinessUnitName ,
		BU.BusinessUnitId ,
		SUM(BR.NewOrders) AS [NewOrders],
		SUM(BR.NetSales) AS[NetSales],
		colname
into #dataset 
from 
dbo.fnBusinessResultsBasePerYear(@dateS,@dateE, 1,0,0,0,0,0,0, @CurrentUserId) BR
inner join Customer C  on c.CustomerId =BR.CustomerId
inner join Gbk on Gbk.GbkId=BR.GbkId 
inner join viSalesDisctrictRegionMarketPrimaryRepresentative SD on C.SalesDistrictId=SD.SalesDistrictId
inner join BusinessUnit BU	on GBK.BusinessUnitId=BU.BusinessUnitId
inner loop join @periods b on 
		DATEDIFF(day,b.DateStart, BR.BusinessResultDate)>=0
		and DATEDIFF(day,b.DateEnd, BR.BusinessResultDate)<=0
where SD.SalesMarketId=6 
group by BU.BusinessUnitName,BU.BusinessUnitId,colname
option (recompile, force order);
	
if OBJECT_ID('tempdb..#result') is not null
begin drop table #result end;

select distinct BusinessUnitName,BusinessUnitId 
into #result
from #dataset;

while @i>0 
	begin
	set @qq=(select colname from @periods where id=@i)
	set @qq1=N'ALTER TABLE #result add [NO ' + @qq + '] decimal(22,4), [NS ' + @qq + '] decimal(22,4)'
	exec sp_executesql @qq1
	
	set @qq0='
		update a 
		set 
			[NO ' + @qq + '] = b.NewOrders,
			[NS ' + @qq + '] = b.NetSales

		from 
		#result a join #dataset b on a.BusinessUnitId=b.BusinessUnitId
		and b.colname='''+ @qq + ''''
	
	exec sp_executesql @qq0
	
	set @i=@i-1
end;

alter table #result drop column BusinessUnitId

select * from #result

24 июл 17, 14:08    [20669878]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Шыфл,

автор
из личного опыта, при количестве строк несколько десятков, замена @ на # (или ## не важно) дает выигрыш в разы


автор
А есть предложения по существу?

обоим узнать почему табличные переменные зло и опираться не на чувства, а на факты
24 июл 17, 14:21    [20669927]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
архивариус
из личного опыта, при количестве строк несколько десятков, замена @ на # (или ## не важно) дает выигрыш в разы

Это не тот случай, тут это не критично. Попробовал заменить @ на #, выйграл 8%:

Если убрать хинт,
Total execution time 18372

Если добавать loop join (или remote join)
Total execution time 1965

option (recompile, force order)
Total execution time 1280

Обратно заменил # на @,
Total execution time 1390
24 июл 17, 14:29    [20669974]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
TaPaK
Шыфл,
обоим узнать почему табличные переменные зло и опираться не на чувства, а на факты

Прафесар, йа гатовая! (с)


У сервера 64 гига памяти и обычные диски, не SSD, и это явное узкое место. Операции с временными таблицами логируются, операции с табличными переменными идут в памяти. Какой билет читать, прафесар?
24 июл 17, 14:39    [20670028]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Шыфл
TaPaK
Шыфл,
обоим узнать почему табличные переменные зло и опираться не на чувства, а на факты

Прафесар, йа гатовая! (с)


У сервера 64 гига памяти и обычные диски, не SSD, и это явное узкое место. Операции с временными таблицами логируются, операции с табличными переменными идут в памяти. Какой билет читать, прафесар?

и тут меня стошнило
24 июл 17, 14:40    [20670033]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
TaPaK
и тут меня стошнило

Понюхай MySQL :)
24 июл 17, 14:52    [20670077]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Шыфл
Операции с временными таблицами логируются, операции с табличными переменными идут в памяти. Какой билет читать, прафесар?
Билеты вам еще рано читать.

Для начала заполните табличную переменную так, чтобы объем превысил доступную память и поразмышляйте над тем а как это оно работает и не падает.
Также поразмышляйте для чего нужно логирование и как оно соотносится с табличными переменными.
Ну и напоследок нагуглите себе ссылок о сравнении временных таблиц и табличных переменных, а также о мифах про табличные переменные.
24 июл 17, 14:59    [20670113]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
invm
Билеты вам еще рано читать.
---
нагуглите себе ссылок

Вот так всегда, спрашиваешь про опцию для хинта, чтобы warningа не было, а тебе отвечают, что ты массив на 8 строк не правельно определяешь... <_<
24 июл 17, 15:14    [20670187]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Шыфл
invm
Билеты вам еще рано читать.
---
нагуглите себе ссылок

Вот так всегда, спрашиваешь про опцию для хинта, чтобы warningа не было, а тебе отвечают, что ты массив на 8 строк не правельно определяешь... <_<

а на автосервисе вы не удивляетесь, когда слесарь с вас ржёт когда вы спрашиваете: "я еду и у меня колесо спустилось, я выскакиваю и качаю, подскажите как мне качать колесо на ходу"
24 июл 17, 15:23    [20670227]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
o-o
Guest
Шыфл
invm
Билеты вам еще рано читать.
---
нагуглите себе ссылок

Вот так всегда, спрашиваешь про опцию для хинта, чтобы warningа не было, а тебе отвечают, что ты массив на 8 строк не правельно определяешь... <_<

нет.
всех тут затошнило от приведенного вами мифа
автор
Операции с временными таблицами логируются,
операции с табличными переменными идут в памяти.

он наверное по 10 раз на дню цитируется и все задолбались его развеивать.
а с темы да, это хорошо сбивает,
ибо сколько же можно мифопоклонничать
24 июл 17, 15:30    [20670254]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
Шыфл
операции с табличными переменными идут в памяти.
Это в какой СУБД?
24 июл 17, 17:02    [20670601]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Гавриленко Сергей Алексеевич
Шыфл
операции с табличными переменными идут в памяти.
Это в какой СУБД?

Да мне уже с криками объяснили, что это новый, улутшеный миф...
24 июл 17, 17:24    [20670665]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Шыфл
Гавриленко Сергей Алексеевич
пропущено...
Это в какой СУБД?

Да мне уже с криками объяснили, что это новый, улутшеный миф...

т.е. прочитать что ж за проблема с табличными переменными и оптимизатором, вы всё равно не в состоянии. Лепите дальше ваши хинты
24 июл 17, 17:26    [20670667]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7758
А чем оригинальный вариант запроса был плох?
Те же 8 прогонов, но с оптимизацией, без пивота, сортировки и зажигательной пляски с хинтами.
24 июл 17, 17:33    [20670689]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
TaPaK,

дядя, а что ты мне посоветуешь, когда нужен массив на 8 записей и по нему групировать данные?Исходный запрос >100 строк, у меня <20 на выборку и простенькое преобразование. Замена @ на # даёт прирост 8%, добавление хинта даёт прирост 1000%.
Можно, конечно и без хинта, просто периоды первой таблицей поставить, но без option (FORCE ORDER) всё равно в 5 раз медленнее, чем с хинтами.
24 июл 17, 17:46    [20670739]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Владислав Колосов
А чем оригинальный вариант запроса был плох?
Те же 8 прогонов, но с оптимизацией, без пивота, сортировки и зажигательной пляски с хинтами.


1). Там 100 строк кода, который повторяется и повторяеться и повторяеся и поворяется и потворяется и повторяеится и павтаряется и снова повторяится.
2). Там 8 раз дёргается табличная функция
3). 8 параметров на даты и статичные названия столбцов
4). Он банально не правельно работал (я не стал разбираться почему, но контрольная сумма не сходилась почти на процент)
24 июл 17, 17:52    [20670762]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Шыфл,

fnBusinessResultsBasePerYear это ваше творение хоть инлайновое?
24 июл 17, 17:53    [20670769]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
TaPaK
Шыфл,
fnBusinessResultsBasePerYear это ваше творение хоть инлайновое?

Я не понимаю ваш русский, но это не моё творение, это источник данных с row-level допуском. Там внутри вызов как минимум трёх табличных функций. Просто из таблицы спрашивать низзя, вдруг юзер не нашенский
24 июл 17, 18:02    [20670806]     Ответить | Цитировать Сообщить модератору
 Re: Хинты и Excel  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
TaPaK, да, все 4 функции инлайновые
24 июл 17, 18:11    [20670837]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить