Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 параметризованная кака. что можно сделать?  [new]
beg_inner
Guest
имеется отчет с параметрами.
если в запрос вместо параметров начала(@data_fasi_da) и конца(@data_fasi_a) периода поставить конктерные значения,
выполняется 3 секунды, если оставить в виде параметров,
при тех же значениях 5 минут думает.
как ему можно навязать план с подставленными значениями?

declare @mandante int
set @mandante = 902

declare @portafoglio int
set @portafoglio = -999

declare @data_fasi_da char(8)
set @data_fasi_da = '20120701' 

declare @data_fasi_a char(8)
set @data_fasi_a = '20120701'

select u.CODUTENTE,
       u.NOMEUTENTE,
       p.NUM_PRAT,
       bb.BB20_DES_BANCA,
       YEAR(f.DATA) AS ANNO,
       YEAR(f.DATA)*100 + MONTH (f.DATA) AS ANNO_MESE,
       pro.CODICEPROC,
       mpr.DESCRPROCE AS PROCEDURA,
       f.CODICEFASE,
       mfa.DESCRFASE AS FASE,
       f.SPESE AS SPESE,
       f.SPESENONSO AS ANTICIPAZIONI,
       f.DIRITTI AS DIRITTI,
       f.ONORARI AS ONORARI,
       (f.SPESE+f.SPESENONSO+f.ONORARI+f.DIRITTI) AS 'S.A.D.O.'

  FROM a.LEGFASI f
       JOIN a.LEGPROCE pro ON f.CODICEPROC = pro.CODICEPROC
       JOIN LEGMODFA mfa ON f.CODFASE = mfa.CODFASE
       JOIN LEGMODPR mpr ON  mpr.CODPROCEDU = pro.CODPROCEDU 
       JOIN a.legprati p ON pro.NUM_PRAT = p.NUM_PRAT
       JOIN BAD0BANCHE bb ON bb.BB20_IDBANCA = p.IDBANCA
       JOIN LEGUTENT u ON u.CODUTENTE = pro.CODRESPONS
       JOIN PROFILI pr ON pr.CODPROFILO = u.CODPROFILO
 WHERE pr.LIVELLO = 27
   AND (f.SPESE <> 0
        OR f.DIRITTI <> 0
        OR f.ONORARI <> 0
        OR f.SPESENONSO <> 0)
   and (p.IDBANCA = @mandante or -999 = @mandante)
   and (p.COD_PORTAFOGLIO = @portafoglio or -999 = @portafoglio)

   and f.DATA between @data_fasi_da and @data_fasi_a

   AND f.DATA >= ISNULL(pro.DATA_MANDATO_ORIG, p.DATARICMAN)
   AND f.FATTURATA = 0
   AND pro.PROC_INVALIDATA = 0
   AND pro.CODPROCEDU >= 10
   AND pro.CODPROCEDU IN (SELECT CODPROCEDU
                                FROM LEGMODPR
                                WHERE TIPOPROC <> 200)
   AND NOT EXISTS (SELECT fn.CODICEFASE
                     FROM a.LEGFASINOTULA fn
                          JOIN a.LEGNOTULE n ON fn.CODNOTULA = n.CODNOTULA
                    WHERE f.CODICEFASE = fn.CODICEFASE
                      AND n.DATA_ANNULLAMENTO IS NULL )


планы в виде xml превышают 150Кб, не знаю, как прицепить
28 авг 12, 13:10    [13075422]     Ответить | Цитировать Сообщить модератору
 Re: параметризованная кака. что можно сделать?  [new]
SomewhereSomehow
Member

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

Можно попробовать добавить option(recompile) в конец запроса.
28 авг 12, 13:19    [13075524]     Ответить | Цитировать Сообщить модератору
 Re: параметризованная кака. что можно сделать?  [new]
beg_inner
Guest
SomewhereSomehow,

спасибо, помогло.
а скажите пожалуйста, почему помогло.
я вроде помню для процедур даже специально вводят фиктивные параметры,
куда присваивают переданные в процедуру параметры,
чтоб в самой процедуре сервер не мог бы понять,
какие будут переданы параметры фильтрации в запрос.
и я так поняла, что он как видит переданные параметры, что-то пересчитывает.
а в моем случае же он явно видит, что передано.
почему тогда не выбирается план с поиском, если видит же, что начало с концом совпадают и строк мало?
28 авг 12, 13:27    [13075615]     Ответить | Цитировать Сообщить модератору
 Re: параметризованная кака. что можно сделать?  [new]
SomewhereSomehow
Member

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

фиктивные параметры (кстати это старая техника, сейчас можно просто optimize for unknown) вводят для того, чтобы план не строился и кэшировался для каких-то определенных значений, а вместо этого строился исходя из того, что значения неизвестны.
Для неизвестных значений часто используются догадки, которые могут быть неверны, т.к. собственно догадки. По этому, может быть выбран неудачный план.
Наилучшая ситуация, когда в запросе все известно. Но чтобы было известно, необходимо, чтобы сервер просмотрел и перекомпилирвоал запрос во время выполнения. Как следствие, это накладные расходы на повторное построение плана. Однако, если запрос вызывается с невысокой частотой, то это не так уж и страшно, а время на компиляцию и поиск хорошего плана может нивелироваться временем выполнения.
28 авг 12, 13:39    [13075700]     Ответить | Цитировать Сообщить модератору
 Re: параметризованная кака. что можно сделать?  [new]
Читатель неместный
Guest
А можно ли почистить "кеш планов" частично?
Или все планы одного запроса.
Для случая когда нельзя поставить option(recompile) ...из-за мегакомплекса, который в конец запроса чтото ещё пытается впихнуть.
28 авг 12, 13:53    [13075817]     Ответить | Цитировать Сообщить модератору
 Re: параметризованная кака. что можно сделать?  [new]
beg_inner
Guest
SomewhereSomehow
Наилучшая ситуация, когда в запросе все известно. Но чтобы было известно, необходимо, чтобы сервер просмотрел и перекомпилирвоал запрос во время выполнения.


это понятно.
но я никак не пойму, если даже мой запрос закешировался, почему с плохим планом-то?
я же его В САМЫЙ ПЕРВЫЙ РАЗ запустила с параметрами, где даты начала и конца совпадали.
такое впечатление, что уже в первый раз он на параметры и не посмотрел.
в первый же раз параметры были известны (одинаковые даты), откуда плохой план?

вот какая разница между ситуациями:
1) в кеше плана нет, запускаю с одинаковыми параметрами -> плохой план
2) неважно что в кеше, запускаю с теми же одинаковыми параметрами, но с option(recompile) -> план хороший

вернее, я-то такое наблюдала:
1) запускаю в 1-ый раз с одинаковыми параметрами -> плохой план
2) запускаю с забитыми одинаковыми те же самыми датами -> хороший план
28 авг 12, 14:03    [13075953]     Ответить | Цитировать Сообщить модератору
 Re: параметризованная кака. что можно сделать?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Читатель неместный,

dbcc freeproccache (plan_handle|sql_handle|pool_name). Там же написано, откуда брать все хендлы. Так что в принципе можно.
Но есть способ получше. Вы можете использовать plan guides. Там, в том числе, можно указывать и опцию recompile, и оно даже работает. Имхо, это более правильный способ.
28 авг 12, 14:19    [13076108]     Ответить | Цитировать Сообщить модератору
 Re: параметризованная кака. что можно сделать?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
beg_inner
это понятно.
но я никак не пойму, если даже мой запрос закешировался, почему с плохим планом-то?
я же его В САМЫЙ ПЕРВЫЙ РАЗ запустила с параметрами, где даты начала и конца совпадали.
такое впечатление, что уже в первый раз он на параметры и не посмотрел.
в первый же раз параметры были известны (одинаковые даты), откуда плохой план?

вот какая разница между ситуациями:
1) в кеше плана нет, запускаю с одинаковыми параметрами -> плохой план
2) неважно что в кеше, запускаю с теми же одинаковыми параметрами, но с option(recompile) -> план хороший

вернее, я-то такое наблюдала:
1) запускаю в 1-ый раз с одинаковыми параметрами -> плохой план
2) запускаю с забитыми одинаковыми те же самыми датами -> хороший план

Честно говоря не совсем понял вопрос. Смотря что и как вы запускали.
Могут быть например такие случаи.
1) запрос внутри процедуры, в запрос параметры - план кэшируется со значениями параметров при первом вызове.
2) запрос внутри процедуры, в запросе параметры, заменены на локальные переменные - план кэшируется, как если бы оптимизатор ничего не знал про значения параметров.
3) запрос выдран из процедуры, параметры заменены на переменные, запрос выполняется отдельно вне процедуры - план кэшируется, как если бы оптимизатор ничего не знал про значения переменных (при этом сравнивать его с п.1 и 2 м.б. некорректно).
Последующие исполнения, с измененными значениями, могут быть как быстрыми так и нет, в зависимости от того, адекватный ли план получился на предыдущем этапе или нет.
Если мы добавляем option(recompile), то во всех случаях, запрос не кэшируется, а значения параметров/переменных заменяются на что-то вроде runtime constant.
28 авг 12, 14:39    [13076306]     Ответить | Цитировать Сообщить модератору
 Re: параметризованная кака. что можно сделать?  [new]
beg_inner
Guest
нет, запрос не выдран из процедуры.
он выдран из отчета SSRS,
т.е. посылается на сервер как есть.
и поэтому мне непонятно, почему он сразу параметры не подставляет как "что-то вроде runtime constant".

не понимаю, какая разница, описала я вначале переменные и там задала значения,
или я их вбила константами без участия переменных.
если это в обоих случаях заранее известные значения
28 авг 12, 14:46    [13076369]     Ответить | Цитировать Сообщить модератору
 Re: параметризованная кака. что можно сделать?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
beg_inner
не понимаю, какая разница, описала я вначале переменные и там задала значения,
или я их вбила константами без участия переменных.
если это в обоих случаях заранее известные значения

Есть такое объяснение. Если в запросе локальная переменная, то нет гарантии что вы ее не поменяете до того как она будет использована в запросе. Даже точнее, весьма вероятно, что вы ее заполните во время выполнения, т.е. вы можете где-то до запроса написать set @a = (select top(1) a from mytable) и пока сервер в режиме выполнения не дойдет до этой строчки, он не узнает, что реально лежит в переменной @a. А ведь сервер, не выполняет ваш запрос во время построения плана. Он сначала строит план, а потом начинает выполнение. Так что он никак не может узнать, пока не начнет выполнять, менялось ли что-то. Если вы знаете что переменная не меняется - используйте константу. Если переменная меняется вне запроса, то оформите как процедуру и переменную сделайте параметром. Но опять-таки, если от параметра зависит то, как эффективнее выполнять запрос, вы наткнетесь на случай, когда прослушивание параметров вредит и все равно придется делать рекомпиляцию...
Решением могла бы стать какая-нибудь условная пере компиляция. Но как мне объяснил один из участников команды разработки оптимизатора, задача суметь правильно определить эту границу.
28 авг 12, 15:38    [13076943]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить