CODEWITHSUNDEEP
performancesql-server-2008cursor
Bob
Bob

Reputation: 3084

Improvement for this SQL Cursor - It's really slow!

I have a sql procedure in which I use a cursor, but it's really really slow. I'm looking to improve the speed by using set-based operation or such things, but I've no idea how to apply it to this specific procedure:

    declare @isMulti bit
    declare @QuestionID int
    declare db_cursor cursor FAST_FORWARD for
        select distinct QuestionID
        from tblQuestions (nolock)
        where ID=@ID

    open db_cursor   
    fetch next from db_cursor into @QuestionID   

    while @@FETCH_STATUS = 0   
    begin   
               --check if @isMulti is true or not for the current question
               if(@isMulti=1)
               begin
                    update tblAnswers
                    set col1 = 1, col2 = 1, col3 = (select count(*) from tblAnswers where QuestionID=@QuestionID and ID=@ID)
               end
               else if(@isMulti=0)
               begin
                    update tblAnswers
                    set col1 = AnswerID, col2 = 1, col3 = (select LEN(count(*)) from tblAnswers where QuestionID=@QuestionID and ID=@ID)
               end

               fetch next from db_cursor into @QuestionID   
    end
close db_cursor   
deallocate db_cursor

Thanks for any help provided!

Upvotes: 1

Views: 1271

Answers (3)

RC_Cleland
RC_Cleland

Reputation: 2294

Ted, I believe the cause of the slowness may be, beside the fact you are using a cursor, that the complete tblAnswers is updated every time through the cursor. I am expecting there are multiple rows in the answer table because a cursor was used during the design. Until the decision is made on the changing from a cursor to a set-based op would you consider adding a WHERE clause to the update for the answer table.

On to my answser

If the udf is expensive then I would add a column to the questions table or create a new table if modifing the question table is not possible. Populate the new column with a flag for "multi", with the result of the function, using a trigger when the question is inserted or updated.
Update the answers table in a sp using the code below as a model. Call the SP with the question ID and the value of the "multi" flag.

update tblAnswers
set col2 =1,
col1 = CASE @isMulti THEN 1 Else AnswerID
col3 = CASE @isMulti THEN (select count(*) from tblAnswers where ID=@ID) ELSE (select LEN(count(*)) from tblAnswers where ID=@ID)
from tblQuestions
inner join tblAnswers on tblQuestions.QuestionID= tblAnswers.QuestionID
WHERE tblQuestions.QuestionID= @ID

Upvotes: 1

amelvin
amelvin

Reputation: 9051

I may be missing something, but why doesn't this bit work outside the cursor if you take out the @QuestionId from the where clause?:

       --check if @isMulti is true or not
       if(@isMulti=1)
       begin
            update tblAnswers
            set col1 = 1, col2 = 1, col3 = (select count(*) from tblAnswers where ID=@ID)
       end
       else if(@isMulti=0)
       begin
            update tblAnswers
            set col1 = AnswerID, col2 = 1, col3 = (select LEN(count(*)) from tblAnswers where ID=@ID)
       end

EDIT

Without knowing more about the metadata I'm not sure about how to process the multi element to the questions, but this should be a good way to the answer:

declare @question table (questionid int, multi int)
declare @answer table (answerid int, col1 int, col2 int, col3 int)

insert into @question (questionid, multi) values (1, 0)
insert into @question (questionid, multi) values (2, 0)
insert into @question (questionid, multi) values (3, 0)
insert into @question (questionid, multi) values (4, 1)
insert into @question (questionid, multi) values (5, 1)


insert into @answer (answerid, col1, col2, col3) values (1, 0, 0, 0)
insert into @answer (answerid, col1, col2, col3) values (1, 0, 0, 0)
insert into @answer (answerid, col1, col2, col3) values (2, 0, 0, 0)
insert into @answer (answerid, col1, col2, col3) values (2, 0, 0, 0)
insert into @answer (answerid, col1, col2, col3) values (3, 0, 0, 0)
insert into @answer (answerid, col1, col2, col3) values (4, 0, 0, 0)
insert into @answer (answerid, col1, col2, col3) values (4, 0, 0, 0)
insert into @answer (answerid, col1, col2, col3) values (4, 0, 0, 0)
insert into @answer (answerid, col1, col2, col3) values (5, 0, 0, 0)

update @answer 
set col1 = 1, col2 = 1, col3 = (select count(*) from @answer a join @question q on a.answerid = q.questionid where q.multi = 0 and [@answer].answerid = a.answerid)

select distinct * from @answer

Upvotes: 1

Rikalous
Rikalous

Reputation: 4564

You can update the answer table across a join, i.e join Answers to Questions on the ID and then restrict on the @ID using a where clause.

Upvotes: 0

Related Questions