SQLTeam.com | Weblogs | Forums

SQL Stored procedure executing for over 25+ minutes


#1

I have a stored procedure that I have inherited. When I try to execute it in SQL Server it executes for over 25+ minutes. I cant seem to find the issue. Any ideas??

ALTER PROCEDURE [dbo].[sp_AssignFR]
-- Add the parameters for the stored procedure here
@competitionid int,
@Num_reviews int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Common variable declarations
DECLARE @Index INT
DECLARE @Num_nominees INT
DECLARE @Num_reviewers INT
DECLARE @Rev_ID nvarchar(255), @Nom_ID varchar(8)
DECLARE @RevsPerReviewer INT
DECLARE @Row_test INT
DECLARE @Left_overs INT --...
DECLARE @RevCursorStatus INT -- Keep @@FETCH_STATUS seperate for each cursor
DECLARE @NomCursorStatus INT
declare @actualCompID int
create table #RedoNominees (Nom_ID varchar(8)) -- temp table used to hold Nom_IDs already assigned to Rev_IDs

set @actualCompID = (select comp_to_review_id from Competition where id=@competitionid)

select "Application Number" into #nominees_uf
from Fellowship_Results
where UF='Y'
and comp_id = @actualCompID
and nominated=1
SET @Num_nominees = @@rowcount
-- Insert first randomized list of nominees
select * into #mult_nominees_uf
from #nominees_uf order by newid()
-- Insert (Reviews per nominee - 1) more randomized lists
SET @Index = 1 ;	-- reinit variable
WHILE @Index < @Num_reviews BEGIN
	insert into #mult_nominees_uf
	select * from #nominees_uf order by newid()
	SET @Index = @Index + 1
END
-- Create temp table for Reviewers
select uf_rev_id into #temp_reviewers_uf
from Reviewer_Competition
where uf_rev_id like 'UF%'
and competition_id = @actualCompID
--and Term_Exp >= '2009'
SET @Num_reviewers = @@rowcount
SET @RevsPerReviewer = (@Num_nominees * @Num_reviews) / nullif(@Num_reviewers,0)	-- will be INT part of the result

-- Declare and open cursors
DECLARE @ReviewerCursorUF CURSOR
SET @ReviewerCursorUF = CURSOR FAST_FORWARD FOR
	select * from #temp_reviewers_uf order by newid()
OPEN @ReviewerCursorUF
DECLARE @NomineeCursorUF CURSOR
SET @NomineeCursorUF = CURSOR FAST_FORWARD FOR
	select * from #mult_nominees_uf
OPEN @NomineeCursorUF

