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

Откуда: Нижний Новгород
Сообщений: 845
Добрый день, добрый ALL!
Подскажите направление мысли на подумать, и на что можно погрешить, возможные диагнозы по симптомам ибо я в ступоре. Так сказать "кто-то сверху отменил таблицу умножения, а меня не предупредил".

Вводная:
Имеется хранимка сутью которой является выполнение запроса на выборку с последущим возвращением результата. Запрос средней величины.... десяток LEFT OUTER JOIN... не суть.

Сея хранимка чудненько отрабатывает в течении последнего полугода. Намедни приходит поклёп, мол со стандартного времени выполнения 2-5 мин время выполнение ушло в бесконечность. Тест показыват, что действительно выполняться она стала >1часа и результата дождаться не получилось.

Берётся скрипт хранимки меняется
ALTER PROCEDURE dbo.spDymmy @P1 FLOAT, @P2 DATETIEME
на DECLARE @P1 FLOAT, @P2 DATETIEME
Удаляется AS BEGIN
Добавляется инициализация переменных...
Одинм словом получаю батч по наполнению на 100% равносильный коду хранимки.

Ключ на старт - результат работы виден через, ожидаемые, 2:30 мин.
Хранимка пересоздаётся, запускается - время выполнения стремится к бесконечности....
Создаётся Хранимка №2 - полная копия проблемной хранимки/ожидаемо отработанного батча но с иным именем, запускается - время выполнения стремится к бесконечности....

Т.е. де-факто один и тот же запрос вызыванный в батче, вызванный опосредованно через хранимую процедуру имеет Значительно отличающееся время выполнениня.

Если есть советы, мысли на тему, подскажите пожалуйста.
Благодарю.
12 ноя 09, 10:08    [7918221]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка vs Batch. Время выполнения. Дайте мысль  [new]
Glory
Member

Откуда:
Сообщений: 104760
Планы выполнения запроса в пакете и в процедуре вы сравнивали ?
12 ноя 09, 10:15    [7918274]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка vs Batch. Время выполнения. Дайте мысль  [new]
bacalavr
Member

Откуда:
Сообщений: 311
может, нужно план перестроить для хранимой процедуры?
12 ноя 09, 10:36    [7918409]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка vs Batch. Время выполнения. Дайте мысль  [new]
vanoman
Member

Откуда: Нижний Новгород
Сообщений: 845
Glory
Планы выполнения запроса в пакете и в процедуре вы сравнивали ?


Отличаются. Копаю...

+ наблюдение
Имеется набор входных параметров при которых отрабатывают
И запрос И хранимки
Также имеется набор входных параметров при которых отрабатывает
Запрос Но не хранимка.

Различия в наборах входных параметров идеологически верный.
Т.е. состав инициализируемых параметров одинаков. Параметры одни и те же.
Различия только в Значениях которыми инициализируются параметры.
12 ноя 09, 10:47    [7918507]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка vs Batch. Время выполнения. Дайте мысль  [new]
vanoman
Member

Откуда: Нижний Новгород
Сообщений: 845
bacalavr
может, нужно план перестроить для хранимой процедуры?

Частенько вижу подобные предложения, но к сожалению не в курсе как делается. Подскажите пожалуйста.
12 ноя 09, 10:48    [7918515]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка vs Batch. Время выполнения. Дайте мысль  [new]
bacalavr
Member

Откуда:
Сообщений: 311
vanoman
bacalavr
может, нужно план перестроить для хранимой процедуры?

Частенько вижу подобные предложения, но к сожалению не в курсе как делается. Подскажите пожалуйста.

exec procname with recompile
12 ноя 09, 10:57    [7918594]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка vs Batch. Время выполнения. Дайте мысль  [new]
vanoman
Member

Откуда: Нижний Новгород
Сообщений: 845
bacalavr
vanoman
bacalavr
может, нужно план перестроить для хранимой процедуры?

