долго работает хранимая процедура

← →
SLP © (2007-01-17 15:28) [40]

> Павел Калугин © (17.01.07 14:47) [39]

Послала файл со скриншотами : "Это план запроса для Select и ХП.doc"
Посмотрите, пож-та.

← →
Павел Калугин © (2007-01-17 15:41) [41]

> [40] SLP © (17.01.07 15:28)

куда?

← →
Bless © (2007-01-17 15:55) [42]

> SLP © (17.01.07 15:28) [40]
>
> Послала файл со скриншотами : "Это план запроса для Select
> и ХП.doc"
>

Куда послала-то? И еще выложи, если не трудно, еще и "неудобный" csv-файл.
По картинкам ориентироваться конечно удобнее, но я как-то подозреваю, что план в ширину у тебя не влез в один экран, что неудобно. Да и клацнув мышкой по пиктограмме на скриншоте, дополнительная информация у меня не всплывет, как у тебя в Analizer-е. А в "неудобном" файле это все есть. :)
Может, это и не понадобится, но паузы между твоими появлениями тут довольно велики, поэтому на всякий случай прошу все сразу.

И еще, напиши, сколько записей в каждой из таблиц, участвующих в запросе.

← →
SLP © (2007-01-17 15:56) [43]

зашла на http://slil.ru/ - получился http://zalil.ru/, набрала browse - выбрала свой файл - и Send. Куда ушел мой файл я не знаю. Может что не так сделала?

← →
SLP © (2007-01-17 15:59) [44]

> Bless © (17.01.07 15:55) [42]

Сейчас подготовлю. Спасибо. Паузы - это работы другой много...

← →
Bless © (2007-01-17 16:06) [45]

после того, как залила, у тебя вверху страницы (у меня в Опере в добавок и в адресной строке тоже) будет адрес типа
http://slil.ru/23762505
Вот его сюда и скажи

← →
SLP © (2007-01-17 16:16) [46]

> Bless © (17.01.07 16:06) [45]

http://slil.ru/23762556 - здесь .CVS
http://slil.ru/23762577 - десь док с скриншотами, выложила их по-порядку, поэтому понятно будет, т.к. не умещающиеся рисунки сделала с повторением по краям.
В табл start_ssd - около 270000 зап.
В табл ves- 1971 записей. Это справочник судов.

← →
Павел Калугин © (2007-01-17 16:44) [47]

> [46] SLP © (17.01.07 16:16)

гут вечерком дома попробую смоделировать

← →
SLP © (2007-01-17 16:51) [48]

> ел Калугин © (17.01.07 16:44) [47]

Спасибо. Только жаль Ваше время

← →
Bless © (2007-01-17 17:05) [49]

в CSV и на картинках - разные планы выполнения. В CSV они одинаковы и для запроса, и для хранимки. А на картинке - разные.
К тому же, на картинках у тебя написано, что хранимка выполняется 5-8 мин. А судя по плану она занимает 0.08% от времени выполнения. Т.е. выполняется ГОРАЗДО быстрее запроса. Так что я думаю, твое утверждение "хранимка -5 мин, запрос - 2 сек" - неверно.
Вероятнее, что ты выполняла запрос непосредственно сразу после хранимки, я прав?
Подозреваю, что выполни ты запрос первым, а хранимку - сразу вслед за ним, получила бы противоположный результат (о чем план в картинках и говорит).
Я хочу сказать, что 2 секунды, может быть, получились из-за того, что сервер кешировал данные или что-то в этом духе (я не шибко силен) и этот результат - "нечестный" и запорачиваться по поводу "почему большая разница" не стоит.
А вот по поводу, "почему аж 5 мин" - стоим, имхо.

Что касается запроса:
поле start_ssd.id_ssd случайно не ключевое? Если да, то тогда (если я не ошибаюсь) один start_ssd из запроса можно выбросить:
select rtrim(ves.iki_ves)+" "+ rtrim(ves.board_number)+" "+rtrim(ves.ves) as name_ves,cast(convert(char(10), a.datetime,102) as datetime) as ssd_data, datediff(day,a.datetime,cast(convert(char(10),a.ssd_mail,102) as datetime))-1 as day_r,a.id_ssd, a.id_ves, a.ssd_mail from start_ssd a join ves on a.id_ves=ves.id_ves where a.ssd_mail>=@d_mail and a.ssd_mail<=@d_mail2 and a.processstatus not in (1,11) and a.mem in (21,31) and a.id_ves<>0 and datediff(day,a.datetime,a.ssd_mail)>=2 and not exists (select id_ssd from start_ssd where id_ves=a.id_ves and datetime=a.datetime and ssd_mail<a.ssd_mail) order by a.ssd_data, a.id_ves

