Saturday, February 25, 2012

problems with a stored prc

I have to get a stored procedure to produce a result set then loop
through it and insert records into another table in the same db. What I
have written is causing SQL server to hang or some other performance
bottleneck. Here is the code. Anybody got any ideas? Thanks!

ALTER PROCEDURE initQuestionsAnswered
@.empid int,@.testid int
AS

declare @.ANSID VARCHAR(10),@.QID VARCHAR(10)
DECLARE MY_CURS1 CURSOR FOR
select ansid,answers.questionid from
answers inner join questions on
questions.questionid=answers.questionid
inner join concepts on
concepts.conceptid=questions.conceptid
where concepts.testid=@.testid

OPEN MY_CURS1

FETCH MY_CURS1 INTO @.ANSID,@.QID
WHILE (@.@.FETCH_STATUS=0)
BEGIN
if not exists(
select answerid from questionsanswered where questionid=@.QID and
empid=@.empid)

BEGIN
insert into questionsanswered
(empid,questionid,testid,answercorrect)
values (@.empid,@.QID,@.testid,0)
END

END
close my_curs1

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Yep, cursors usually destroy performance and scalability. Don't use
them. I think you want something like this (untested though):

CREATE PROCEDURE initQuestionsAnswered
@.empid INTEGER,
@.testid INTEGER
AS

INSERT INTO QuestionsAnswered (empid, questionid, testid,
answercorrect)
SELECT @.empid, A.questionid, @.testid, 0
FROM Answers
JOIN Questions AS Q
ON Q.questionid = A.questionid
JOIN Concepts AS C
ON C.conceptid = Q.conceptid
WHERE C.testid = @.testid
AND NOT EXISTS
(SELECT *
FROM QuestionsAnswered
WHERE questionid = A.questionid
AND empid = @.empid
AND testid = @.testid)

GO

--
David Portas
SQL Server MVP
--|||Phil Townsend wrote:
> I have to get a stored procedure to produce a result set then loop
> through it and insert records into another table in the same db. What
I
> have written is causing SQL server to hang or some other performance
> bottleneck. Here is the code. Anybody got any ideas? Thanks!
> ALTER PROCEDURE initQuestionsAnswered
> @.empid int,@.testid int
> AS
> declare @.ANSID VARCHAR(10),@.QID VARCHAR(10)
> DECLARE MY_CURS1 CURSOR FOR
> select ansid,answers.questionid from
> answers inner join questions on
> questions.questionid=answers.questionid
> inner join concepts on
> concepts.conceptid=questions.conceptid
> where concepts.testid=@.testid
> OPEN MY_CURS1
> FETCH MY_CURS1 INTO @.ANSID,@.QID
> WHILE (@.@.FETCH_STATUS=0)
> BEGIN
> if not exists(
> select answerid from questionsanswered where questionid=@.QID and
> empid=@.empid)
> BEGIN
> insert into questionsanswered
> (empid,questionid,testid,answercorrect)
> values (@.empid,@.QID,@.testid,0)
> END
> END
> close my_curs1
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

You don't have a second fetch inside your while loop.

--
David Rowland
http://dbmonitor.tripod.com

No comments:

Post a Comment