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.
Best Answer
LIMIT i,1
-- smacks of non-SQL coding. This will get slower and slower. And, since there noORDER BY
, it is unpredictable. And, without a transaction, someone could slip in and mess up theOFFSET
, leading to duplicated or skipped rows. See my pagination blogUse
JOIN
to do bothSELECTs
at the same time. (There may be other instances of this.)COUNT(x)
checksx
for being null. The usual pattern (if you don't need to check forNULL
) isCOUNT(*)
.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.