← →
Bless © (2007-01-17 17:16) [50]

Попробуй запрос из [49]. Результат работы должен быть таким же, как и старого запроса. А вот скорость... неуверен, но уж точно не медленнее.

← →
SLP © (2007-01-17 17:22) [51]

> Bless © (17.01.07 17:05) [49]

Нет, первым я выполняла запрос, вторым ХП. На самом деле запрос -= 2 сек, ХП - вертится от 5 до 8 мин.
id_ssd - ключевое. С Вашим вариантом согласна.
Завтра с утра еще посмотрю планы запросов, но по - моему я все правильно послала. Сейчас домой надо идти.

← →
SLP © (2007-01-17 17:24) [52]

SLP © (17.01.07 17:22) [51]

> Bless © (17.01.07 17:05) [49]

Нет, первым я выполняла запрос, вторым ХП. На самом деле запрос -= 2 сек, ХП - вертится от 5 до 8 мин.
id_ssd - ключевое. С Вашим вариантом согласна.
Завтра с утра еще посмотрю планы запросов, но по - моему я все правильно послала. Сейчас домой надо идти.

← →
Bless © (2007-01-17 17:36) [53]

> Нет, первым я выполняла запрос, вторым ХП.

Я имел ввиду не когда ты проверяла текст из [15], а когда создавала ветку
У меня хранимая процедура на сервере отрабатывает около 5 мин. Помещаю этот же самый запроc в QA и он отрабатывает за 2 сек.

тут ты сначала выполняла запрос, а вторым ХП?

> На самом деле запрос -= 2 сек, ХП - вертится от 5 до 8
> мин.

Судя по плану, все с точностью до наоборот:
на 1-ом скриншоте:
Query3: Query cost (relative to the batch): 99.92% Query text: select rtrim(ves.iki_ves)+" "+ rtrim(ves.board_number)+....

на 4-ом скриншоте:
Query 4: Query cost (relative to the batch): 0.08% Query text: exec [ssd_delayed_period] @d_mail, @d_mail2

И раз уж согласна с моим вариантом, то выполни новый запрос (только запрос, без ХП) и скинь план пожалуйста (уже завтра, конечно).

<OFFTOP>
Ничего, что я "Ты-каю"?
</OFFTOP>

← →
b z (2007-01-17 18:03) [54]

попробуйте так

http://dotnetslackers.com/SQL/re-38060_SQL_Server_Query_Analyzer_Runs_Fast_Stored_Procedure_Runs_Slow.aspx

← →
SLP © (2007-01-18 10:23) [55]

> Bless © (17.01.07 17:36) [53]

Приготовила Вам планы запросов с одним start_ssd. Хотела послать, но перед этим решила проверить как будет работать ХП с одним start_ssd.
Время выполнения 1 Сек!!!!
Так что дело в запросе, а не в сервере.
Виталий, большое Вам спасибо за внимание и помощь!
Павел! Вам тоже большое спасибо!

← →
Bless © (2007-01-18 11:06) [56]

> SLP © (18.01.07 10:23) [55]
>
> Время выполнения 1 Сек!!!!

Откровенно говоря, даже не рассчитывал, что так легко все разрешится. :)
Ну да хорошо все, что хорошо кончается.

← →
Павел Калугин © (2007-01-18 13:05) [57]

Да вроде не за что. Хорошо то, что хорошо кончается...

← →
SLP © (2007-01-19 09:39) [58]

> Bless © (17.01.07 17:36) [53]

Окончательный вариант ХП сделала такой:
CREATE PROCEDURE [dbo].[ssd_delayed_period2] (@d_mail datetime, @d_mail2 datetime ) AS
-----Опоздавшие ССД за конкретный период
-----ССД, пришедшие на замену, опоздавшими не считаются.
------EXEC DBO.SSD_DELAYED_period2 "20070101","20070115"

