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

Откуда: Москва
Сообщений: 158
в запросе считается скалярное произведение документа по кластерам, параметром ограничивается множество кластеров.
declare @ParentID int =1, @minweight int =40, @minCount int =250;
with cte as
(select CD.ClusterID, count(*) DocCount from ClusterDocuments CD 
inner join Clusters C on C.id=CD.[ClusterID] 
where C.ParentID=1
GROUP BY CD.ClusterID
having count(*)>@minCount
)
select CD1.ClusterID ID1, CD2.ClusterID ID2, count(*) Shared_DocCount
from (ClusterDocuments CD1 inner join cte C1 on C1.ClusterID=CD1.ClusterID) inner join
(ClusterDocuments CD2 inner join cte C2 on C2.ClusterID=CD2.ClusterID )
on CD1.DocumentID=CD2.DocumentID and CD1.ClusterID<CD2.ClusterID
group by  CD1.ClusterID, CD2.ClusterID


К сообщению приложен файл (Быстрый.sqlplan - 71Kb) cкачать
17 июн 15, 19:50    [17784258]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
Wisky
Member

Откуда: Москва
Сообщений: 158
а вот медленный

К сообщению приложен файл (медленный.sqlplan - 42Kb) cкачать
17 июн 15, 19:51    [17784265]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
Wisky
Member

Откуда: Москва
Сообщений: 158
когда переменную @minCount заменяю значением 250 (тем же самым) работает по медленному варианту.
Он же и используется в хранимке
17 июн 15, 19:53    [17784273]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Wisky,

Для запроса с переменной сервер не может знать значение для которого потимизирует предикат > @minCount , т.к. в момент компиляции @minCount неизвестно. Поэтому, он использует догадку в 30% (11 263 * 0.3 = 3378.9). Этого количества оказывается достаточно, чтобы сервер выбрал другие типы соединений и параллельный план.

В случае, когда используется литерал 250 - его значение точно известно и оптимизатор может посмотреть в гистограмму распределения, которая ему достается от нижележащего Merge Join и далее по цепочке. Но то ли статистика неактуальная, то ли не полная, то ли соединение так искажает гистограмму, но в результате получается оценка в одну строку. Видимо, это далеко от истины. Выбираются неправильные алгоритмы соединения, кроме того, резервируется очень мало памяти на последующую сортировку и хэш соединение, которые, возможно, из-за недостатка памяти сливают данные на диск.

Рекомендую вам в первую очередь одновить все статистики на таблицах участвующих в запросе с опцией with fullscan.
17 июн 15, 21:07    [17784573]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
SomewhereSomehow,
автор
Для запроса с переменной сервер не может знать значение для которого потимизирует предикат > @minCount , т.к. в момент компиляции @minCount неизвестно.

Неужто?!
declare @minCount int =250;

автор
В случае, когда используется литерал 250 - его значение точно известно

У ТС, с его слов, как раз обратный случай:
Wisky
когда переменную @minCount заменяю значением 250 (тем же самым) работает по медленному варианту.


ЗЫ. Да, старые шаблоны, они такие...
17 июн 15, 21:28    [17784642]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
pkarklin
Неужто?!
declare @minCount int =250;


И что, это значит, что известно в момент компиляции?

pkarklin
У ТС, с его слов, как раз обратный случай:
Wisky
когда переменную @minCount заменяю значением 250 (тем же самым) работает по медленному варианту.


ЗЫ. Да, старые шаблоны, они такие...


ТС заменяет @minCount на значение 250 и работает медленно. Я и написал вероятную причину такого поведения, судя по планам. Какие еще шаблоны?
17 июн 15, 21:41    [17784716]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
SomewhereSomehow,

автор
И что, это значит, что известно в момент компиляции?

Я теряюсь в догадках, что может этому помешать.
автор
ТС заменяет @minCount на значение 250 и работает медленно. Я и написал вероятную причину такого поведения, судя по планам. Какие еще шаблоны?

"Шаблон" - это когда литерал заменяем на переменную и работает медленно. Здесь же, со слов автора, обратный случай.

Возможно, автор что-то не договаривает, ибо планы есть, а самих запросов (полностью) не видно. Это вот высказывание тоже не подтверждено скриптом:
Wisky
Он же и используется в хранимке
17 июн 15, 21:55    [17784776]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
pkarklin
автор
И что, это значит, что известно в момент компиляции?

Я теряюсь в догадках, что может этому помешать.

Видимо, реализация SQL Server? Или вы утверждаете, что ничего не может? Приведёте репро?

pkarklin
"Шаблон" - это когда литерал заменяем на переменную и работает медленно. Здесь же, со слов автора, обратный случай.

Возможно, я вас удивлю, но бывают и обратные случаи. Когда переменную заменяем на литерал и работает медленно. Я даже могу привести репро, чуть позже, если это нужно.
17 июн 15, 22:19    [17784879]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
SomewhereSomehow,

автор
Видимо, реализация SQL Server? Или вы утверждаете, что ничего не может? Приведёте репро?

Т.е. Вы сами не знаете, но требуете с меня репро? Возможно, Вы потрудитесь привести репро на это:
автор
И что, это значит, что известно в момент компиляции?

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

Не надо меня ничем удивлять. Удивлений у меня было уже предостаточно. Репро приводить не нужно. Достаточно признать, что вот это вот 17784573 было шаблонным ответом без вникания в суть проблемы.
17 июн 15, 22:33    [17784962]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Вот репро, где значение переменной неизвестно.
use opt;
go
set showplan_xml on
go
select *, (select 1) from t1 where a > 100; -- Estimated Number Of Rows = 900 from statistics, because the value is known
go
declare @a int = 100;
select *, (select 1) from t1 where a > @a; -- Estimated Number Of Rows = 300 from 30% guess, @a is unknown
go
set showplan_xml off
go

Вот документация Statistics Used by the Query Optimizer in Microsoft SQL Server 2008. Там и про догадки, и про 30% для локальной переменной. Можете поискать, где-то в документации сказано и как по гистограмме оценка считается если известно точное значение в предикате.

Теперь вы приведите репро, когда значение локальной переменной известно в момент компиляции.

Вы невнимательно прочитали мой ответ, и перепутали шаблонный ответ когда советуют заменить переменную константой или добавить option(recompile) с тем, что я написал выше. Чтобы это понять, достаточно еще раз это перечитать. Вы прочитали не то что написано, а то что хотели прочитать.
17 июн 15, 23:18    [17785187]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
SomewhereSomehow,

автор
Вот репро

Репро это то, что каждый может у себя повторить... Но я постараюсь объяснить доходчиво.

К сообщению приложен файл. Размер - 74Kb
17 июн 15, 23:38    [17785294]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
pkarklin,

А, не можете воспроизвести, конечно. Я же в этом сообщении забыл дать ссылку на скрипт тестовой БД, пожалуйста, воспроизводите http://www.queryprocessor.com/sampledbs/.

А вот обьяснить доходчиво у вас не получилось.
Как можно понять из картинки, что в случае второго запроса сервер знает значение локальной переменной @ID в момент компиляции? Вы приведите скрипт, который можно запустить.
17 июн 15, 23:58    [17785382]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
SomewhereSomehow
Как можно понять из картинки, что в случае второго запроса сервер знает значение локальной переменной @ID в момент компиляции?

Так следуя Вашим словам:
автор
Для запроса с переменной сервер не может знать значение для которого потимизирует предикат > @minCount , т.к. в момент компиляции @minCount неизвестно. Поэтому, он использует догадку в 30% (11 263 * 0.3 = 3378.9).


К сообщению приложен файл. Размер - 17Kb
18 июн 15, 00:06    [17785417]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
pkarklin,

Еще раз, как это понять из картинки?
Возможно, вы имеете ввиду, что оценка 1 строка, ну так вы же ее ограничили сверху Top 1, который задействует механизм Row Goal, который работает ортогонально вычислению селективности и ограничивает оценку сверху.

Можно вместо топ1 еще предикатов понаписать по разным колонкам, тоже не будет 30%, будет комбинация селективностей и какое-то другое число строк. Это просто доказывает, что оптимизатор может по-разному считать селективности, комбинируя условия.

И что? Это доказательство того, что сервер знает значение переменной во время компиляции?
18 июн 15, 00:26    [17785462]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
SomewhereSomehow,
автор
Возможно, вы имеете ввиду, что оценка 1 строка, ну так вы же ее ограничили сверху Top 1

TOP 1 будет применен после выполнения условий предиката, неправда ли?
автор
Можно вместо топ1 еще предикатов понаписать по разным колонкам, тоже не будет 30%, будет комбинация селективностей и какое-то другое число строк. Это просто доказывает, что оптимизатор может по-разному считать селективности, комбинируя условия.

Был приведен очень простой пример. И TOP, заметьте, был без ORDER BY, чтобы не усложнять ситуацию.
автор
И что? Это доказательство того, что сервер знает значение переменной во время компиляции?

Если бы он не знал, то вынужден был бы свалится в скан, ибо малой толики делений гистограммы недостаточно для точного попадания при таком кол-ве записей.
18 июн 15, 00:35    [17785486]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
pkarklin
TOP 1 будет применен после выполнения условий предиката, неправда ли?

Top да, а механизм Row Goal нет.

pkarklin
Если бы он не знал, то вынужден был бы свалится в скан, ибо малой толики делений гистограммы недостаточно для точного попадания при таком кол-ве записей.

Я думаю, что в dbo.Object есть кластерный индекс по id, и часто в id только положительные числа, если все так, то такой запрос должен выбирать всю или почти всю таблицу: select * from dbo.Object where id > 0
Попробуйте выполнить.

Свалился в скан? (Вы можете, конечно, сказать что да, но в таком случае, я попрошу структуру таблицы и тестовые данные, это же репро)

Честно говоря, я не пойму, зачем вы упорствуете. То, что значение локальных переменных неизвестно в время компиляции - это хорошо известный факт. Этот факт используется даже в древней статье на этом сайте по борьбе с Parameter Sniffing по 2000-му сиквелу, где советовали использовать в запросах вместо параметров переменные, как раз потому, что их значения неизвестны.
Запись declare @a int = 1, это просто укороченная форма declare @a int; set @a = 1; это совершенно ничего не говорит о том, что значение переменной известно во время компиляции.
18 июн 15, 00:50    [17785518]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
SomewhereSomehow,

автор
Я думаю, что в dbo.Object есть кластерный индекс по id, и часто в id только положительные числа, если все так, то такой запрос должен выбирать всю или почти всю таблицу: select * from dbo.Object where id > 0

Какое отношение этот запрос имеет к приведенной Вами ссылке на документацию по статистике?
автор
Попробуйте выполнить.

Увольте, я еще в здравом уме.

К сообщению приложен файл. Размер - 22Kb
18 июн 15, 00:56    [17785521]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
pkarklin
SomewhereSomehow,
Какое отношение этот запрос имеет к приведенной Вами ссылке на документацию по статистике?

Это имеет утверждение к "свалится в скан"

pkarklin
Увольте, я еще в здравом уме.

Мда. Выполнить компиляцию. План посмотреть. Что в плане?
18 июн 15, 01:00    [17785526]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
SomewhereSomehow
Честно говоря, я не пойму, зачем вы упорствуете. То, что значение локальных переменных неизвестно в время компиляции - это хорошо известный факт. Этот факт используется даже в древней статье на этом сайте по борьбе с Parameter Sniffing по 2000-му сиквелу, где советовали использовать в запросах вместо параметров переменные, как раз потому, что их значения неизвестны.

С чего Вы взяли, что я в чём то упорствую?! Я всего лишь пытаюсь показать, что "древние статьи" не всегда употребимы ни к месту.
SomewhereSomehow
Запись declare @a int = 1, это просто укороченная форма declare @a int; set @a = 1

Совершенно согласен! Синтаксический сахар.
SomewhereSomehow
это совершенно ничего не говорит о том, что значение переменной известно во время компиляции.

Мы начинаем всё с начала?
18 июн 15, 01:02    [17785528]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
SomewhereSomehow
Мда. Выполнить компиляцию. План посмотреть. Что в плане?

В ожидаемом?
18 июн 15, 01:19    [17785536]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
pkarklin
Мы начинаем всё с начала?

А мы пока что ни на чем не закончили, чтобы начинать сначала.

Пока что есть.

С моей стороны:
- репро (со ссылкой на тестовую бд), можете запустить посмотреть
- документация, которая объясняет репро
- упомянул статью с этого сайта (ссылку тут в форуме на нее миллион раз давали, ищется по Parameter Sniffing)

