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