BEGIN
select * into #start_ssd from start_ssd where mem in (21,31) and id_ves<>0
select rtrim(ves.iki_ves)+" "+ rtrim(ves.board_number)+" "+rtrim(ves.ves) as name_ves,cast(convert(char(10),
a.datetime,102) as datetime) as ssd_data,
datediff(day,a.datetime,cast(convert(char(10),a.ssd_mail,102) as datetime))-1 as day_r,a.id_ssd, a.id_ves, a.ssd_mail
from #start_ssd a
join ves on a.id_ves=ves.id_ves
where a.ssd_mail>=@d_mail
and a.ssd_mail<=@d_mail2
and a.processstatus not in (1,11)
---and a.mem in (21,31)
---and a.id_ves<>0
and datediff(day,a.datetime,a.ssd_mail)>=2
and not exists
(select id_ssd
from #start_ssd
where id_ves=a.id_ves
and datetime=a.datetime
and ssd_mail<a.ssd_mail)
order by a.ssd_data, a.id_ves

drop table #start_ssd

END
GO

Запустила последний раз в 9-00,в самое напряженное время, когда start_ssd эксплуатируется оч. напряженно, в нее постоянно добавляются и редактируются записи. ( эта табл весь раб день так занята операторами).
С вариантом, сначала временной табл., ХП отрабатывает за 10 сек.
С вариантом напрямую к start_ssd - крутится более 3 мин и я снимаю, чтобы не тормозить работу операторов.

← →
Павел Калугин © (2007-01-19 10:36) [59]

> [58] SLP © (19.01.07 09:39)

ребята из Диасофта очень любят, дабы избежать блокировок, использовать конструкцию from table_name (NOLOCK), и так далее при выборке данных. Помогает, однако..

← →
MsGuns © (2007-01-20 13:12) [60]

QA ВСЕГДА выполняет запрос быстрее, чем "обертка"+Jet
И было бы странно, если бы было наоборот ;)

← →
SLP © (2007-01-22 08:50) [61]

> MsGuns © (20.01.07 13:12) [60]

Да. Но у меня эта ХП вставлена и в прогр на Delphi (компонент ADOStoredProc1). Так же долго выполнялась. Но посл. вариант через # стала выполняться в 3 раза быстрее...Пока так.

> Запустила последний раз в 9-00,в самое напряженное время,
> когда start_ssd эксплуатируется оч. напряженно, в нее постоянно
> добавляются и редактируются записи. ( эта табл весь раб
> день так занята операторами).
> С вариантом, сначала временной табл., ХП отрабатывает за
> 10 сек.
> С вариантом напрямую к start_ssd - крутится более 3 мин
> и я снимаю, чтобы не тормозить работу операторов.
>

хранимка, запущенная в 9-00 и просто запрос из QA, запущенный в это же время, отрабатывают одинаково долго? Или запрос, как и раньше, на пару порядков быстрее и имеет другой план выполнения?
Если все, как и раньше, то:

1)
может быть, совет из [54] - то, что нужно?
Более подробно этот вопрос (из [54]) освещен в статье
http://www.sql.ru/articles/mssql/2005/070704TechniqueForEnsuringPlanStabilityInSQLServer2000.shtml

2) сделай exec sp_recompile ssd_delayed_period и опять выполни запрос и хранимку с одинаковыми параметрами. Желательно делать это на копии базы, чтоб быть уверенным, что между sp_recompile и твоим вызовом хранимки не было вызова хранимки, инициированного каким-то пользователем (хотя если сделать все в одном батче ...exec sp_recompile... exec ssd_delayed_period... select, то вероятность этого будет невысокой наверное). Если и теперь планы выполнения будут разными, то я даже не знаю...
Если же планы стали одинаковыми, то, видимо, совет из [54] - таки хорош.

Если все вышесказанное - не подходит, то еще вопрос:
хранимка, запущенная не в час пик, отрабатывает так же долго?

По поводу хранимки со временной таблицей:

Может быть, я ошибаюсь, но я не вижу причин, по которым введение временной таблицы ускоряет выполнение твоей хранимки, состоящей из одного запроса. Не должно быть такой огромной разницы между вариантами с временной таблицей и без нее.
И блокировки таблицы start_ssd из-за активной работы тут ни при чем, имхо.
(Соответственно, опять же имхо, NOLOCK проблему не решит).
Ведь если запрос без # не может быстро отработать из-за блокировок, то заполнить временную таблицу ты тоже не смогла бы по причине этих же самых блокировок. А раз с этим проблем нет, то, видимо, дело не в блокировках start_ssd.

