Скорость работы хранимых процедур значительно ниже запросов

← →
Malign (2005-08-30 11:27) [0]

Добрый день.

Всю жизнь был уверен что скорость выполнения хранимых процедуры меньше чем запроса с клиента, но недавно при сравнении скорости выяснилось, что ХП работает в 5-8 раз медленней.

Выполняемый запрос сравнительно большой (около 100 строк). При исполнении запроса на киенте на его исполнение уходит 20-40 секунд, при вызове процедуры (текст тотже) 3-5 минут.

Тестировал на 2-х различных серверах (результат идентичен).

При этом:
-оптимизация запросов включена,
-план выполнения запросов идентичен,
-при включении статистики видно, что количество "обращений" к ресурсам у ХП в несколько СОТЕН тысяч больше чем у запроса.

Вопрос, как заставить процедуру работать с нормальной скоростью??? (требуется сохранить открытый интерфейс(а не перетаскивать все на клиенты))

← →
Nikolay M. © (2005-08-30 11:46) [1]

1. Перекомпилить процедуру, сбросить кэш плана выполнения ХП.
2. Расставить в ХП хинты, индексы, если используются #-таблицы - там свои заморочки.
3. Привести текст ХП.

← →
Ega23 © (2005-08-30 11:56) [2]

А при многократном обращении?

← →
АлексейК (2005-08-30 12:13) [3]

Всю жизнь был уверен что скорость выполнения хранимых процедуры меньше чем запроса с клиента, но недавно при сравнении скорости выяснилось, что ХП работает в 5-8 раз медленней.

Выполняемый запрос сравнительно большой (около 100 строк). При исполнении запроса на киенте на его исполнение уходит 20-40 секунд, при вызове процедуры (текст тотже) 3-5 минут.

Ничего подобного ни разу не встречал, и по логике такого быть не должно, смысла в хранимых процедурах тогды бы не было. Для интереса выполнил пробный тест, набодобие:
select .... from .... where .... go create procedure test as select .... from .... where .... go exec test
Даже в этом случае, учитывая время компиляции процедуры, разницы во времени выполнения запросов не обнаружил. У вас же она отличатеся в разы, интересно было бы посмотреть на код этой самой процедуры. Кстати а название процедуры не начинается с dt_

← →
Nikolay M. © (2005-08-30 12:53) [4]

> Кстати а название процедуры не начинается с dt_

Может, с "sp_"?

> Ничего подобного ни разу не встречал

Мало с ms sql работали, видимо.

> по логике такого быть не должно

У сервера своя логика и она утверждает, что такое очень даже может быть.

> смысла в хранимых процедурах тогды бы не было

Назначение ХП не только в том, чтобы быть обертками простых запросов.

> Для интереса выполнил пробный тест

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

← →
Malign (2005-08-30 13:42) [5]

>1. Перекомпилить процедуру, сбросить кэш плана выполнения ХП.

Перекомпиливал, сбрасывал, переиминовывал (как только не извращался)

>2. Расставить в ХП хинты, индексы, если используются #-таблицы -> там свои заморочки.
Темповые там не используются.

>3. Привести текст ХП.

Вот основная часть процедуры (она чуть пошустрей работает но тенденция таже)

+++++++++++++++++++++++++++++++++++++++++++=

CREATE PROCEDURE Select_xxx
@BEG DATETIME,
@END DATETIME,
@DEPART INT
AS

SET DATEFORMAT dmy