Частенько вижу подобные предложения, но к сожалению не в курсе как делается. Подскажите пожалуйста.

exec procname with recompile


Понял, спасибо.
Эта опция имеется в процедуре в загловке в виде.
ALTER PROCEDURE [dbo].[spGetDataXXX]
@PRICE_ID FLOAT --Код прайса
,@PRICE_DATE DATETIME --Дата прайса
,@CLIENT_ID FLOAT=NULL --Код клиента
WITH RECOMPILE
AS
BEGIN

Отличия, от предложенного Вами, принципиальное?
Т.е., как я понимаю, по Вашему он создастся один раз и заполмнится... Опция в заголовке велит каждый раз создавать план.

Резюмируя.
Опция WITH RECOMPILE была всегда.
Хранимка рабочая, затыкается только для определённго состава входных параметров, при этом такой-же запрос, с теми же "проблемными" входными параметрами, отрабатывает ожидаемо. Планы Хранимки/Запроса РАЗНЫЕ.

Жуть.
12 ноя 09, 11:06    [7918656]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка vs Batch. Время выполнения. Дайте мысль  [new]
Glory
Member

Откуда:
Сообщений: 104760
Поставим эксперимеент
Планы запросов в процедуре и пакете при использовании констант вместо переменных совпадают или нет ?
12 ноя 09, 11:09    [7918683]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка vs Batch. Время выполнения. Дайте мысль  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
vanoman,

вот это посмотрите ещё
12 ноя 09, 11:12    [7918699]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка vs Batch. Время выполнения. Дайте мысль  [new]
vanoman
Member

Откуда: Нижний Новгород
Сообщений: 845
Glory
Поставим эксперимеент
Планы запросов в процедуре и пакете при использовании констант вместо переменных совпадают или нет ?


Сделано:
Копия "проблемной хп" (отключить не могу т.к. люди работают) по макету
CREATE PROCEDURE [dbo].[spGetDataXXX]
@PRICE_ID FLOAT --Код прайса
,@PRICE_DATE DATETIME --Дата прайса
,@CLIENT_ID FLOAT=NULL --Код клиента
WITH RECOMPILE
AS
BEGIN

изменено на
CREATE PROCEDURE [dbo].[spGetDataXXX_V2]
/*@PRICE_ID FLOAT --Код прайса
,@PRICE_DATE DATETIME --Дата прайса
,@CLIENT_ID FLOAT=NULL --Код клиента
WITH RECOMPILE*/

AS
BEGIN

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

После Copy-Paste получившийся запрос в отдельное окно.

Запуск Хранимка, Запрос
Результат: Планы идентичны

Пробую с "Больным" составом входных параметров.
12 ноя 09, 11:26    [7918810]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка vs Batch. Время выполнения. Дайте мысль  [new]
vanoman
Member

Откуда: Нижний Новгород
Сообщений: 845
Эксперимент с больным набором данных.
Замена переменных на фактические значения дала уход времени выполнения Запроса в Бесконечность...

Что, Беря во внимание итерацию №1 с
Берётся скрипт хранимки меняется
ALTER PROCEDURE dbo.spDymmy @P1 FLOAT, @P2 DATETIEME
на DECLARE @P1 FLOAT, @P2 DATETIEME
Удаляется AS BEGIN
Добавляется инициализация переменных...
Одинм словом получаю батч по наполнению на 100% равносильный коду хранимки.

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

В общем, буду капать состояние данных, откусывая фрагменты запроса.

Спасибо за менения и время.
12 ноя 09, 11:51    [7919026]     Ответить | Цитировать Сообщить модератору
 Re: Хранимка vs Batch. Время выполнения. Дайте мысль  [new]
vanoman
Member

Откуда: Нижний Новгород
Сообщений: 845
Адчёт о проделанной работе.
Возможно, кому-либо пригодится.

Кратко: процедура заработала после перестроения плана выполнения.