> Bless © (22.01.07 12:17) [62]

Я запускала ХП, когда таблицу уже никто не дергал. Отрабатывает также долго, как и в час пик. С # отрабатывает быстро, в час пик около 1 мин.
Дело в том, что start_ssd связана по id_ssd c 25 таблицами, которые в свою очередь связаны с справочниками. И получается, что когда я делаю сначала #start_ssd, а потом выполняется select, то #start_ssd уже чистенькая, без наследства в виде 25 табл + справочники.... Она забыла все свои связи.
Выходит так...
Всем спасибо.

SLP © (22.01.07 14:58) [63]

> Я запускала ХП, когда таблицу уже никто не дергал. Отрабатывает
> также долго, как и в час пик.

А просто запрос в Query Analizer-е тоже медленно работает? Или быстро? Меня очень интересует, осталась ли старая проблема, с которой начиналась эта ветка?

> С # отрабатывает быстро, в час пик около 1 мин.
> Дело в том, что start_ssd связана по id_ssd c 25 таблицами,
> которые в свою очередь связаны с справочниками. И получается,
> что когда я делаю сначала #start_ssd, а потом выполняется
> select, то #start_ssd уже чистенькая, без наследства в виде
> 25 табл + справочники.... Она забыла все свои связи.
> Выходит так...

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

В общем, тебе, конечно, решать, оставить все как есть или нет.
Но, имхо, эта ситуация (когда с # быстрее чем без) странная, и ее бы надо разрулить, а не обойти, всяко полезно на будущее.
Кроме того, 1 мин для запроса к таблицам твоих объемов - это неоправданно долго, на мой взгляд.

Скорей всего без # идет полное сканирование таблицы, а при выборке в # используется более простое условие, поэтому возможно работают уже и индексы. Из-за меньшего объема данных в # запрос работает тоже быстрее, даже невзирая на полное отсутстви индексов у #.

> Bless © (22.01.07 15:40) [64]
> Меня очень интересует, осталась ли старая проблема, с которой
> начиналась эта ветка?

Проблема осталась, причем старая процедура (без #) работает также долго как в час пик, так и тогда когда табл никто не дергает уже. Единственный раз, когда админ перезагружал сервер и были сняты все задачи, я неск раз запустила ХП (без #) и она отработала за 1 сек.
Сейчас с # отрабатывает быстро, в час пик около мин, в ост время 15 сек.
После всех этих практических проб, я все таки пришла к выводу, что при select-e такие обремененные табл тянут за собой все связи, лучше делать с #. Но я нигде этого не читала, это чисто практически, может я не права...

> После всех этих практических проб, я все таки пришла к выводу,
> что при select-e такие обремененные табл тянут за собой
> все связи, лучше делать с #. Но я нигде этого не читала,
> это чисто практически, может я не права...

Я на 99% уверен, что ты ошибаешься (1 % оставляю на "в мире нет ничего невозможного", на "человеку (мне) свойственно ошибаться" и на непредсказуемое влияние пятен на солнце на земную жизнь).

Посуди сама: раз старая проблема осталась (т.е. просто запрос в QA выполняется быстро, а аналогичная ХП - медленно), то как твой вывод с этим согласуется? Почему просто select быстро отрабатывает? Все связи ж остаются.

Совет из [54] пробовала?

> ess [67]

Хорошо, что я не выбрал себе ник Blass :)

> Bless © (23.01.07 09:50) [67]

54 попробовала. Сделала параметры так как в http://www.sql.ru/articles/mssql/2005/070704TechniqueForEnsuringPlanStabilityInSQLServer2000.shtml Сделала все как в статье, запускала и первоначально с 2007 годом, с параметрами определенными внутри ХП.
Не помогло, все также, ХП (из QA) без # работает около 6 мин. Запускала подряд неск раз. Это среднее время.
Из QA одновременно запускала select, отрабатывал 10-15 сек, ХП с # тоже около 15 сек. Так что не знаю, что там думает оптимизатор, но может новая версия SP к MSSQL поможет.

долго работает хранимая процедура Найти похожие ветки