SELECT S.SmenaDate as "DATE", E.Etalon_ID as PROP_ID , E.id as PROP, E.TABEL, E.FIO, E.Dolgnost , MAX(S.DateOfPasses) as "DateOfPasses", E.DEPART as "DEPART" , D.Name AS "DEPARTNAME",
(ltrim(str(datepart(hh, ((T.End_ -MAX(S.DateOfPasses))-
isnull((select top 1 (T.End_Din-T.Beg_Din)
FROM SmenParams SE
WHERE (SE.TA_ID=T.[ID]) AND (SE.TypeAccount = 1)
and (MAX(S.DateOfPasses)<T.Beg_Din) ), 0)-
isnull((select top 1 (T.End_Din-MAX(S.DateOfPasses))
FROM SmenParams SE
WHERE (SE.TA_ID=T.[ID]) AND (SE.TypeAccount = 1)
and (MAX(S.DateOfPasses)>T.Beg_Din) and (MAX(S.DateOfPasses)<T.End_Din)), 0)
) )))+":" +
(case ltrim(str(datepart(mi, ((T.End_ -MAX(S.DateOfPasses))-isnull((select top 1 (T.End_Din-T.Beg_Din)
FROM SmenParams SE
WHERE (SE.TA_ID=T.[ID]) AND (SE.TypeAccount = 1)
and (MAX(S.DateOfPasses)<T.Beg_Din) ), 0)-
isnull((select top 1 (T.End_Din-MAX(S.DateOfPasses))
FROM SmenParams SE
WHERE (SE.TA_ID=T.[ID]) AND (SE.TypeAccount = 1)
and (MAX(S.DateOfPasses)>T.Beg_Din) and (MAX(S.DateOfPasses)<T.End_Din)), 0)
) )))
when 0 then "00"
when 1 then "01"
when 2 then "02"
when 3 then "03"
when 4 then "04"
when 5 then "05"
when 6 then "06"
when 7 then "07"
when 8 then "08"
when 9 then "09"
else ltrim(str(datepart(mi, ((T.End_ -MAX(S.DateOfPasses))-isnull((select top 1 (T.End_Din-T.Beg_Din)
FROM SmenParams SE
WHERE (SE.TA_ID=T.[ID]) AND (SE.TypeAccount = 1) -- Доработанно для исключения из времени недоработки обеда
and (MAX(S.DateOfPasses)<T.Beg_Din) ), 0)-
isnull((select top 1 (T.End_Din-MAX(S.DateOfPasses))
FROM SmenParams SE
WHERE (SE.TA_ID=T.[ID]) AND (SE.TypeAccount = 1)
and (MAX(S.DateOfPasses)>T.Beg_Din) and (MAX(S.DateOfPasses)<T.End_Din)), 0)
) ))) end)) as "ned"
FROM TabAccount T INNER JOIN
SmenParams S ON T.ID = S.TA_ID INNER JOIN
etalon E ON T.PROP_ID = E.ETALON_ID INNER JOIN
depart D ON E.depart = D.ID
WHERE (S.TypeAccount = 1) AND (S.Slug_Flag = 0) AND (S.SmenaDate >= @BEG) AND (S.SmenaDate <= @END)
and (E.free<>1) and ((E.DEPART=@DEPART) OR (@DEPART<=0))
AND ((SELECT COUNT(*) FROM DemandsForTabAccount DFT WHERE (DFT.PROP_ID=E.ETALON_ID) AND (S.SmenaDate>=DFT.BEG_)
AND (S.SmenaDate<=DFT.END_))=0 )
GROUP BY E.DEPART, S.SmenaDate,T.[YEAR], T.MES, T.[DATE], E.id, E.Etalon_ID, E.fio, T.End_ , T.Begin_, D.Name , T.ID, T.Beg_Din, T.End_Din, E.Dolgnost, E.TABEL
-- для сортировки по продазделению пренес E.DEPART на перую позицию
HAVING ((SELECT COUNT(*) FROM SmenParams SE WHERE (SE.TA_ID=T.[ID]) AND (SE.TypeAccount = 0)
AND (MAX(S.DateOfPasses) <=(SE.DateOfPasses)) AND (T.End_ >SE.DateOfPasses) ) =0)
AND (T.End_ >max(S.DateOfPasses)) AND (T.Begin_ <= max(S.DateOfPasses))
and (((datepart(ss,T.End_ -MAX(S.DateOfPasses)))<59) -- отсечение времени
and ((datepart(mi,T.End_ -MAX(S.DateOfPasses)))<>0) or ((datepart(hh,T.End_ -MAX(S.DateOfPasses)))<>0))
AND (datepart(day, (T.end_-T.begin_))<=1) -- Для исключения круглосуточных смен
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

← →
АлексейК (2005-08-30 13:50) [6]

Nikolay M. © (30.08.05 12:53) [4]

Может, с "sp_"?
Верно, ошибся с sp.

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

> смысла в хранимых процедурах тогды бы не было

Назначение ХП не только в том, чтобы быть обертками простых запросов.

Однако, если бы они уменьшали бы производительность в разы, то я бы обошолся и без них.

← →
Malign (2005-08-30 13:50) [7]

Процедура расчитывает отработанные временные показатели и имеет дело с большими объемами данных.

← →
Malign (2005-08-30 13:54) [8]

>А при многократном обращении?

При многократном обращении, естественно быстрее, но разница в порядке "отсавания ХП" остается.

← →
Nikolay M. © (2005-08-30 13:58) [9]