-- Begin fetching
FETCH NEXT FROM @ReviewerCursorUF into @Rev_ID
SET @RevCursorStatus = @@FETCH_STATUS
WHILE @RevCursorStatus = 0
BEGIN
	SET @Index = 0

	--First, check doubles table from previous reviewer; if not empty, fill these nominees first
	SET @Row_test = (SELECT count(Nom_ID) FROM #RedoNominees)
	IF (@Row_Test > 0)
	BEGIN
		--insert all previous nominees to Nominee_Reviewer first
		INSERT INTO Nominee_Reviewer (Rev_ID, Nom_ID, Score, comp_id)
		SELECT @Rev_ID, Nom_ID, 0, @actualCompID FROM #RedoNominees

		--remove from extras table so they are not doubly added
		DELETE FROM #RedoNominees
		WHERE Nom_ID is not null

		--update @Index to reflect nominees already added
		SET @Index = @Row_test
	END

	--Start adding UF nominees
	WHILE @Index < @RevsPerReviewer
	BEGIN
		FETCH NEXT FROM @NomineeCursorUF into @Nom_ID
		SET @NomCursorStatus = @@FETCH_STATUS
		IF @NomCursorStatus = 0
		BEGIN
			--Check if already in Nominee_Reviewer table
			SET @Row_test = (SELECT count(Nom_ID) FROM Nominee_Reviewer
								WHERE (Rev_ID = @Rev_ID and Nom_ID = @Nom_ID and comp_id = @actualCompID))

			--if so, add nominee to extras table and decrement @Index
			IF (@Row_test > 0)
			BEGIN
				--insert into temp table
				INSERT INTO #RedoNominees VALUES (@Nom_ID)
				SET @Index = @Index - 1	-- Decrement so that the current reviewer still gets @RevsPerReviewer nominees to review
			END
			ELSE
			BEGIN
				INSERT INTO Nominee_Reviewer VALUES (@Rev_ID, @Nom_ID, NULL, 0, NULL, @actualCompID)
			END
		END
		SET @Index = @Index + 1
	END
	FETCH NEXT FROM @ReviewerCursorUF into @Rev_ID
	SET @RevCursorStatus = @@FETCH_STATUS
END
-- Clean up time
CLOSE @ReviewerCursorUF
DEALLOCATE @ReviewerCursorUF
CLOSE @NomineeCursorUF
DEALLOCATE @NomineeCursorUF

-- At this point, all reviewers have equal # of reviews, but some nominess only in there (@Num_reviews - 1) times
-- Find which nominees and randomly assign to Reviewer
DECLARE @NomineeLeftoversUF CURSOR
SET @NomineeLeftoversUF = CURSOR FAST_FORWARD FOR
	SELECT Nom_ID from Nominee_Reviewer
	where comp_id = @actualCompID
	GROUP BY Nom_ID
	HAVING COUNT(Nom_ID) < @Num_reviews
OPEN @NomineeLeftoversUF

DECLARE @ReviewerExtraUF CURSOR
SET @ReviewerExtraUF = CURSOR FAST_FORWARD FOR
	SELECT Rev_ID FROM Nominee_Reviewer
	WHERE Rev_ID like 'UF%'
	and comp_id = @actualCompID
	GROUP BY Rev_ID
	HAVING count(Rev_ID) = @RevsPerReviewer --Find only reviewers without any appointed overage reviews
OPEN @ReviewerExtraUF

DECLARE @num_leftovers INT
SET @num_leftovers = (SELECT COUNT(A.Nom_ID) FROM 
						(SELECT COUNT(Nom_ID) AS Nom_ID FROM Nominee_Reviewer
						GROUP BY Nom_ID
						HAVING COUNT(Nom_ID) < @Num_reviews) AS A
						GROUP BY Nom_ID)
WHILE (@num_leftovers > 0)
BEGIN
	--Each itteration, re-open cursor to only be selecting nominees that need another review
	CLOSE @NomineeLeftoversUF
	SET @NomineeLeftoversUF = CURSOR FAST_FORWARD FOR
		SELECT Nom_ID from Nominee_Reviewer
		GROUP BY Nom_ID
		HAVING COUNT(Nom_ID) < @Num_reviews
	OPEN @NomineeLeftoversUF
	FETCH NEXT FROM @NomineeLeftoversUF into @Nom_ID
	SET @NomCursorStatus = @@FETCH_STATUS
	WHILE (@NomCursorStatus = 0)
	BEGIN
		FETCH NEXT FROM @ReviewerExtraUF into @Rev_ID
		SET @RevCursorStatus = @@FETCH_STATUS
		WHILE (@RevCursorStatus = 0)
		BEGIN
			--Check if currently in the table
			SET @Row_test = (SELECT count(Nom_ID) FROM Nominee_Reviewer
								WHERE (Rev_ID = @Rev_ID and Nom_ID = @Nom_ID and comp_id = @actualCompID))

			--if not, add to table
			IF (@Row_test = 0)
			BEGIN
				--Insert into table
				INSERT INTO Nominee_Reviewer VALUES (@Rev_ID, @Nom_ID, NULL, 0, NULL, @actualCompID)
				set @num_leftovers = @num_leftovers - 1
				set @NomCursorStatus = -1
				set @RevCursorStatus = -1

				--Reopen Cursor; only grab list of reviewers with no extra nominees
				CLOSE @ReviewerExtraUF
				SET @ReviewerExtraUF = CURSOR FAST_FORWARD FOR
					SELECT Rev_ID FROM Nominee_Reviewer
					WHERE Rev_ID like 'UF%'
					and comp_id = @actualCompID
					GROUP BY Rev_ID
					HAVING count(Rev_ID) = @RevsPerReviewer --Find only reviewers without any appointed overage reviews
				OPEN @ReviewerExtraUF
			END
			--if so, fetch next reviewer; not nominee
			ELSE
			BEGIN
				FETCH NEXT FROM @ReviewerExtraUF into @Rev_ID
			END
		END
		FETCH NEXT FROM @NomineeLeftoversUF into @Nom_ID
	END
END

CLOSE @ReviewerExtraUF
DEALLOCATE @ReviewerExtraUF
CLOSE @NomineeLeftoversUF
DEALLOCATE @NomineeLeftoversUF


select "Application Number" into #nominees_ge
from Fellowship_Results
where GE='Y'
and comp_id = @actualCompID
SET @Num_nominees = @@rowcount

-- Insert first randomized list of nominees
select * into #mult_nominees_ge
from #nominees_ge order by newid()

-- Insert (Reviews per nominee - 1) more randomized lists
SET @Index = 1 ;	-- reinit variable
WHILE @Index < @Num_reviews BEGIN
	insert into #mult_nominees_ge
	select * from #nominees_ge order by newid()
	SET @Index = @Index + 1
END

-- Create temp table for Reviewers
select uf_rev_id into #temp_reviewers_ge
from Reviewer_Competition
where uf_rev_id like 'GE%'
and competition_id = @actualCompID
--and Term_Exp >= '2009'
SET @Num_reviewers = @@rowcount
SET @RevsPerReviewer = (@Num_nominees * @Num_reviews) / nullif(@Num_reviewers,0)

-- Declare and open cursors
DECLARE @ReviewerCursorGE CURSOR
SET @ReviewerCursorGE = CURSOR FAST_FORWARD FOR
	select * from #temp_reviewers_ge order by newid()
OPEN @ReviewerCursorGE

DECLARE @NomineeCursorGE CURSOR
SET @NomineeCursorGE = CURSOR FAST_FORWARD FOR
	select * from #mult_nominees_ge
OPEN @NomineeCursorGE

-- Begin fetching
FETCH NEXT FROM @ReviewerCursorGE into @Rev_ID
SET @RevCursorStatus = @@FETCH_STATUS
WHILE @RevCursorStatus = 0
BEGIN
	SET @Index = 0

	--First, check extras table from previous reviewer; if not empty, fill these nominees first
	SET @Row_test = (SELECT count(Nom_ID) FROM #RedoNominees)
	IF (@Row_Test > 0)
	BEGIN
	
		INSERT INTO Nominee_Reviewer (Rev_ID, Nom_ID, Score, comp_id)
		SELECT @Rev_ID, Nom_ID, 0, @actualCompID FROM #RedoNominees

		--remove from extras table so they are not doubly added
		DELETE FROM #RedoNominees
		WHERE Nom_ID is not null

	
		SET @Index = @Row_test
	END

	
	WHILE @Index < @RevsPerReviewer
	BEGIN
		FETCH NEXT FROM @NomineeCursorGE into @Nom_ID
		SET @NomCursorStatus = @@FETCH_STATUS
		IF @NomCursorStatus = 0
		BEGIN
			--Check if already in Nominee_Reviewer table
			SET @Row_test = (SELECT count(Nom_ID) FROM Nominee_Reviewer
								WHERE (Rev_ID = @Rev_ID and Nom_ID = @Nom_ID and comp_id = @actualCompID))

			--if so, add nominee to extras table and decrement @Index
			IF (@Row_test > 0)
			BEGIN
				--insert into temp table
				INSERT INTO #RedoNominees VALUES (@Nom_ID)
				SET @Index = @Index - 1	-- Decrement so that the current reviewer still gets @RevsPerReviewer nominees to review
			END
			ELSE
			BEGIN
				INSERT INTO Nominee_Reviewer VALUES (@Rev_ID, @Nom_ID, NULL, 0, NULL, @actualCompID)
			END
		END
		SET @Index = @Index + 1
	END
	FETCH NEXT FROM @ReviewerCursorGE into @Rev_ID
	SET @RevCursorStatus = @@FETCH_STATUS
END
-- Clean up time
CLOSE @ReviewerCursorGE
DEALLOCATE @ReviewerCursorGE
CLOSE @NomineeCursorGE
DEALLOCATE @NomineeCursorGE

-- At this point, all reviewers have equal # of reviews, but some nominess only in there twice...
-- Find which nominees and randomly assign to Reviewer
DECLARE @NomineeLeftoversGE CURSOR
SET @NomineeLeftoversGE = CURSOR FAST_FORWARD FOR
	SELECT Nom_ID from Nominee_Reviewer
	where comp_id = @actualCompID
	GROUP BY Nom_ID
	HAVING (COUNT(Nom_ID) % @Num_reviews <> 0) -- cannot assume COUNT(Nom_ID) < @Num_reviews b/c nominess with both UF and GE
	--HAVING (COUNT(Nom_ID) < @Num_reviews) or (COUNT(Nom_ID) = 4) or (COUNT(Nom_ID) = 5) -- 4,5 for current quarter; should really be "HAVING COUNT(Nom_ID) MOD @Num_reviews <> 0" .... I think
OPEN @NomineeLeftoversGE

DECLARE @ReviewerExtraGE CURSOR
SET @ReviewerExtraGE = CURSOR FAST_FORWARD FOR
	SELECT DISTINCT Rev_ID FROM Nominee_Reviewer
	WHERE Rev_ID like 'GE%'
	and comp_id = @actualCompID
OPEN @ReviewerExtraGE

SET @num_leftovers = (select count(*) from (SELECT count(Nom_ID) AS Nom_ID FROM Nominee_Reviewer
						GROUP BY Nom_ID
						HAVING (COUNT(Nom_ID) % @Num_reviews <> 0)) as B) -- cannot assume COUNT(Nom_ID) < @Num_reviews b/c nominess with both UF and GE
						--HAVING (COUNT(Nom_ID) < 3) or (COUNT(Nom_ID) = 4) or (COUNT(Nom_ID) = 5)) as B) -- once again, should really be "HAVING COUNT(Nom_ID) MOD @Num_reviews <> 0"
WHILE (@num_leftovers > 0)
BEGIN

	CLOSE @NomineeLeftoversGE
	SET @NomineeLeftoversGE = CURSOR FAST_FORWARD FOR
		SELECT Nom_ID from Nominee_Reviewer
		where comp_id = @actualCompID
		GROUP BY Nom_ID
		HAVING (COUNT(Nom_ID) < @Num_reviews) or (COUNT(Nom_ID) = 4) or (COUNT(Nom_ID) = 5)
	OPEN @NomineeLeftoversGE															
	FETCH NEXT FROM @NomineeLeftoversGE into @Nom_ID
	SET @NomCursorStatus = @@FETCH_STATUS
	WHILE (@NomCursorStatus = 0)
	BEGIN
		FETCH NEXT FROM @ReviewerExtraGE into @Rev_ID
		SET @RevCursorStatus = @@FETCH_STATUS
		WHILE (@RevCursorStatus = 0)
		BEGIN
			--Check if currently in the table
			SET @Row_test = (SELECT count(Nom_ID) FROM Nominee_Reviewer
								WHERE (Rev_ID = @Rev_ID and Nom_ID = @Nom_ID and comp_id = @actualCompID))

			--if not, add to table
			IF (@Row_test = 0)
			BEGIN
				--Insert into table
				INSERT INTO Nominee_Reviewer VALUES (@Rev_ID, @Nom_ID, NULL, 0, NULL, @actualCompID)
				set @num_leftovers = @num_leftovers - 1
				set @NomCursorStatus = -1
				set @RevCursorStatus = -1

				
				CLOSE @ReviewerExtraGE
				SET @ReviewerExtraGE = CURSOR FAST_FORWARD FOR
					SELECT Rev_ID FROM Nominee_Reviewer
					WHERE Rev_ID like 'GE%' and comp_id = @actualCompID
					GROUP BY Rev_ID
					HAVING count(Rev_ID) = @RevsPerReviewer --Find only reviewers without any appointed overage reviews
				OPEN @ReviewerExtraGE
			END
			--if so, fetch next reviewer; not nominee...
			ELSE
			BEGIN
				FETCH NEXT FROM @ReviewerExtraGE into @Rev_ID
			END
		END
		FETCH NEXT FROM @NomineeLeftoversGE into @Nom_ID
	END
END

CLOSE @ReviewerExtraGE
DEALLOCATE @ReviewerExtraGE
CLOSE @NomineeLeftoversGE
DEALLOCATE @NomineeLeftoversGE
drop table #nominees_uf
drop table #mult_nominees_uf
drop table #temp_reviewers_uf
drop table #nominees_ge
drop table #mult_nominees_ge
drop table #temp_reviewers_ge
drop table #RedoNominees

#2

Cursors BAD! Nested Cursors BAD-BAD!!
Fire BURN! Fire BAD!! Fire Hurt! Fire BAD-BAD!!!
.
What is the code logic trying to accomplish? Perhaps a solution can be found. It just hurts to see this code....

(Cursors BAD! Cursors SLOW!! Cursors BAD-BAD!!!)