此 SQL 游标的改进 - 它真的很慢！

Question

我有一个使用游标的 sql 过程，但它真的很慢。我希望通过使用基于集合的操作或类似的东西来提高速度，但我不知道如何将其应用到这个特定的过程：

    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

感谢您提供的任何帮助！

Answer 1

泰德，我相信缓慢的原因可能是，除了您使用游标的事实之外，每次通过游标都会更新完整的 tblAnswers 。我预计答案表中有多行，因为在设计过程中使用了游标。在决定从游标更改为基于集合的操作之前，您是否会考虑向答案表的更新添加 WHERE 子句。

关于我的答案

如果 udf 很昂贵，那么我会在问题表中添加一列，或者如果无法修改问题表，则创建一个新表。在插入或更新问题时使用触发器，使用“multi”标志和函数结果填充新列。
使用下面的代码作为模型更新 sp 中的答案表。使用问题 ID 和“multi”标志的值调用 SP。

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

Answer 2

我可能遗漏了一些东西，但是如果您从 where 子句中取出 @QuestionId，为什么这个位不能在光标之外工作？：

       --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

编辑

不了解有关我不确定的元数据的更多信息如何处理问题的多元素，但这应该是答案的好方法：

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

Answer 3

您可以通过连接更新答案表，即连接 ID 上的问题答案，然后使用 where 子句限制 @ID。