Процедура кушает на вход параметры, которые используются в WHERE - т.е. уже нельзя говорить о том, что ХП является оберткой простого селекта.
Поскольку при построении плана выполнения ХП конкретные значения параметров неизвестны, сервер будет исходить из того, что значения могут быть любыми и этот план, скорее всего, будет выбран не оптимальным, а иногда даже может быть выбран Table scan вместо банального Index seek. При построении же плана отдельного запроса (не в ХП), когда параметры известны, сервер будет использовать статистику. Попробуй:
1. При создании указать WITH RECOMPILE
2. Ради эксперимента вместо параметров подставить конкретные значения.
3. Расставить хинтами необходимые индексы.

ПС
Выглядит, конечно, страшновато. А обязательно это делать одним запросом?

← →
Nikolay M. © (2005-08-30 14:35) [10]

> АлексейК (30.08.05 13:50) [6]
> Может и мало, но все же не встречался, что бы один и тот
> же скрипт отдельно и завернутый в процедуру отличался по
> времени выполнения в разы. А поскольку вы утверждаете, что
> такое явление вполне нормально, хотелось узнать в каком
> случае это будет происходить.

Самый распространенный пример: устаревание и ее последующее обновление статистики влечет за собой необходимость корректировки плана запроса, а поскольку у ХП план остается одним и тем же, со временем он теряет свою актуальность.
Еще одна обычная ситуация (имхо, как раз как у автора вопроса): неоптимальный план выполнения ХП ввиду неопределенности значений подаваемых на вход параметров.

← →
Malign (2005-08-30 15:29) [11]

> 1. При создании указать WITH RECOMPILE
> 2. Ради эксперимента вместо параметров подставить конкретные
> значения.
> 3. Расставить хинтами необходимые индексы.

1.Рекопайл не помог.

2.Действительно с конкретными значениями все летает :-)

3.Поставил пару хинтов и процедура стала значительно быстрее (!!)
и практически сравнялась с запросом :-)
(хотя при простановке индексов хинтами в запросе он стал немого тормазнутей (при вводе не тех индексов))

Вообщем все заработало приемлимо, пойду еще погоняю процедурку с другими индексами.

Да с входными параметрами весело получается, теперь придется все оптимизировать. А запрос разделить можно в принципе но там придется все основывать на других ХП чтобы интерфейс не нарушать, а на это надо время.

Всем спасибо,
отдельное Nikolay"ю ^|^.

← →
Bless © (2005-08-30 15:41) [12]

Кстати, попробуй трюк, предложенный в
http://www.sql.ru/articles/mssql/2005/070704TechniqueForEnsuringPlanStabilityInSQLServer2000.shtml

А вдруг поможет и удастся обойтись без хинтов :).

← →
Nikolay M. © (2005-08-30 15:53) [13]

> Malign

На здоровье, обращайся :)

> Bless © (30.08.05 15:41) [12]
> Кстати, попробуй трюк

Ненадежно, не всегда применимо и вызывает сдвиг по фазе в мозгах программистов следующих за тобой поколений :)
В итоге получается "нечто" вроде такого:
http://www.rsdn.ru/Forum/Message.aspx?mid=1320137&only=1
:))))

← →
Bless © (2005-08-30 16:27) [14]

Nikolay M. [13]>
Да ладно. Я ж в порядке расширения кругозора :)
Кстати, по ссылке вполне читаемый код (правда, pop ebx потеряно). Хоть и не оптимальный.

> Bless © (30.08.05 16:27) [14]
> Да ладно. Я ж в порядке расширения кругозора :)

Ну, тогда другое дело :)

> Кстати, по ссылке вполне читаемый код (правда, pop ebx потеряно).
> Хоть и не оптимальный.

Согласен с мнением, что кг/ам :)

>Согласен с мнением, что кг/ам :)

Что-то я не уловил, что не так в том примере (кроме размера var-параметров)?
И что такое "ам"? :)

> Bless © (30.08.05 17:00) [16]

http://www.sokr.ru/?text=%EA%E3%2F%E0%EC&where=abbr&exact=on
это к вопросу об ассемблерных вставках

Nikolay M.[17]>

ЛОЛ!

← →
АлексейК (2005-08-31 05:02) [19]

Nikolay M. © (30.08.05 13:58) [9]

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

Malign (30.08.05 15:29) [11]

Почитай статью "Информационная система и реляционная СУБД" в разделе проектирование на www.rsdn.ru, очень интересно когда оборотка собирается из 10 миллионов записей за 1-2 секунды, при этом прямой запрос отрабатывает 6 минут.

> АлексейК (31.08.05 05:02) [19]
> Речь в начале шла об одном и том же запросе

Что я и написал в самом начале: эти две ситуации не равнозначны. Поскольку автор в тот момент не знал, чем отличается запрос без параметров и процедура с параметрами, он и говорил о них как об одном и том же "запросе".

Скорость работы хранимых процедур значительно ниже запросов Найти похожие ветки