Dashanan Dashanan - 20 days ago 10
MySQL Question

Nested while loop taking more time than nested cursor

I am inserting n number of rows after fetching data from two sql statement. I have used two ways so far first one is Cursor and other is While loop.

Nested Cursor:

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

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


Here the problem is that the while loop is taking double time than the cursor. I am confused about its future results. will it be good to follow the cursor by replacing nested while loop.

While inserting 425 rows cursor taking 23.05 sec and while loop is taking 46 secs. both the timing are too much for me. Is there any other way to increase performance.

Will be glad to know if any.

Answer Source

I'm not sure if I caught every check you have in there (especially the limit), but it would save a lot if you could squeeze it into a single insert..select like this:

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)

Other stuff to keep in mind: Make sure you have proper indexes and try to avoid functions like FIND_IN_SET. It is generally a sign that your database is not normalized enough, and it's very slow to use, since it bypasses any indexes available on the column.

Even if you can't put everthing in one select, it's probably still faster to loop through a main cursor (for instance to get the users), and perform an insert..select for each of the rows of the cursor.