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

Откуда:
Сообщений: 4887
Всем добрый день. Есть простой запрос (с простым условием из вьюхи, во вьюху запихана оконные функции lead, lag - для того что бы понимать повысилась или понизилась цена). В этом запросе когда я номер инструмента - paper_no задаю константой выдается один план запроса. Возникла необходимость анализировать подобным запросом несколько инструментов, собственно вместо константы в предикате поставил переменную! Другой план запроса!
Понятно что оптимизатор может ошибаться со статистикой, апдейт статистики никак не решает проблему, и даже хинт параметризации не спасает. Поясните кто знает - как хинтовать запрос, что бы план был как с константой?

Текст запроса:
declare
    @d1 datetime = '2014-11-01',
    @d2 datetime = '2014-11-15 23:59:59',
    @paper1 int = 4940

select
    paper_no,
    ttime,
    avg_price,
    prev_avg_price
from v_candles
where 1=1
and paper_no = 4936
and ttime between  @d1 and @d2
order by paper_no, ttime desc

select
    paper_no,
    ttime,
    avg_price,
    prev_avg_price
from v_candles
where 1=1
and paper_no = @paper1
and ttime between  @d1 and @d2
order by paper_no, ttime desc
option (optimize for (@paper1=4936))

PS: План во вложении (оценочный-estimated).
select @@version

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

К сообщению приложен файл (не понятно почему разный план.sqlplan - 94Kb) cкачать
25 дек 14, 15:33    [17052033]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять физику процесса в параметризированном запросе  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
так для чистоты екпи ремента вот так должен выглядеть 1й запрос однако
declare
    @d1 datetime = '2014-11-01',
    @d2 datetime = '2014-11-15 23:59:59',
    @paper1 int = 4940

select
    paper_no,
    ttime,
    avg_price,
    prev_avg_price
from v_candles
where 1=1
--and paper_no = 4936
and paper_no = @paper1
and ttime between  @d1 and @d2
order by paper_no, ttime desc
25 дек 14, 15:36    [17052077]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять физику процесса в параметризированном запросе  [new]
zasandator
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 4887
Maxx,

Мне нужно первый запрос параметризировать не просев в производительности. понятно одинаковые запросы - одинаковые планы.

вопрос то примерно такой сокращенно:

declare @a int = 10

select * from t where a = 10
select * from t where a = @a option (optimize for (@a=10))

Разные планы - разная статистика (во вложенном плане можно увидеть). Почему???
25 дек 14, 15:40    [17052115]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять физику процесса в параметризированном запросе  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
1. что внутрях вьюхи
2. что из себя представляет [a_hell_info].[dbo].[candles] (поля, индексы)
25 дек 14, 16:21    [17052521]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять физику процесса в параметризированном запросе  [new]
SomewhereSomehow
Member

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

Добрый день. Это связано с ограничением сервера по проталкиванию предикатов в представления/cte/подзапросы с оконными функциями (недавно тут обсуждали проталкивание предикатов, я там обмолвился что есть исключения - вот это одно из них).
Грубо говоря, если задействуется переменная во внешнем по отношению к представлению с оконными функциями запросу, то сервер не будет проталкивать предикат внутрь и соответственно не сможет задействовать поиск.
Вот тут пару лет назад такое поведение обсуждали (в коментах к статье), можно поискать, может и на коннекте что-то есть по этому поводу, но искать лень: http://sqlblog.com/blogs/hugo_kornelis/archive/2012/05/04/the-curious-case-of-the-optimizer-that-doesn-t.aspx
В вашем примере, либо отказаться от представления и поместить условие where внутрь подзапроса, либо option(recompile).
use tempdb;
go
if object_id('t1') is not null drop table t1;
create table t1(a int not null, b int not null, c int, constraint pk_a primary key(a));
insert into t1(a,b,c) select number, number%100+1, number%50+1 from master..spt_values where type = 'p' and number between 1 and 2000;
go
delete top (19) from t1 where t1.b = 10;
create index ix_b on t1(b);
update statistics t1 with fullscan;
go
-- seek
with c as (select *, rn = row_number() over(partition by b order by a) from t1) 
select * from c where b = 10;
go
-- scan
declare @a int = 10;
with c as (select *, rn = row_number() over(partition by b order by a) from t1) 
select * from c where b = @a
option (optimize for (@a=10))
go
-- seek
declare @a int = 10;
with c as (select *, rn = row_number() over(partition by b order by a) from t1) 
select * from c where b = @a
option (recompile)
go
25 дек 14, 16:26    [17052565]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять физику процесса в параметризированном запросе  [new]
zasandator
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 4887
kDnZP
1. что внутрях вьюхи
2. что из себя представляет [a_hell_info].[dbo].[candles] (поля, индексы)

Разобрался пляяяааа! Вьюха:
ALTER view [dbo].[v_candles] as
select *
	,lead(avg_price,1,null) over (partition by paper_no order by ttime desc) prev_avg_price
from candles -- это таблица

Мои знания о том что текст вьюхи встраивается в запрос - неправильны оказались! точнее... встраивает, но как только во вьюхе встречаются скалярные функции (тем более пользовательские, а здесь встроенные). Оптимизатор нихуа не пральна оценивает статистику...
25 дек 14, 16:27    [17052570]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять физику процесса в параметризированном запросе  [new]
zasandator
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 4887
SomewhereSomehow,

Спасибо за ответ - буду знать.
25 дек 14, 16:28    [17052578]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять физику процесса в параметризированном запросе  [new]
zasandator
Member [скрыт] [заблокирован]

Откуда:
Сообщений: 4887
SomewhereSomehow,

Еще раз спасибо!
Почитал блог по ссылке - ваще разрыв шаблона )))) Как так разные планы???????? Пиппэц!
SELECT   TerritoryID,
         Name,
         SalesLastYear,
         SalesYTD,
         RANK() OVER (ORDER BY SalesLastYear) AS Rank1,
         RANK() OVER (ORDER BY SalesYTD) AS Rank2
FROM     Sales.SalesTerritory
ORDER BY SalesLastYear;

SELECT   TerritoryID,
         Name,
         SalesLastYear,
         SalesYTD,
         RANK() OVER (ORDER BY SalesYTD) AS Rank2,
         RANK() OVER (ORDER BY SalesLastYear) AS Rank1
FROM     Sales.SalesTerritory
ORDER BY SalesLastYear;
25 дек 14, 17:02    [17052815]     Ответить | Цитировать Сообщить модератору
 Re: Не могу понять физику процесса в параметризированном запросе  [new]
SomewhereSomehow
Member

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

Вот так =) Сиквел тоже люди пишут, не все учитывают =)
25 дек 14, 17:39    [17053060]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить