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

Откуда: большая деревня
Сообщений: 266
Есть запрос, выполняется в менеджмент студии около 4 сек (SQL 2005).
Тот же запрос, обернутый в процедуру, выполняется в той же студии больше минуты. Включение/отключение опции recompile не влияет на скорость. Исходные данные одинаковые (в том числе при первом запуске процедуры)
Тут читал, пытался просматривать планы выполнения. Вижу, что планы разные - почему?
В быстром плане основная стоимость ложится на операцию clustered index seek, а в плохом плане основная стоимость приходится на Hash Match (полное соединение).

Какие варианты решения проблемы?
Планы во вложении.

К сообщению приложен файл (Plan.rar - 67Kb) cкачать
28 апр 12, 13:00    [12484880]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
aleks2
Guest
1. Нибось запрос с параметрами?
2. where/join кляузы тож написаны шоб сервер замучить.

PS. Зачем нужон ваш кривой план? Запрос покажите.
28 апр 12, 13:16    [12484984]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
может быть во втором случае включить option(loop join) ?
28 апр 12, 13:35    [12485101]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Выложу только часть запроса. Код выполняется менее 1 сек, в процедуре 20 сек. Проблемным является вторая часть, в которой формируется таблица db_Talking_period. Её роль - корректное вычисление времени на границах периода.
В хорошем плане идет параллелизм, в плохом этого нет.

автор
может быть во втором случае включить option(loop join)

понять бы ещё для какого соединения...

К сообщению приложен файл (talking.sql - 3Kb) cкачать
28 апр 12, 14:33    [12485532]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
virtuOS,
автор
В быстром плане основная стоимость ложится на операцию clustered index seek, а в плохом плане основная стоимость приходится на Hash Match (полное соединение).

Тут походу проблема то не в стоимости в "попугаях" команд,а с io.Там с индекса IX_Cal_history_date_cals то ~115000,то ~176000 строк выбирается и считается для них выражения,а потом тока обрезается набор.
Вообще конечно странно,что план который ожидает ~3900000 записей(получает 132) с параллелизмом работает,лучше чем,который ожидает 4(получает 132) и без параллелизма.
28 апр 12, 14:44    [12485625]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
я думаю надо в процедуре копировать входные параметры в параметры обьявленные локально и их использовать в запросе. либо использовать OPTION (OPTIMIZE FOR UNKNOWN).
ЗЫ опции (OPTION) можно обьявлять для всего запроса в самом его конце
28 апр 12, 15:11    [12485827]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Мистер Хенки

Я правильно понял, что в теле процедуры попробовать написать:
DECLARE @Date_Call_Start datetime
DECLARE @Date_Call_End datetime

SET @Date_Call_Start = 'Входной параметр №1'
SET @Date_Call_End = 'Входной параметр №1'
28 апр 12, 15:17    [12485858]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
virtuOS
Мистер Хенки

Я правильно понял, что в теле процедуры попробовать написать:
DECLARE @Date_Call_Start datetime
DECLARE @Date_Call_End datetime

SET @Date_Call_Start = 'Входной параметр №1'
SET @Date_Call_End = 'Входной параметр №1'

ну как то так, да. Только я смотрю @Date_Call_Start = @Date_Call_End
28 апр 12, 15:19    [12485871]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Действительно, стало быстрее :)
28 апр 12, 15:20    [12485888]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
virtuOS,
virtuOS
Исходные данные одинаковые (в том числе при первом запуске процедуры)

virtuOS
Включение/отключение опции recompile не влияет на скорость.

Мистер Хенки

DECLARE @Date_Call_Start datetime
DECLARE @Date_Call_End datetime

SET @Date_Call_Start = 'Входной параметр №1'
SET @Date_Call_End = 'Входной параметр №1'


virtuOS
Действительно, стало быстрее :)

Если значения параметров не менялись,то это как-то странно ...
или я что-то не понимаю в Parameter Sniffing ?
28 апр 12, 15:37    [12486002]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Еще было бы неплохо сделать
update statistics CALL_HISTORY with fullscan
28 апр 12, 15:57    [12486142]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
параметры в процессе не меняются.

А статистику зачем обновлять? Это взаимосвязано? Я в этом не понимаю.
28 апр 12, 15:58    [12486145]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
virtuOS
параметры в процессе не меняются.

А статистику зачем обновлять? Это взаимосвязано? Я в этом не понимаю.

судя по планам оптимизатор ошибался в предполагаемом количестве строк. Можно попробовать с обновленной статистикой без того способа с подменой параметров, который я предложил но с with recompile - тогда, имхо, тоже должно работать правильно. Вообще обновление статистики это только на пользу оптимизатору
28 апр 12, 16:14    [12486231]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
SomewhereSomehow
Member

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

Для начала, плз, приведите одинаковые запросы с планом.
А то вот что вы выложили, запросы отличаются у вас.
Картинка с другого сайта.

Конечно я не знаю, может все поля у вас и так перечислены во втором плане и все условия where совпадают, но хотелось бы быть уверенным. Так что, выкладывайте. После этого, будем думать, что у вас предотвращает параллельный план в процедуре. Может вы там скалярку где-то запрятали или еще что...
28 апр 12, 17:42    [12486430]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
SomewhereSomehow
Member

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

Ну и оценки конечно просто жесть. Короче, нужны два полных действительных плана. В которых будет два полных запроса.
28 апр 12, 17:47    [12486443]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
SomewhereSomehow

Похоже выложил не тот файл в первом сообщении, запутался в экспериментах. Точно могу сказать, что отличие состоит только в первой части запроса (которая с with). Вместо * указал конкретные столбцы чтобы уменьшить объем данных. На результат это повлияло, но очень слабо, в пределах погрешности кеширования.
А вот переопределение входных параметров по совету Мистер Хенки вместе с однократной опцией recompile реально помогло снизить время выполнения, даже и не знаю почему. Входные параметры в процедуре точно не меняются.

Запрос действительно жсть, т.к. получен многократным join различных выборок из одной таблицы. Можно будет сократить число количество просмотров начальной базы путем объединения некоторых частей кода, но это пойдет в ущерб читабельности и будет сложнее в дальнейшем модифицировать (в том числе возможно другим людям).

Также рассматривал вариант создания временной таблицы, но инсерт в неё от 30 до 500 тыс. строк (а иногда потребуется и около 1 млн строк) выполняется совсем не быстро.

Сейчас уже ушел с работы, поэтому продолжение будет только после праздников.
28 апр 12, 19:03    [12486570]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
SomewhereSomehow
Member

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

Мне так всеравно что после праздников, если честно =)

Насчет замены параметров переменными при включенной опции перекомпиляции - это все предельно странно.
Если принять во внимание только это и то что у вас параллельный план - то возможно что есть какие-то напряги с памятью на каждый поток, это хорошо объяснят, бывшый сотрудник, а ныне консультант по производительности Ramesh Meyyappan. Если я не ошибаюсь, второй веб каст. Но мне, почему-то кажется, что проблема здесь более тривиальна. Нужны действительно два одинаковых запроса и планы, действительные и полные, с запросами целиком. Так же нужно попробовать выполнить процедуру из приложения и нет. И если время отличатся, то опять планы, но уже двух выполнений процедур.
А еще в любом случае лучше оптимизировать логику, чем реализацию, подумайте над этим.
28 апр 12, 23:56    [12487394]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
По-моему все предельно понятно.
Статистика устрела, пересчитана с недостаточным сэмплом или просто очень кривая. Как следствие:

1. При выполнении запроса (без процедуры), значения параметров неизвестны, поэтому, пальцем в небо сервер предполагает что из таблицы CALL_HISTORY будет выбрана приличная часть данных, а именно 458 тысяч строк, ну а дальше от этого пляшет весь план: параллелизм, hash/merge joins и т.д.
Логично предположить, что в этом варианте ТС не включал OPTION(RECOMPILE), ибо и так все быстро работает.

2. При запуске процедуры, из-за сниффинга значения параметров известны, и так как диапазон всего из одного дня ('2012-04-28 00:00:00.000' - '2012-04-27 00:00:00.000') и каких то проблем со статистикой получаем кучу эстимейтов в одну строку, ничтожную стоимость всего плана и соответствующую оптимизацию в виде NESTED LOOPS и т.д.
Добавление OPTION(RECOMPILE) в этом случае ничего не меняет, потому как что с опцией что без, сервер все равно знает значения параметров и пытается под них оптимизировать, что приводит к не очень хорошему результату.

Варианта 2: крутить статистику или использовать локальные переменные, как уже было предложено.
Ну или в корне пересмотреть сам запрос.
29 апр 12, 07:05    [12487749]     Ответить | Цитировать Сообщить модератору
 Re: Процедура медленно выполняется  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Mind
По-моему все предельно понятно.
Статистика устрела, пересчитана с недостаточным сэмплом или просто очень кривая. Как следствие:

1. При выполнении запроса (без процедуры), значения параметров неизвестны, поэтому, пальцем в небо сервер предполагает что из таблицы CALL_HISTORY будет выбрана приличная часть данных, а именно 458 тысяч строк, ну а дальше от этого пляшет весь план: параллелизм, hash/merge joins и т.д.
Логично предположить, что в этом варианте ТС не включал OPTION(RECOMPILE), ибо и так все быстро работает.

2. При запуске процедуры, из-за сниффинга значения параметров известны, и так как диапазон всего из одного дня ('2012-04-28 00:00:00.000' - '2012-04-27 00:00:00.000') и каких то проблем со статистикой получаем кучу эстимейтов в одну строку, ничтожную стоимость всего плана и соответствующую оптимизацию в виде NESTED LOOPS и т.д.
Добавление OPTION(RECOMPILE) в этом случае ничего не меняет, потому как что с опцией что без, сервер все равно знает значения параметров и пытается под них оптимизировать, что приводит к не очень хорошему результату.

Варианта 2: крутить статистику или использовать локальные переменные, как уже было предложено.
Ну или в корне пересмотреть сам запрос.

да, примерно так я и думал, когда помогло использование локальных переменных. Если автор таки обновит статистику и попробует выполнить старый вариант процедуры, то должно нормально отработать и наше предположение верно.
2 май 12, 10:38    [12494362]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить