0

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.

Dashanan
  • 122
  • 3
  • 16
  • 2
    Have you ever tried indenting your code? It would make it way more easy to read. Anyway, the version with the while loop is creating and dropping temporary tables inside the loop. I'm not surprised that it's slower. I'm actually surprised that it's only 2 times as slow. – GolezTrol Sep 26 '17 at 10:46
  • 2
    By far the fastest way would be to build a single select statement with everything you want to have, and insert the value using `insert into ut_userstask(...) select (...) from ...`. – GolezTrol Sep 26 '17 at 10:49
  • @GolezTrol there are multiple user and multiple tasks for each i have to insert all tasks. so it cant be stored in a table coz i thing it would be a mess there. That is why i have used cursor – Dashanan Sep 26 '17 at 10:52
  • It's not really clear to my why you can't, apart maybe from the `limit target`, which seems like you want to have a (different?) limit per user. There are [ways to solve that](https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group), and I think it's possible to join the three selects into one select and use that select in a single insert statement. I'm a bit reluctant to post it, since I'm not sure if I haven't missed anything.... – GolezTrol Sep 26 '17 at 10:59

1 Answers1

1

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.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210