0

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.

0

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.

  • Also can you confirm that removing the while loop and replacing it with single Insert query, speed up the process ? – imagngames Feb 9 '17 at 3:37
  • Thank you Rick. With indexes I am able to reduce the time form 3 minutes to less then 1 minutes. – imagngames Feb 11 '17 at 16:31
  • "Confirm"? No. But it have been my considerable experience that it helps more than the 3:1 you got from just the index change. I have measured that one INSERT with 100 rows runs 10 times as fast as 100 single-row INSERTs. – Rick James Feb 11 '17 at 17:29
  • Yes I am still working on stored procedure to remove that loop . Will let you know . Thanks – imagngames Feb 12 '17 at 15:49
  • Forget to update here.After removing the loop procedure took only 3 seconds to complete. Thanks again. Now I am to write more efficient procedure. – imagngames Jul 12 '17 at 9:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.