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.

4
  • 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. Commented Sep 26, 2017 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 .... Commented Sep 26, 2017 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 Commented Sep 26, 2017 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, 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.... Commented Sep 26, 2017 at 10:59

1 Answer 1

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.

Sign up to request clarification or add additional context in comments.

4 Comments

I have tried this but it will not because in where clause there will be more than one user id and it will not get me result back.
That check can be removed from the where clause, because it's already implied in the inner join. Query modified.
Yup with a few modification it worked. Thank for the idea. please vote up.
Good to know. Fast enough too?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.