Как прервать запрос по таймауту

Ребята помогите в такой проблеме.

Для некоторых деталей запрос на входимость выполняется очень долго - 5 минут 8 секунд. При этом выдает 29557 записей.

Клиент делфи вылетает через 3 минуты с ошибкой Timeout SQL operation.

Вопрос в том как в процедуре закончить рекурсивный цикл по времени скажем через 3 минуты ?
Или может как то оптимизировать запрос ?

/* ВЕДОМОСТЬ ВХОДИМОСТИ
								(с) 29.12.2008 RESEARCH	*/
CREATE    PROCEDURE [dbo].[PRIM] (@IZD VARCHAR(30)) AS


SELECT 1 UR,@IZD CHTO,@IZD CUDA,1 VX INTO #PRIM
DECLARE @UR INT
SET @UR=1

WHILE @@ROWCOUNT>0 
BEGIN
	SELECT @UR=@UR+1
	INSERT INTO #PRIM
	SELECT @UR UR,B.CHTO, B.CUDA, SUM(B.KVO*P.VX) VX FROM BAZSPEC B, #PRIM P
	WHERE  (B.CHTO=P.CUDA) AND (P.UR>=@UR-1)
	GROUP BY UR,B.CHTO,B.CUDA
END

SELECT CUDA, SUM(VX) TOTAL INTO #TOTAL FROM #PRIM
GROUP BY CUDA

SELECT PR.UR,UPPER(PR.CHTO) CHTO, dbo.NAM(PR.CHTO)NAMCHTO,UPPER(PR.CUDA) CUDA,dbo.NAM(PR.CUDA)NAMCUDA,PR.VX,TTL.TOTAL FROM #PRIM PR
LEFT JOIN #TOTAL TTL ON TTL.CUDA=PR.CUDA
ORDER BY 1 DESC ,4,2

RETURN @UR

GO

RESEARCH,

Покажите CREATE TABLE на таблицу BAZSPEC (включая все индексы). А также напишите, сколько записей в этой таблице.

RESEARCH
При этом выдает 29557 записей

Сколько итераций цикла при этом выполняется?

CREATE TABLE [BAZSPEC] (
	[CUDA] [varchar] (25) COLLATE Cyrillic_General_CI_AS NULL ,
	[PRR] [varchar] (1) COLLATE Cyrillic_General_CI_AS NULL ,
	[PRU] [varchar] (1) COLLATE Cyrillic_General_CI_AS NULL ,
	[CHTO] [varchar] (25) COLLATE Cyrillic_General_CI_AS NULL ,
	[KVO] [float] NULL ,
	[TABN] [int] NULL CONSTRAINT [DF__BAZSPEC__TABN__10E07F16] DEFAULT (0),
	[DAT] [smalldatetime] NULL ,
	CONSTRAINT [Пустые значения запрещены] CHECK ([CUDA] > '' and [CHTO] > ''),
	CONSTRAINT [Рекурсивная входимость запрещена] CHECK ([CUDA] <> [CHTO])
) ON [PRIMARY]

CREATE  CLUSTERED  INDEX [cudachto] ON [dbo].[BAZSPEC]([CUDA], [CHTO]) ON [PRIMARY]
CREATE  INDEX [CHTOCUDA] ON [dbo].[BAZSPEC]([CHTO], [CUDA]) ON [PRIMARY]

1850454 записей

(ОТГРУЗКА)СЛ030210
4 8 72502016119 8.0 49006 2010-02-08 10:03:00 (ОТГРУЗКА)СЛ030210
3 0 8ЮР293004 4.0 49006 2010-02-08 10:03:00 (ОТГРУЗКА)СЛ030210
3 0 8ЯТ151822 4.0 49006 2010-02-08 10:03:00 (ОТГРУЗКА)СЛ030210
6 8 91700293509 3.6000000000000001 49006 2010-02-08 10:03:00 (ОТГРУЗКА)СЛ030210
6 8 93530100209 22.800000000000001 49006 2010-02-08 10:03:00 (ОТГРУЗКА)СЛ030210
6 8 95140200109 0.23999999999999999 49006 2010-02-08 10:03:00

9 итераций

Ок, а dbo.NAM — что за функция? Если вместо dbo.NAM(PR.CHTO) и dbo.NAM(PR.CUDA) в последнем селекте подставить пустые строки, то вместо 5 минут 8 секунд — сколько будет выполняться процедура?

RESEARCH
CONSTRAINT [Пустые значения запрещены] CHECK ... CONSTRAINT [Рекурсивная входимость запрещена] CHECK ...

Любопытный приём, надо будет взять на заметку.

Гость333
RESEARCH CONSTRAINT [Пустые значения запрещены] CHECK ... CONSTRAINT [Рекурсивная входимость запрещена] CHECK ... Любопытный приём, надо будет взять на заметку.

Особенно если учесть, что CHECK CONSTRAINT радостно пропускает NULLы

Нулы туда не попадают так как прграмный код дает только ''... хотя вобще верное замечание надо исправить


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

RESEARCH
так что нет никаких способов вычислить время выполнения цикла и прервать его через 3 минуты ?

Чтобы при этом процедура завершилась штатным способом, без всяких "kill spid" и тайм-аутов? Нету такого.

Вы не туда копаете. Процедуру можно и нужно оптимизировать. Пока из того, что вы показали, я больше всего подозреваю неведомую функцию dbo.NAM. Применение скалярной пользовательской функции 60000 раз (2 раза на каждую из 29557 записей) при определённых условиях способно напрочь убить быстродействие запроса.

Так что покажите, что делает функция dbo.NAM, и посмотрите, какая будет скорость, если убрать её из запроса.

RESEARCH
так что нет никаких способов вычислить время выполнения цикла и прервать его через 3 минуты ?

Время выполнения вычисляется как разность между временем начала цикла(которое надо запомнить) и текущим временем.

автор
Время выполнения вычисляется как разность между временем начала цикла(которое надо запомнить) и текущим временем.

гениально это именно то что мне нужно подумал я
но потом решил все таки проверить первый совет хотя он и показался мне маловероятным
ведь функция NAM не подводила на малых выброках

Пока из того, что вы показали, я больше всего подозреваю неведомую функцию dbo.NAM. Применение скалярной пользовательской функции 60000 раз (2 раза на каждую из 29557 записей) при определённых условиях способно напрочь убить быстродействие запроса.

все же убрав эту функцию я с удивлением обнаружил что запрос выполнился за 7 секунд

итого конечная выборка теперь выглядит так

SELECT PR.UR,UPPER(PR.CHTO) CHTO, COALESCE(W1.NAME,NC1.NAMAT,@NONAM) NAMCHTO,UPPER(PR.CUDA) CUDA, COALESCE(W2.NAME,NC2.NAMAT,@NONAM) NAMCUDA,PR.VX,TTL.TOTAL FROM #PRIM PR
LEFT JOIN WHAT W1 ON W1.CHTO=PR.CHTO
LEFT JOIN NOMCEN NC1 ON NC1.KODM=PR.CHTO
LEFT JOIN WHAT W2 ON W2.CHTO=PR.CUDA
LEFT JOIN NOMCEN NC2 ON NC2.KODM=PR.CUDA
LEFT JOIN #TOTAL TTL ON TTL.CUDA=PR.CUDA
ORDER BY 1 DESC ,4,2

спасибо обоим за советы

PS запустил формирование дерева входимости на Delphi клиенте - построил два уровня и пока висит ггг