Stored Procedure is taking more time to execute

Question

I am executing a stored procedure im MYSQL. That will generate 6000 records and insert that 6000 records into a Table. For this, that stored procedure is taking more than 45 min.The file size of stored procedure is 45kb. Systems RAM is 0.98GB. Does system RAM affecting the stored procedure performance?

CREATE DEFINER=`root`@`localhost` PROCEDURE `sample`()
BEGIN

Declare FY_LHS Varchar(20); 
Declare FY_RHS Varchar(20); 

Declare abc int default 0; 
Declare bcd varchar(40) default null; 
Declare cde int default 0; 
Declare def varchar(40) default null; 
Declare efg int default 0; 
Declare ghi varchar(40) default null; 
Declare ijk varchar(40) default null;
Declare hij varchar(40) default null;
Declare ijk varchar(40) default null;
Declare param_month int ;


Declare monthsortsequence int default 0; 
Declare p int default 0;
Declare q int default 0; 
Declare r int default 0; 
Declare s int default 0; 
Declare t int default 0;  



DECLARE done INT DEFAULT FALSE;
declare dateofdisch int default 0;
Declare fId int default 0; 
DECLARE fac_cur CURSOR FOR SELECT r FROM u ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

SET param_month = Month(CURDATE());

IF param_month = 4 THEN SET monthsortsequence = 1;
ELSEIF param_month = 5 THEN SET monthsortsequence = 2;
ELSEIF param_month = 6 THEN SET monthsortsequence = 3;
ELSEIF param_month = 7 THEN SET monthsortsequence = 4;
ELSEIF param_month = 8 THEN SET monthsortsequence = 5;
ELSEIF param_month = 9 THEN SET monthsortsequence = 6;
ELSEIF param_month = 10 THEN SET monthsortsequence = 7;
ELSEIF param_month = 11 THEN SET monthsortsequence = 8;
ELSEIF param_month = 12 THEN SET monthsortsequence = 9;
ELSEIF param_month = 1 THEN SET monthsortsequence = 10;
ELSEIF param_month = 2 THEN SET monthsortsequence = 11;
ELSEIF param_month = 3 THEN SET monthsortsequence = 12;
END IF;

  OPEN fac_cur;

  read_loop: LOOP
    FETCH fac_cur INTO fId;
    IF fId >= 65 THEN
      LEAVE read_loop;
    END IF;
    IF fId != 0 THEN

IF MONTH(CURDATE())>=4 THEN
         Set FY_LHS = YEAR(CURDATE());
        Set FY_RHS = YEAR(CURDATE())+1;
ELSE 
        Set FY_LHS = YEAR(CURDATE())-1;
        Set FY_RHS = YEAR(CURDATE());
        END IF;


Select tblbc.StateId, tblst.StateName, tblbc.DistrictId, 
tblst.District, tblbc.kID, tblst.k, tblbc.FacilityName, tblbc.FacilityType, 
tblbc.ReportingToId  
into abc, bcd, cde, def, efg, ghi, ijk, hij, ijk  
from u tblbc inner join tblstateinfo tblst on 
tblbc.stateid = tblst.stateid and tblbc.DistrictId = tblst.dcode and tblbc.kID = tblst.tcode 
where tblbc.r = fId;

Delete from data where Year = concat(FY_LHS,'-',FY_RHS) and Month = param_month and r = fId;

Set p = (select count(*) as Count from w tblreg inner join tblp1s tblu on tblreg.p1id = tblu.p1id where tblreg.pm  = 0 and (Str_To_Date(tblreg.ty, '%d/%m/%Y') between Str_To_Date(concat(FY_LHS,'/04/01'), '%Y/%m/%d') and Str_To_Date(concat(FY_RHS,'/03/31'), '%Y/%m/%d')) and month(Str_To_Date(tblreg.ty, '%d/%m/%Y')) = param_month and tblu.r = fId); 
Set q = (Select count(ytid) as Count From w reg inner join tblp1s tblu on reg.p1id = tblu.p1Id where datediff(CurDate(), Str_To_Date(reg.LMP, '%d/%m/%Y')) < 300 and reg.pm = 0 and (Str_To_Date(reg.ty, '%d/%m/%Y') between Str_To_Date(concat(FY_LHS,'/04/01'), '%Y/%m/%d') and Str_To_Date(concat(FY_RHS,'/03/31'), '%Y/%m/%d')) and month(Str_To_Date(reg.ty, '%d/%m/%Y')) = param_month and tblu.r = fId); 
Set r = (Select count(reg.ytid) from (Select ts.p1id, ts.ytid, count(ts.ytid) as count from tblhjlist ts where ts.fgtype = 'ANC' and ts.actualdateofaction is not null and (Str_To_Date(actualdateofaction, '%d/%m/%Y') between Str_To_Date(concat(FY_LHS,'/04/01'), '%Y/%m/%d') and Str_To_Date(concat(FY_RHS,'/03/31'), '%Y/%m/%d')) and month(Str_To_Date(actualdateofaction, '%d/%m/%Y')) = param_month group by ts.ytid, ts.p1id) tss left join w reg on tss.ytid = reg.ytid inner join tblp1s tblu on reg.p1Id = tblu.p1id where tss.count = 3 and tblu.r = fId); 
Set s = (Select count(sl.ytid) from (Select p1id, ytid from tblhjlist where fgid = 1 and fgtype = 'FT' and actualdateofaction is not null and (Str_To_Date(actualdateofaction, '%d/%m/%Y') between Str_To_Date(concat(FY_LHS,'/04/01'), '%Y/%m/%d') and Str_To_Date(concat(FY_RHS,'/03/31'), '%Y/%m/%d')) and month(Str_To_Date(actualdateofaction, '%d/%m/%Y')) = param_month) sl inner join w reg on sl.ytid = reg.ytid inner join tblp1s tblu on reg.p1id = tblu.p1id and reg.pm = 0 and  tblu.r = fId); 
Set t = (Select count(ts.ytid) from (Select p1id, ytid from tblhjlist where ((fgtype = 'FT' and fgid = 2) or (fgtype = 'FTBooster' and fgid = 3)) and actualdateofaction is not null and (Str_To_Date(actualdateofaction, '%d/%m/%Y') between Str_To_Date(concat(FY_LHS,'/04/01'), '%Y/%m/%d') and Str_To_Date(concat(FY_RHS,'/03/31'), '%Y/%m/%d')) and month(Str_To_Date(actualdateofaction, '%d/%m/%Y')) = 9 group by ytid) ts inner join w reg on ts.ytid = reg.ytid inner join tblp1s tblu on reg.p1Id = tblu.p1Id and reg.pm = 0 and tblu.r = fId); 
.
.
.
.
.
40 queries

insert into data (year, month, stateId, state, districtId, district, kid, k, facilityname, facilityType, sortsequenceMonth, r, sortsequence, itemRefno, itemvalue, ReportingToId) values (concat(FY_LHS,'-',FY_RHS), param_month, abc, bcd, cde, def, efg, ghi, ijk, hij, monthsortsequence, fId, 2, '1.1.1', q, ijk);
insert into data (year, month, stateId, state, districtId, district, kid, k, facilityname, facilityType, sortsequenceMonth, r, sortsequence, itemRefno, itemvalue, ReportingToId) values (concat(FY_LHS,'-',FY_RHS), param_month, abc, bcd, cde, def, efg, ghi, ijk, hij, monthsortsequence, fId, 1, '1.1', p, ijk);
insert into data (year, month, stateId, state, districtId, district, kid, k, facilityname, facilityType, sortsequenceMonth, r, sortsequence, itemRefno, itemvalue, ReportingToId) values (concat(FY_LHS,'-',FY_RHS), param_month, abc, bcd, cde, def, efg, ghi, ijk, hij, monthsortsequence, fId, 3, '1.3', r, ijk);
insert into data (year, month, stateId, state, districtId, district, kid, k, facilityname, facilityType, sortsequenceMonth, r, sortsequence, itemRefno, itemvalue, ReportingToId) values (concat(FY_LHS,'-',FY_RHS), param_month, abc, bcd, cde, def, efg, ghi, ijk, hij, monthsortsequence, fId, 4, '1.4.1', s, ijk);
insert into data (year, month, stateId, state, districtId, district, kid, k, facilityname, facilityType, sortsequenceMonth, r, sortsequence, itemRefno, itemvalue, ReportingToId) values (concat(FY_LHS,'-',FY_RHS), param_month, abc, bcd, cde, def, efg, ghi, ijk, hij, monthsortsequence, fId, 5, '1.4.2', t, ijk);
.
.
.
.
40 insert statements

set fId = fId + 1;

 END IF;
  END LOOP;

CLOSE fac_cur;
END

Hi Jordan,that is not the complete code....it looks exactly like that — Teja, Jun 29 '16 at 05:57
That's a lot of inserts you might want to google mysql insert limit and have a look at http://stackoverflow.com/questions/3536103/mysql-how-many-rows-can-i-insert-in-one-single-insert-statement — P.Salmon, Jun 29 '16 at 06:30

score -1 · Answer 1 · edited Jun 29 '16 at 06:59

-1

You need to check which statement exactly in the stored procedure is taking time by adding some debug points (ex: You can insert timestamp after each select into some temp table).
You need to check that you have proper indexes on table for select queries.

edited Jun 29 '16 at 06:59

Nazik

8,696
27
77
123

answered Jun 29 '16 at 06:21

Indira

41
2
7

Stored Procedure is taking more time to execute

1 Answers1