С вашей стороны:
- картинки
- домыслы о том, как работает Top (хотя топа у автора вообще не было) домыслы о том, что должно сваливаться в скан (правда, даже на мой вопрос предыдущий вопрос про план не ответили - свалилось там в скан или нет по такому предикату?)

Будете продолжать настаивать на вот этом:
pkarklin
Неужто?!
declare @minCount int =250;

И на этом:
pkarklin
Достаточно признать, что вот это вот 17784573 было шаблонным ответом без вникания в суть проблемы.

?
18 июн 15, 01:20    [17785538]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
SomewhereSomehow,

автор
Будете продолжать настаивать на вот этом

О, да. Вы перепутали ситуации!
18 июн 15, 01:23    [17785539]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
invm
Member

Откуда: Москва
Сообщений: 9400
pkarklin
автор
И что, это значит, что известно в момент компиляции?

Я теряюсь в догадках, что может этому помешать.
Возьмем простенький скрипт:
use tempdb;
go

create table dbo.t (id int identity primary key, v int);
insert into dbo.t values (1), (2), (3), (1), (1), (1), (1), (1);
create statistics ST_t__v on dbo.t (v);
go

declare @v int = 1;

set statistics xml on;

select * from dbo.t where v = 1;
select * from dbo.t where v = @v;

set statistics xml off;
go

drop table dbo.t;
go
Если, как вы утверждаете, значение переменной @v известно во время компиляции, у обоих запросов в планах должно совпасть оценочное число возвращаемых строк.
Однако, не совпадает.
18 июн 15, 01:27    [17785542]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
pkarklin
В ожидаемом?

Да.
Можете и актуальный посмотреть. Вы же уже запустили скан по этой таблице, чтобы показать картинку:
Картинка с другого сайта.
=)
А тут вдруг сказали что таблица большая и скан по ней вы запускать не будете. =)

Тема ТС:
автор
почему то с указанным значением запрос медленнее чем с переменной того же значения


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

Теперь что я писал
SomewhereSomehow
/...cut.../
Для запроса с переменной /...cut.../ и параллельный план.

В случае, когда используется литерал 250 /...cut.../ получается оценка в одну строку. /...cut.../ Выбираются неправильные алгоритмы соединения, кроме того, резервируется очень мало памяти на последующую сортировку и хэш соединение, которые, возможно, из-за недостатка памяти сливают данные на диск.
/...cut.../

Для переменной параллельный план и нормальные оценки. Быстро.
Для литерала оценка в одну строку, неправильные типы соединения, нехватка памяти - медленно.

Для вас это:
pkarklin
О, да. Вы перепутали ситуации!

?
18 июн 15, 01:36    [17785554]     Ответить | Цитировать Сообщить модератору
 Re: почему то с указанным значением запрос медленнее чем с переменной того же значения  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
SomewhereSomehow,

автор
Да. Можете и актуальный посмотреть. Вы же уже запустили скан по этой таблице, чтобы показать картинку.

Для этого мне не понадобилось словить сотни гигов на клиенте, неправда ли?
автор
Теперь что я писал


Без кутов...
автор
Для запроса с переменной сервер не может знать значение для которого потимизирует предикат > @minCount , т.к. в момент компиляции @minCount неизвестно. Поэтому, он использует догадку в 30% (11 263 * 0.3 = 3378.9). Этого количества оказывается достаточно, чтобы сервер выбрал другие типы соединений и параллельный план.

В случае, когда используется литерал 250 - его значение точно известно и оптимизатор может посмотреть в гистограмму распределения, которая ему достается от нижележащего Merge Join и далее по цепочке. Но то ли статистика неактуальная, то ли не полная, то ли соединение так искажает гистограмму, но в результате получается оценка в одну строку. Видимо, это далеко от истины. Выбираются неправильные алгоритмы соединения, кроме того, резервируется очень мало памяти на последующую сортировку и хэш соединение, которые, возможно, из-за недостатка памяти сливают данные на диск.


Браво!!! Не зная значения, для которого он оптимизирует, был выбран верный план. Для известного значения был выбран не верный план!
18 июн 15, 01:48    [17785561]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить