Speed Up the Stored Procedure execution time MySQL

Question

I have a stored-procedure which performs some select query with join and and all, on data from few tables and then some process on those data and then insert the output to a new table.Whole task takes 3 minutes to complete for 1000 users data.But problem is for around 1 million users it will take ages to complete. Also its a daily routine process.Need suggestion to what process should I follow to reduce the time.

Here is the Procedure

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Finalest`()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DELETE FROM ercot_customer_forecast;
SELECT COUNT(*) FROM ercot_temp_esi_id where meter_cycle is not NULL INTO n;
SET i=0;
WHILE i<n DO
select  esi_id,meter_cycle
    into  @esiId,@billingCycle
    from  ercot_temp_esi_id
    where  meter_cycle is not NULL
    LIMIT  i,1;
select  zone,weather_station
    into  @zone, @weatherStation
    from  ercot_customer_zipcode_mapping
    where  esi_id=@esiId;
set @query=CONCAT('
        select  count(scaling_factor),tdsp_duns,consumer_oid,source_system_oid,
                site_oid,profile_type
            into  @numberOfMonth,@tdspDuns,@consumerOid,@ssOid,@siteOid,
                @profileType
            from  ercot_customer',@billingCycle,'
            where  esi_id=@esiId'
                 );
PREPARE stmt1 FROM  @query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SELECT  RIGHT(read_date,5)
    into  @currentDate
    from  ercot_cycle_mapping
    where  month=MONTHNAME(CURRENT_DATE())
      and  tdsp_duns=@tdspDuns
      and  billing_cycle=@billingCycle;
SELECT  RIGHT(read_date,5)
    into  @promptDate
    from  ercot_cycle_mapping
    where  month=MONTHNAME(Date_add(CURRENT_DATE(), interval 1 month))
      and  tdsp_duns=@tdspDuns
      and  billing_cycle=@billingCycle;
set @query=CONCAT('
        SELECT  scaling_factor
            into  @scalingFactorCurrent
            from  ercot_customer',@billingCycle,'
            where  esi_id=@esiId
              and  RIGHT(service_end,5)=@currentDate'
                 );
PREPARE stmt1  FROM  @query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
set @query=CONCAT('
        SELECT  scaling_factor
            into  @scalingFactorPrompt
            from  ercot_customer',@billingCycle,'
            where  esi_id=@esiId
              and  RIGHT(service_end,5)=@promptDate'
                 );
PREPARE stmt1 FROM  @query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
set @query=CONCAT('
        SELECT  case when @scalingFactorCurrent IS NULL
              or  @scalingFactorCurrent = \'\' then 
            (
                SELECT  sum(scaling_factor)/@numberOfMonths
                    from  ercot_customer',@billingCycle,'
                    where  esi_id=@esiId
            ) else @scalingFactorCurrent end
            into  @scalingFactorCurrent'
                 );
PREPARE stmt1 FROM  @query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
set @query=CONCAT('
        SELECT  case when @scalingFactorPrompt IS NULL
              or  @scalingFactorPrompt = \'\' then 
            (
                SELECT  sum(scaling_factor)/@numberOfMonths
                    from  ercot_customer', @billingCycle, '
                    where  esi_id=@esiId
            ) else @scalingFactorPrompt end
            into  @scalingFactorPrompt;'
                 );
PREPARE stmt1 FROM  @query;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
set @query=CONCAT('
        INSERT
             INTO  ercot_customer_forecast (esi_id, consumer_oid, source_system_oid,
                        site_oid, date, final_usage, measurement_unit, gen_date
                          ) 
        select  @esiId,@consumerOid, @ssOid, @siteOid, date,
                sum(profile_usage)*(CASE MONTHNAME(date) WHEN MONTHNAME(CURRENT_DATE()) THEN @scalingFactorCurrent WHEN MONTHNAME(Date_add(CURRENT_DATE(),
                                        interval 1 month)) THEN @scalingFactorPrompt END
                   ),','\'KWH\',
                                        ', curdate(),' from  forecastERCOT_',
                @zone,' where  weather_station=@weatherStation
              and  profile_type=@profileType
            group by  date'
                 );
PREPARE stmt1 FROM @query; EXECUTE stmt1; DEALLOCATE PREPARE stmt1;
SET i = i + 1;
END While;
END$$
DELIMITER ;

Update:- I am just asking that how to optimize this stored procedure.

Answer 1

LIMIT i,1 -- smacks of non-SQL coding. This will get slower and slower. And, since there no ORDER BY, it is unpredictable. And, without a transaction, someone could slip in and mess up the OFFSET, leading to duplicated or skipped rows. See my pagination blog

SELECT ... INTO @esiId ...;
SELECT ... WHERE ... = @esiId ...;

Use JOIN to do both SELECTs at the same time. (There may be other instances of this.)

COUNT(x) checks x for being null. The usual pattern (if you don't need to check for NULL) is COUNT(*).

Perhaps you are missing some useful 'composite' indexes; let's see SHOW CREATE TABLE for each table.

Ouch! from ercot_customer',@billingCycle,' -- You have just encountered one of the pains of having multiple tables instead of putting all the data in a single table. (This is a common question on this forum.)

That is just touching the surface. Come back (with a fresh Question) if you still have problems after fixing what you can.