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