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

← →
SLP © (2007-01-16 12:02) [0]

Добрый день!
У меня хранимая процедура на сервере отрабатывает около 5 мин. Помещаю этот же самый запроc в QA и он отрабатывает за 2 сек. Сервер MSSQL2000, SP поставлен последний.
Помогите пожалуйста советом, куда смотреть ?

← →
stone © (2007-01-16 12:03) [1]

> Помогите пожалуйста советом, куда смотреть ?

на план выполнения запроса и команды в профайлере

← →
SLP © (2007-01-16 12:08) [2]

> на план выполнения запроса и команды в профайлере

Не знаю как в хранимой процедуре смотреть план выполнения запроса и команды...
Вот текст ХР

CREATE PROCEDURE [dbo].[ssd_delayed_period] (@d_mail datetime, @d_mail2 datetime ) AS

------EXEC DBO.SSD_DELAYED "20061205","20061215"

BEGIN
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, start_ssd b
join ves on b.id_ves=ves.id_ves
where a.ssd_mail>=@d_mail
and a.ssd_mail<=@d_mail2
and a.id_ssd=b.id_ssd
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

END
GO

← →
stone © (2007-01-16 12:19) [3]

> SLP © (16.01.07 12:08) [2]

профайле выдаст тебе команду типа sp_execsql и т.д. с параметрами
вот ее и выполни в аналайзере и посмотри палн выполнения
если будут теже проблемы есть еще ряд ньюансов

← →
evvcom © (2007-01-16 12:25) [4]

> [2] SLP © (16.01.07 12:08)
> from start_ssd a, start_ssd b
> join ves

ты бы уж определился, то ли ты будешь использовать ключевое слово join, то ли не будешь. А так от твоего винегрета подташнивает.

> and not exists
> (select id_ssd
> from start_ssd

по сути получается, если оптимизатор не сможет ЭТО как то умно переделать, то получится, что для каждого a.id_ves надо серверу выполнить подзапрос, отсюда и тормоза. Попробуй
from start_ssd a left join start_ssd с on c.id_ves=a.id_ves and c.datetime=a.datetime and c.ssd_mail<a.ssd_mail where c.id_ves is null
Вместо многократного выполнения подзапроса только join и filter.

← →
SLP © (2007-01-16 14:40) [5]

> evvcom © (16.01.07 12:25) [4]

Попробовала Ваш запрос в QA.
declare @d_mail datetime,@d_mail2 datetime
set @d_mail="20070101"
set @d_mail2="20070115"
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
left join start_ssd c
on c.id_ves=a.id_ves and
c.datetime=a.datetime and
c.ssd_mail<a.ssd_mail
join ves on a.id_ves=ves.id_ves
where c.id_ves is null
and 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
order by a.ssd_data,a.id_ves

Такой запрос, конечно, красивый. Но вот выполняется он в среднем на 2 сек дольше, чем с "and not exists". Мой 5 сек, Ваш - 7 сек. Запускала по очереди порядка 10 раз.
Но в любом случае, когда выполняешь его в ХП, все также медленно выполняется ХП, порядка 5 мин.

← →
Desdechado © (2007-01-16 15:44) [6]

а зачем нужна процедура из одного запроса?

← →
evvcom © (2007-01-16 15:50) [7]

> [5] SLP © (16.01.07 14:40)
> он в среднем на 2 сек дольше

значит оптимизатор нормально разруливал

> Но в любом случае, когда выполняешь его в ХП, все также
> медленно выполняется ХП, порядка 5 мин

значит план другой. Я с MS мало общался, но помню читал как-то советы по этому поводу. Есть в справке такое:
Creating a stored procedure that specifies the WITH RECOMPILE option in its definition ...
может это и не хороший совет (устранение последствий, а не причины), но тем не менее.

> cast(convert(char(10),a.ssd_mail,102) as datetime))

а это зачем? Если данные по сути дата или число не надо их хранить в строке! Аналогично с параметрами.

← →
clickmaker © (2007-01-16 15:53) [8]

> [6] Desdechado © (16.01.07 15:44)
> а зачем нужна процедура из одного запроса?

а что в этом криминального?

← →
Ega23 © (2007-01-16 15:54) [9]

> а это зачем? Если данные по сути дата или число не надо
> их хранить в строке! Аналогично с параметрами.

Это так округляется время и берётся предыдущий день.

← →
evvcom © (2007-01-16 15:58) [10]

> [9] Ega23 © (16.01.07 15:54)
> Это так округляется время и берётся предыдущий день

А, ну да... :)))
Только для этого есть спец.функция округления, но ты то знаешь это. Осталось автору это рассказать.

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

> evvcom © (16.01.07 15:50) [7]

Почитала книжки по серверу, справку, запускала 2 варианта выполнения процедуры ( с "and not exists" и с left join) :

exec DBO.SSD_DELAYED_period1 "20070101","20070115" with recompile

- результат тот же. от 5 до 8 мин.

> evvcom © (16.01.07 15:58) [10]

Расскажите, пожалуйста.

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

> [10] evvcom © (16.01.07 15:58)
> Только для этого есть спец.функция округления,

В MSSQL? какая?

← →
evvcom © (2007-01-16 17:13) [13]

> [12] Павел Калугин © (16.01.07 17:05)

Чего, нету что ль? Бардак.
Помнится перед Новым Годом как раз Ega23 в какой-то ветке и говорил об округлениях даты без конвертации к чару. Round дату принимает?

← →
Desdechado © (2007-01-16 17:23) [14]

clickmaker © (16.01.07 15:53) [8]
> а что в этом криминального?
Да ничего, если без фанатизма. Только зачем?

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

