嵌套while循环比嵌套游标占用更多时间

Question

从两个sql语句中提取数据后，我插入n行。到目前为止，我使用了两种方法，第一种是Cursor，另一种是While循环。

嵌套游标：

begin 
    declare userId,taskId int default 0; 
    declare userCnt int default 0; 
    declare c1 cursor for select us_id from us_uxusermaster ; 
    declare continue handler for not found set userCnt=1; 
    open c1; 
    CheckId: loop 
    fetch c1 into userId; 
    if userCnt=1 
    then 
     leave CheckId; 
    end if; 
      Select pl.pl_minTarget into target from pl_planlist pl inner join ap_affiliateplan ap inner join us_uxusermaster us on Find_in_set(pl.pl_id,us.us_planListId) and ap.ap_id =us.us_taskPlanId where us_id=userId and pl.pl_serviceName=2; 
       Begin 
       DECLARE taskId int default 0; 
       Declare taskCnt int default 0; 
       Declare t1 cursor for select tk.tk_id from tk_taskmaster tk where tk.tk_activeTime=AddDate(Current_date(),1) and tk_actStatus=0 and tk_status=1 limit target; 
       Declare continue handler for not found set taskCnt=1; 
       open t1; 
       CheckTask: loop 
       fetch t1 into taskId; 
       if taskCnt=1 
       then 
       leave CheckTask; 
       end if; 

        insert into ut_userstask(ut_tk_id,ut_us_id,ut_edtm,ut_eby) values (taskId,userId,current_timestamp,'Via-Event'); 
       end loop checkTask; 
       close t1; 
       End; 
    end loop CheckId; 
    close c1; 

    end; 

While循环：

begin 
declare taskName,taskCode,description,url,userLevel,TaskStatus,TaskActStatus,Steps,taskId,userId varchar(50); 

declare activationTime,deActivationTime datetime; 

Declare flag,flag2,counts,counts2 int default 0; 
Drop Temporary Table if exists temptrigg; 
Set @rownumber=0; 
Set @rownumber2=0; 
create temporary table temptrigg as(select * from (select (@rownumber := @rownumber + 1) AS newrow, us_id from us_uxusermaster) AS xst); 
select count(*) into counts from temptrigg; 
while(flag<counts) 
Do 
Set flag=flag+1; 
Select us_id into userId from temptrigg where newrow=flag; 

Drop Temporary Table if exists temptrigg2; 
Create temporary table temptrigg2 as(select * from(select (@rownumber2 := @rownumber2 + 1) as newrow2,tk.tk_id from tk_taskmaster tk where tk.tk_activeTime=Current_date() and tk_actStatus=0 and tk_status=1)as xst); 
Select count(*) into Counts2 from temptrigg2; 
While(flag2<Counts2) 
Do 
Set flag2=flag2+1; 
Select tk_id into taskId from temptrigg2 where newrow2=flag2; 

    insert into ut_userstask(ut_tk_id,ut_us_id,ut_edtm,ut_eby) values (taskId,userId,current_timestamp,'Via-Event'); 

End While; 
End While; 

end 

这里的问题是，while循环正在双倍时间比光标。我对其未来的结果感到困惑。通过替换嵌套的while循环来遵循游标会很好。

插入425行游标需要23.05秒，while循环需要46秒。这两个时间对我来说都太多了。有没有其他方法可以提高性能？

很高兴知道有没有。

Answer 1

我不知道如果我抓住每一个检查你有没有（特别是limit），但它会节省很多，如果你能挤它变成一个单一的insert..select这样的：

Insert into ut_userstask(ut_tk_id,ut_us_id,ut_edtm,ut_eby) 
Select 
    tk.tk_id, 
    us.us_id, 
    current_timestamp, 
    'Via-Event' 
from pl_planlist pl 
inner join ap_affiliateplan ap 
inner join us_uxusermaster us on ap.ap_id = us.us_taskPlanId 
inner join tk_taskmaster tk on tk.tk_activeTime=AddDate(Current_date(),1) and tk_actStatus=0 and tk_status=1 
where 
    pl.pl_serviceName=2 
    and Find_in_set(pl.pl_id,us.us_planListId) 

要记住的其他内容：确保您有适当的索引并尽量避免像FIND_IN_SET这样的函数。这通常表明您的数据库不够标准化，并且使用起来非常慢，因为它绕过了列上可用的任何索引。

即使你不能在一个select中放置everthing，它仍然可能更快地循环遍历一个主游标（例如获取用户），并为游标的每一行执行insert..select 。