DECLARE	@PRICE_ID	FLOAT		--Код прайса
	,@PRICE_DATE	DATETIME		--Дата прайса
	,@CLIENT_ID		FLOAT		--Код клиента
	,@CNO_LIST		VARCHAR(8000)	--Перечень колонок прайса к выводу (Значения разделённые символом ',')
	,@ACTIVE_ONLY	INT			-- Признак: вывод только активных позиций 1-да;0-нет
	SET NOCOUNT ON;
--Параметры при которых процедура работает ожидаемо
	SET	@PRICE_ID	=112
	SET	@PRICE_DATE ='11-14-2009'
	SET	@CLIENT_ID	=237236
	SET	@CNO_LIST	='1,3'
	SET	@ACTIVE_ONLY =1

exec [dbo].[spPriceReport] @PRICE_ID,@PRICE_DATE,@CLIENT_ID,@CNO_LIST,@ACTIVE_ONLY --WITH RECOMPILE

PRINT 'Step 1 Complete'
--Параметры при которых время работы процедуры стремится к бесконечности
	SET	@PRICE_ID	=110
	SET	@PRICE_DATE ='11-14-2009'
	SET	@CLIENT_ID	=130308
	SET	@CNO_LIST	='1,2'
	SET	@ACTIVE_ONLY =1

exec [dbo].[spPriceReport] @PRICE_ID,@PRICE_DATE,@CLIENT_ID,@CNO_LIST,@ACTIVE_ONLY-- WITH RECOMPILE
PRINT 'Step 2 Complete'

Развёрнуто, или как это делалось.
1. Попробованные танцы с бубном (см выше) стаильно работающего кода при "плохих" параметрах не дали.
2. В очередном пируэте были внесены ряд изменений в код по предложениям iap изложенным тут. (грохалась процедура и создавалась новая)
Однако катрина остовалась без изменений: с Правильными параметрами работало; с неправильными не работало.
3. После очереной итерации "грохаем процедуру и создаём" был выполнен код (см. выше выделен). Основная мысль - после создания процедуры выполнить её с "Правильными" параметрами создав тем самым Хороший план выполнениня, после которого выполнить процедуру с "Плохими" параметрами по имеющемуся плану.
Интересно то, что в итоге Задача была выполнена, Но план который обслуживает и "Хорошие" параметры и "Плохие" появился только с 5-6-ой попытки выполнения алгоритма: Грохнуть процедуру - Создать процедуру - выполнить алгоритм "настройки" (см. выше выделен).
3.1. Инетерсно то, что после того как "Ура! Есть стабильно поведение в обоих вариантах входных параметров!" Была произведена попытка запуска из Клиентского приложения которое строит нужный отчёт(с "плохими" параметрами). Реультат - уход в бесконечность. Не смотря на то, что (пока оно шло в бесконечность) скрипт
--Параметры при которых время работы процедуры стремится к бесконечности
	SET	@PRICE_ID	=110
	SET	@PRICE_DATE ='11-14-2009'
	SET	@CLIENT_ID	=130308
	SET	@CNO_LIST	='1,2'
	SET	@ACTIVE_ONLY =1

exec [dbo].[spPriceReport] @PRICE_ID,@PRICE_DATE,@CLIENT_ID,@CNO_LIST,@ACTIVE_ONLY

вызванный из Managment Studio выполнялся на ура. Т.е. за ожидаемое время, но для "Плохих" параметров.
3.2. С клиентским приложением был применён тот же алгоритм: Грохнуть процедуру - Создать процедуру - Запустить внешнее приложение с "Хорошими" параметрами (первое использование ХП) - Запустить внешнее приложение с "Плохими" параметрами.
Результат: И из Клиентского приложения всё залетало!

Итог: в голове сыр-бор и потеря веры в Логику. Особо интересен пируэт с разной работой в Клиентском приложении и, в это же время, Managment Studio.

Запишем опыт в Моск и на обед пора. :)
Ещё раз, Спасибо за мнения.
12 ноя 09, 13:40    [7920224]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить