title: Stored procedure using cursor in mySql
body: <p>I wrote a stored procedure using cursor in mysql but that procedure is taking 10 second to fetch the result while that result set have only 450 records so, I want to know that why that proedure is taking that much time to fetch tha record.</p>
  
  <p>procedure as below:</p>
  
  <pre><code>DELIMITER //
  DROP PROCEDURE IF EXISTS curdemo123//
  
  CREATE PROCEDURE curdemo123(IN Branchcode int,IN vYear int,IN vMonth int)
     BEGIN
        DECLARE  EndOfData,tempamount INT DEFAULT 0;
        DECLARE tempagent_code,tempplantype,tempsaledate CHAR(12);
        DECLARE tempspot_rate DOUBLE;
        DECLARE var1,totalrow INT DEFAULT 1;
        DECLARE cur1 CURSOR FOR 
                select SQL_CALC_FOUND_ROWS ad.agentCode
                     , ad.planType
                     , ad.amount
                     , ad.date 
                  from adplan_detailstbl ad 
                 where ad.branchCode=Branchcode 
                   and (    ad.date between '2009-12-1' 
                                        and '2009-12-31')
                 order by ad.NUM_ID asc;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET EndOfData = 1;
        DROP TEMPORARY TABLE IF EXISTS temptable;
  
        CREATE TEMPORARY TABLE temptable (
               agent_code varchar(15)
             , plan_type char(12)
             , sale double
             , spot_rate double default '0.0', dATE DATE
        );
  
        OPEN cur1;
        SET totalrow=FOUND_ROWS();
        while var1 &lt;= totalrow DO
           fetch cur1 into tempagent_code
                          ,tempplantype
                          ,tempamount
                          ,tempsaledate;
  
           IF( (    tempplantype='Unit Plan' 
                 OR tempplantype='MIP'
               ) OR tempplantype='STUP') THEN
  
              select spotRate into tempspot_rate 
                from spot_amount 
                where ((    monthCode=vMonth 
                        and year=vYear) 
                      and (     (     agentCode=tempagent_code 
                                  and branchCode=Branchcode) 
                            and (planType=tempplantype)
                ));
  
              INSERT INTO temptable VALUES(
                  tempagent_code
                 ,tempplantype
                 ,tempamount
                 ,tempspot_rate
                 ,tempsaledate)
             ;
        else
           INSERT INTO temptable(
                agent_code
               ,plan_type
               ,sale,dATE) 
           VALUES( tempagent_code,tempplantype,tempamount,tempsaledate);
        END IF;
  
        SET var1=var1+1;
  END WHILE;
  CLOSE cur1;
  select * from temptable;
  DROP TABLE  temptable;
  END //
  DELIMITER ;
  </code></pre>