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

Откуда: Спб
Сообщений: 634
Добрый день. Есть некая процедура. Периодически возникает при попытке ее выполнения в приложении вот такое сообщение "Время ожидания истекло". Это значит что процедура выполняется слишком долго и прекращается по таймауту. Процедура не выполняется на всех машинах и даже на сервере. НО стоит мне зайти в менеджмент студию открыть процедуру на редактирование и ничего не меняя нажать выполнить (Alter procedure) как сразу все начинает мгновенно формироваться.

Скажите в чем прикол. почему так происходит? На что влияет alter procedure что сразу все начинает работать
23 май 14, 10:11    [16059633]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
iap
Member

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

величина таймаута задаётся клиентом.
Например, студией (и этого таймаута хватает для выполнения процедуры).
23 май 14, 10:13    [16059643]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
maximIZ
Member

Откуда: Спб
Сообщений: 634
да вопрос не в этом. почему после выполнения alter procedure на всех машинах все работает мгновенно. что такого делает alter procedure
23 май 14, 10:15    [16059652]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
iap
maximIZ,

величина таймаута задаётся клиентом.
Например, студией (и этого таймаута хватает для выполнения процедуры).
Хотя, прочитал ещё раз - таймаут вроде и не при чём
23 май 14, 10:15    [16059653]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
maximIZ
Member

Откуда: Спб
Сообщений: 634
вот процедура, если кому интересно. в ней нет ничего необычного

ALTER procedure [dbo].[repFirstResults]
(@SDate datetime, @EDate datetime, @ReportType tinyint)
AS
SET NOCOUNT ON;
IF @ReportType = 12
	SELECT     tblBuilders.Name AS BuilderName, tblUsers.FIO AS Manager, tblRealtyObjects.Name AS ObjectName, tblCalls.CallDate, tblClients.UnionID AS ClientID, tblAdvertising.Name AS Advertising, 
						  tblCalls.ID AS CallID, tblScheduleType.Name AS CallType, CASE WHEN ISNULL(UseForPrice, 0) = 0 THEN 'Нет' ELSE 'Да' END AS HasContract, 'Да' AS FromCallCenter
	FROM         tblContractStates INNER JOIN
						  tblContract ON tblContractStates.ID = tblContract.ContractState RIGHT OUTER JOIN
						  tblManagerScheduler INNER JOIN
						  tblCallsClients ON tblManagerScheduler.ID = tblCallsClients.ScheduleID INNER JOIN
						  tblClients ON tblManagerScheduler.ClientID = tblClients.ID ON tblContract.ClientID = tblClients.ID RIGHT OUTER JOIN
						  tblScheduleType INNER JOIN
						  tblCallsManagers INNER JOIN
						  tblCalls ON tblCallsManagers.CallID = tblCalls.ID INNER JOIN
						  tblUsers ON tblCallsManagers.ManagerID = tblUsers.ID ON tblScheduleType.ID = tblCalls.CallType ON tblCallsClients.CallID = tblCalls.ID LEFT OUTER JOIN
						  tblAdvertising INNER JOIN
						  tblCallsAdvertising ON tblAdvertising.ID = tblCallsAdvertising.AdvertisingID ON tblCalls.ID = tblCallsAdvertising.CallID LEFT OUTER JOIN
						  tblRealtyObjects INNER JOIN
						  tblCallsObjects ON tblRealtyObjects.ID = tblCallsObjects.ObjectID INNER JOIN
						  tblBuilders ON tblRealtyObjects.BuilderID = tblBuilders.ID ON tblCalls.ID = tblCallsObjects.CallID
	WHERE     (CONVERT(DATE, tblCalls.CallDate) BETWEEN @SDate AND @EDate)
IF @ReportType = 13
	SELECT     tblBuilders.Name AS BuilderName, tblUsers.FIO AS Manager, tblRealtyObjects.Name AS ObjectName, tblManagerScheduler.OprnDate AS CallDate, tblClients.UnionID AS ClientID, 
						  tblAdvertising.Name AS Advertising, tblManagerScheduler.ID AS CallID, tblScheduleType.Name AS CallType, CASE WHEN ISNULL(UseForPrice, 0) = 0 THEN 'Нет' ELSE 'Да' END AS HasContract, 
						  CASE WHEN ISNULL(tblCallsClients.CallID, 0) = 0 THEN 'Нет' ELSE 'Да' END AS FromCallCenter
	FROM         tblCallsClients RIGHT OUTER JOIN
						  tblManagerScheduler INNER JOIN
						  tblClients ON tblManagerScheduler.ClientID = tblClients.ID INNER JOIN
						  tblUsers ON tblManagerScheduler.ManagerID = tblUsers.ID INNER JOIN
						  FirstCalls ON tblManagerScheduler.OprnDate = FirstCalls.OprnDate AND tblClients.ID = FirstCalls.ClientID INNER JOIN
						  tblScheduleType ON tblManagerScheduler.TypeID = tblScheduleType.ID ON tblCallsClients.ScheduleID = tblManagerScheduler.ID LEFT OUTER JOIN
						  tblContractStates INNER JOIN
						  tblContract ON tblContractStates.ID = tblContract.ContractState ON tblClients.ID = tblContract.ClientID LEFT OUTER JOIN
						  tblRealtyObjects INNER JOIN
						  tblBuilders ON tblRealtyObjects.BuilderID = tblBuilders.ID INNER JOIN
						  tblDemandObj ON tblRealtyObjects.ID = tblDemandObj.ObjectID ON tblClients.ID = tblDemandObj.ClientID LEFT OUTER JOIN
						  tblAdvertising INNER JOIN
						  tblClientAdvertising ON tblAdvertising.ID = tblClientAdvertising.AdvertisingID ON tblClients.ID = tblClientAdvertising.ClientID
	WHERE     (CONVERT(DATE, tblManagerScheduler.OprnDate) BETWEEN @SDate AND @EDate)