А если выполнить в QueryAnalizer что-то в духе
declare @d_mail datetime, @d_mail1 datetime 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, start_ssd b join ves on b.id_ves=ves.id_ves where a.ssd_mail>=@d_mail and a.ssd_mail<=@d_mail2 and a.id_ssd=b.id_ssd 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 exec [ssd_delayed_period] @d_mail, @d_mail1

и сравнить планы выполнения?
Кстати, лучшие собаководы рекомендуют начинать процедуру с команды SET NOCOUNT ON (но это я так, к слову)

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

@d_mail2 datetime

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

> Bless © (16.01.07 17:25) [16]

Спасибо. уже запустила, крутится 5 минуту....
Не знаю как разрулить ситуацию, завтра уже напишу планы выполнения. Нужно идти домой.

← →
Bless © (2007-01-17 11:59) [18]

> SLP © (16.01.07 17:31) [17]
> крутится 5 минуту....Не знаю как разрулить ситуацию, завтра
> уже напишу планы выполнения. Нужно идти домой.

Ну и как все прошло?

← →
SLP © (2007-01-17 12:54) [19]

> Bless © (17.01.07 11:59) [18]

Отвлеклась на другую работу... А насчет ХП : выполняется около 7 мин, планы запросов select-а и ХП на экране смотрю одновременно, но что с чем и как сравнивать и как их понимать не знаю. На каждом этапе и в первом и во-втором случае указывается % от всего выполнения selеcta ли ХП. А время выполнения этого этапа, например там где 70% не указывается, вернее я не вижу
Индексы в табл меняла, не помогает. Сейчас индексы по ssd_mail, datetime, id_ves. PK по id_ssd.

← →
Ega23 © (2007-01-17 13:10) [20]

> Помнится перед Новым Годом как раз Ega23 в какой-то ветке
> и говорил об округлениях даты без конвертации к чару. Round
> дату принимает?
>

Я делаю
Declare @dt datetime Set @dt=getdate() Select @dt=Cast(Cast(@dt as int) as datetime)

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

> [20] Ega23 © (17.01.07 13:10)

а я через строку привык гонять... Через целое быстрее разве?

← →
Ega23 © (2007-01-17 13:30) [22]

> а я через строку привык гонять... Через целое быстрее разве?
>
>

дык. DateTime ведь число.

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

> [22] Ega23 © (17.01.07 13:30)

а конверту не пофиг?

← →
Ega23 © (2007-01-17 13:36) [24]

> а конверту не пофиг?

Шут его знает. Специально не проверял, но мне кажется, что через int быстрее будет.

← →
Ega23 © (2007-01-17 13:46) [25]

Шеф так делает
Set @Date=cast( Floor(cast(@DateTime as numeric(18,12))) as datetime)
Это, ИМХО, как-то мутновато...

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

> [24] Ega23 © (17.01.07 13:36)

Олег, через округление числа - грабля...
declare @d datetime select @d=convert(datetime,"17 Jan 2007 23:59:59:01",13) select @d Select Cast(Cast(@d as int) as datetime) select convert(datetime,convert(varchar,@d,3),3)
и сравни результаты. иногда это ошибка.

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

I>
> SLP © (17.01.07 12:54) [19]

А на [19] ничего не напишите?

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

> [25] Ega23 © (17.01.07 13:46)

вот через флур - это верно. грабли не будет..

> Олег, через округление числа - грабля...

Согласен. Это я не продумал.
Ну тогда [25] - та наверняка

> [30] Ega23 © (17.01.07 13:58)

а select convert(datetime,convert(varchar,@d,3),3) чем не то?
ну да собстно результат то один и тот же...

> [27] SLP © (17.01.07 13:52)

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

> SLP © (17.01.07 12:54) [19]
>

Я правильно понял, что или ХП или запрос занял 70% от общего времени выполнения?
Если так, то это как-то не согласуется с твоими 5 мин против 2 сек.
Или ты опечаталась и на самом деле 5 мин против 2 мин?
Впрочем, в любом случае неплохо бы глянуть план.
Я правильно понимаю, что ты не знаешь, как его в текстовом виде получить?
Если да, то выполни сначала в Query Analizer-е
set showplan_all on, а потом
еще раз то, что в [15]. Полученные планы выложи здесь.

А на
http://sql.ru/forum/actualforum.aspx
спрашивать не пробовала?
Твой вопрос все же не к программированию баз в делфи, а чисто к MS SQL больше относится. Да и народу там поболе тусуется, шансы получить ответ выше.

> а select convert(datetime,convert(varchar,@d,3),3) чем не
> то?

Ты по-аккуратнее с varchar без длины.

> [34] Ega23 © (17.01.07 14:11)

:) но работает:) хотя если написать
declare @v varchar set @v=convert(varchar,getdate(),3)
то получим @v="1"
а если просто
select convert(varchar,getdate(),3)
то получим 17/01/07

> то получим @v="1"

по умолчанию при объявлении переменной размер варчар равен мин. кол-ву символов, т.е. 1

> select convert(varchar,getdate(),3)
> то получим 17/01/07

при конвертации размер по умолчанию = макс кол-ву символов конвертируемого типа

2 Ega23 © (17.01.07 14:11) [34]
так что тут все нормально :)

> Bless © (17.01.07 14:10) [33]

Спасибо. Сделала план запросов через "set showplan_all on", но там черт ногу сломает: 45 записей, куча полей, я все это записала как .csv, потом открыла Excel-ем, во-первых, Вам не послать и не выложить здесь, а во-вторых, даже неудобно, что кто-то будет тратить свое время, чтобы в этом разбираться...

> Павел Калугин © (17.01.07 13:53) [29]
> > [27] SLP © (17.01.07 13:52)
>
> а план запроса посмотреть дашь?

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

http://slil.ru/

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

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

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