23 май 14, 10:18    [16059670]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
parameter sniffing
Guest
1. alter принудительно выкидывает план выполнения процедуры из кеша. И при следующем ее запуске сервер, на основе значений параметров, может выбрать другой план выполнения. Ключевые слова для поиска parameter sniffing


2. весь запрос не читал (сломал глаза об джойны с "отложенными" on-ми), но вот это доставляет
(CONVERT(DATE, tblCalls.CallDate) BETWEEN @SDate AND @EDate)
23 май 14, 10:30    [16059730]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
Glory
Member

Откуда:
Сообщений: 104760
maximIZ
почему после выполнения alter procedure на всех машинах все работает мгновенно. что такого делает alter procedure

Удаляет план выполнения из кэша. Поэтому при следующем запуске процедуры план создается заново.
Кроме того, у вас в процедуру два разных запроса, выполняющихся по условию. Что тоже не способствует нормальным планам

А условия вида (CONVERT(DATE, tblCalls.CallDate) предотвращают возможность использовать индексы по полям в выражении. Если эти индексы конечно есть
23 май 14, 10:34    [16059751]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
maximIZ
Member

Откуда: Спб
Сообщений: 634
спасибо
23 май 14, 10:52    [16059860]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
_human
Member

Откуда:
Сообщений: 560
Glory
А условия вида (CONVERT(DATE, tblCalls.CallDate) предотвращают возможность использовать индексы по полям в выражении. Если эти индексы конечно есть

поясните пожалуйста почему ? и как этого избежать ?
23 май 14, 11:08    [16059995]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
_human
Glory
А условия вида (CONVERT(DATE, tblCalls.CallDate) предотвращают возможность использовать индексы по полям в выражении. Если эти индексы конечно есть

поясните пожалуйста почему ? и как этого избежать ?
Потому что по полю tblCalls.CallDate индекс, может быть, и есть,
а по функции CONVERT() - точно нет и быть не может.
Но в условии фигурирует именно функция, а не поле
23 май 14, 11:12    [16060028]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
Glory
Member

Откуда:
Сообщений: 104760
_human
Glory
А условия вида (CONVERT(DATE, tblCalls.CallDate) предотвращают возможность использовать индексы по полям в выражении. Если эти индексы конечно есть

поясните пожалуйста почему ? и как этого избежать ?

Потому что индекс обычно включает поле tblCalls.CallDate, а не выражение (CONVERT(DATE, tblCalls.CallDate)
23 май 14, 11:18    [16060074]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
_human
Member

Откуда:
Сообщений: 560
iap,

т.е. ТС достаточно уйти от использования ф-ии.
а если в поле tblCalls.CallDate значения date и time и есть индекс на это поле. как отфильтровать по дате и заставить работать индекс ?
23 май 14, 11:19    [16060086]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
_human
iap,

т.е. ТС достаточно уйти от использования ф-ии.
а если в поле tblCalls.CallDate значения date и time и есть индекс на это поле. как отфильтровать по дате и заставить работать индекс ?
WHERE tblCalls.CallDate>=@SDate AND tblCalls.CallDate<DATEADD(DAY,1,@EDate)
Один день к @EDate можно, конечно, прибавить заранее.
Или вообще присваивать сразу на день больше.
Разумеется, в данном случае предполагается, что @SDate и @EDate содержат нулевое время
23 май 14, 11:23    [16060118]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
_human
как отфильтровать по дате и заставить работать индекс ?
Оптимизатор умеет использовать index seek в таких ситуациях:
create table #t (dt datetime primary key);

insert into #t
values
 ('20140101 10:00'), ('20140101 11:00'), ('20140101 12:00'),
 ('20140102 10:00'), ('20140102 11:00'), ('20140102 12:00'),
 ('20140103 10:00'), ('20140103 11:00'), ('20140103 12:00'),
 ('20140104 10:00'), ('20140104 11:00'), ('20140104 12:00'),
 ('20140105 10:00'), ('20140105 11:00'), ('20140105 12:00'),
 ('20140106 10:00'), ('20140106 11:00'), ('20140106 12:00'),
 ('20140107 10:00'), ('20140107 11:00'), ('20140107 12:00'),
 ('20140108 10:00'), ('20140108 11:00'), ('20140108 12:00');
go

set statistics profile on;
go

declare @dt date = '20140102';
select * from #t where cast(dt as date) = @dt;
go

set statistics profile off;
go

drop table #t;
go
23 май 14, 11:33    [16060220]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
_human
Member

Откуда:
Сообщений: 560
iap
предполагается, что @SDate и @EDate содержат нулевое время

да, именно этот случай. спасибо.
invm
Оптимизатор умеет использовать index seek

но не всегда так будет? я так понимаю, что все зависит от других событий и 100% не гарантирует поиск по индексу.
23 май 14, 11:44    [16060334]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
_human
iap
предполагается, что @SDate и @EDate содержат нулевое время

да, именно этот случай. спасибо.
invm
Оптимизатор умеет использовать index seek

но не всегда так будет? я так понимаю, что все зависит от других событий и 100% не гарантирует поиск по индексу.
Этосправедливо и во всех остальных случаях.
Какой индекс когда использовать, - всегда решает только оптимизатор.
Никаких 100%-х гарантий.
23 май 14, 11:52    [16060409]     Ответить | Цитировать Сообщить модератору
 Re: Процедура  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
_human
но не всегда так будет? я так понимаю, что все зависит от других событий и 100% не гарантирует поиск по индексу.
Гарантировать поиск по индексу без хинтов вообще нельзя. Даже если предикат классически саргабельный.
23 май 14, 11:58    [16060458]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить