Hi Forums,
I have a frustrating problem on my Stored Procedure. It can only proccess
about 100 records in 10 minutes. I have 2 million initial records that need
to processed. Meaning that with this speed i will around 200 days to finish
all of them.
To make it worse, the data itself grows at least another 100 records per
hour.
Really appreciated if anybody can help to speed this up.
Rgds/Hardi
---
Here's the SP definition:
DELIMITER $$
DROP PROCEDURE IF EXISTS `hisdb`.`SP_HIS_NTMS_MSISDN_EDUM_REQ_PROVISIONING`
$$
CREATE PROCEDURE `SP_HIS_NTMS_MSISDN_EDUM_REQ_PROVISIONING`(
IN inMSISDN VARCHAR(23),
IN inIMEI VARCHAR(20),
IN inIMSI VARCHAR(20),
IN inPHONE_TYPE VARCHAR(100),
IN inIMEI_SOURCE SMALLINT(5),
IN inREQ_TIMESTAMP BIGINT(20))
BEGIN
DECLARE vIS_DELETED ENUM('Yes', 'No');
DECLARE vIMEI VARCHAR(20);
DECLARE vLAST_ACTIV_IMEI VARCHAR(20);
DECLARE vLAST_ACTIV_PHONE_TYPE VARCHAR(100);
DECLARE FLAG_LABEL INT DEFAULT 0;
DECLARE tmpLSTMODIF_TIME DATETIME;
DECLARE vNO_OF_IMEI_CHANGES SMALLINT(5);
DECLARE vNO_OF_TAC_CHANGES SMALLINT(5);
DECLARE stmt VARCHAR(200);
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
SET tmpLSTMODIF_TIME = null;
SELECT LSTMODIF_TIME INTO tmpLSTMODIF_TIME FROM HIS_MSISDN_IMEI WHERE MSISDN
= inMSISDN;
IF tmpLSTMODIF_TIME is null THEN
SET FLAG_LABEL = 1;
ELSE
BEGIN
IF (tmpLSTMODIF_TIME FROM_UNIXTIME(inREQ_TIMESTAMP)) THEN
SET FLAG_LABEL = 1;
ELSE
SET FLAG_LABEL = 2;
END IF;
END;
END IF;
SET vIS_DELETED = null;
SELECT IS_DELETED_FRM_NTMS INTO vIS_DELETED FROM HIS_MSISDN_IMEI_PROV_HIST
WHERE MSISDN = inMSISDN AND IMEI = inIMEI;
IF vIS_DELETED is not null THEN
BEGIN
IF vIS_DELETED = 'Yes' THEN
INSERT INTO HIS_MSISDN_IMEI_PROV_HIST(MSISDN, IMEI, IMSI, PHONE_TYPE,
PROV_SOURCE, PROV_TIMESTAMP, IS_DELETED_FRM_NTMS)
VALUES(inMSISDN, inIMEI, inIMSI, inPHONE_TYPE, inIMEI_SOURCE,
FROM_UNIXTIME(inREQ_TIMESTAMP), 'No');
END IF;
END;
ELSE
BEGIN
INSERT INTO HIS_MSISDN_IMEI_PROV_HIST(MSISDN, IMEI, IMSI, PHONE_TYPE,
PROV_SOURCE, PROV_TIMESTAMP, IS_DELETED_FRM_NTMS)
VALUES(inMSISDN, inIMEI, inIMSI, inPHONE_TYPE, inIMEI_SOURCE,
FROM_UNIXTIME(inREQ_TIMESTAMP), 'No');
END;
END IF;
IF (FLAG_LABEL = 1) THEN
BEGIN
SET vIMEI = null;
SELECT IMEI INTO vIMEI FROM HIS_MSISDN_IMEI WHERE MSISDN = inMSISDN;
IF vIMEI is not null THEN
BEGIN
IF vIMEI = inIMEI THEN
UPDATE HIS_MSISDN_IMEI SET LSTMODIF_TIME = FROM_UNIXTIME(inREQ_TIMESTAMP);
ELSE
BEGIN
SELECT COUNT(DISTINCT(IMEI)), COUNT(DISTINCT(SUBSTRING(IMEI, 1, 6))) INTO
vNO_OF_IMEI_CHANGES, vNO_OF_TAC_CHANGES
FROM HIS_MSISDN_IMEI_PROV_HIST WHERE MSISDN = inMSISDN;
UPDATE HIS_MSISDN_IMEI
SET IMEI = inIMEI, PHONE_TYPE = inPHONE_TYPE, LSTMODIF_TIME =
FROM_UNIXTIME(inREQ_TIMESTAMP), NO_OF_IMEI_CHANGES = vNO_OF_IMEI_CHANGES,
NO_OF_TAC_CHANGES = vNO_OF_TAC_CHANGES
WHERE MSISDN = inMSISDN;
END;
END IF;
END;
ELSE
BEGIN
SELECT COUNT(DISTINCT(IMEI)), COUNT(DISTINCT(SUBSTRING(IMEI, 1, 6))) INTO
vNO_OF_IMEI_CHANGES, vNO_OF_TAC_CHANGES
FROM HIS_MSISDN_IMEI_PROV_HIST WHERE MSISDN = inMSISDN;
INSERT INTO HIS_MSISDN_IMEI (MSISDN, IMEI, IMSI, PHONE_TYPE, LSTMODIF_TIME,
NO_OF_IMEI_CHANGES, NO_OF_TAC_CHANGES)
VALUES (inMSISDN, inIMEI, inIMSI, inPHONE_TYPE,
FROM_UNIXTIME(inREQ_TIMESTAMP), vNO_OF_IMEI_CHANGES, vNO_OF_TAC_CHANGES);
END;
END IF;
SET vLAST_ACTIV_IMEI = null;
SET vLAST_ACTIV_PHONE_TYPE = null;
SELECT ACTIV_IMEI, ACTIV_PHONE_TYPE INTO vLAST_ACTIV_IMEI,
vLAST_ACTIV_PHONE_TYPE FROM HIS_MSISDN_IMEI_ACTIV_HIST WHERE MSISDN =
inMSISDN ORDER BY ACTIV_TIME DESC LIMIT 1;
INSERT INTO HIS_MSISDN_IMEI_ACTIV_HIST (MSISDN, ACTIV_IMEI,
ACTIV_PHONE_TYPE, PREV_IMEI, PREV_PHONE_TYPE, ACTIV_TIME)
VALUES (inMSISDN, inIMEI, inPHONE_TYPE, vLAST_ACTIV_IMEI,
vLAST_ACTIV_PHONE_TYPE, FROM_UNIXTIME(inREQ_TIMESTAMP));
END;
END IF;
IF (FLAG_LABEL = 2) THEN
BEGIN
SET vLAST_ACTIV_IMEI = null;
SET vLAST_ACTIV_PHONE_TYPE = null;
SELECT ACTIV_IMEI, ACTIV_PHONE_TYPE INTO vLAST_ACTIV_IMEI,
vLAST_ACTIV_PHONE_TYPE
FROM HIS_MSISDN_IMEI_ACTIV_HIST WHERE MSISDN = inMSISDN AND ACTIV_TIME
FROM_UNIXTIME(inREQ_TIMESTAMP) ORDER BY ACTIV_TIME DESC LIMIT 1;
IF (vLAST_ACTIV_IMEI is null AND vLAST_ACTIV_PHONE_TYPE is null) THEN
BEGIN
IF EXISTS(SELECT MSISDN FROM HIS_MSISDN_IMEI_ACTIV_HIST WHERE MSISDN =
inMSISDN AND ACTIV_TIME FROM_UNIXTIME(inREQ_TIMESTAMP)) THEN
BEGIN
INSERT INTO HIS_MSISDN_IMEI_ACTIV_HIST (MSISDN, ACTIV_IMEI,
ACTIV_PHONE_TYPE, PREV_IMEI, PREV_PHONE_TYPE, ACTIV_TIME)
VALUES (inMSISDN, inIMEI, inPHONE_TYPE, NULL, NULL,
FROM_UNIXTIME(inREQ_TIMESTAMP));
UPDATE HIS_MSISDN_IMEI_ACTIV_HIST SET PREV_IMEI = inIMEI, PREV_PHONE_TYPE =
inPHONE_TYPE
WHERE MSISDN = inMSISDN AND ACTIV_TIME FROM_UNIXTIME(inREQ_TIMESTAMP)
ORDER BY ACTIV_TIME ASC LIMIT 1;
END;
ELSE
INSERT INTO HIS_MSISDN_IMEI_ACTIV_HIST (MSISDN, ACTIV_IMEI,
ACTIV_PHONE_TYPE, PREV_IMEI, PREV_PHONE_TYPE, ACTIV_TIME)
VALUES (inMSISDN, inIMEI, inPHONE_TYPE, NULL, NULL,
FROM_UNIXTIME(inREQ_TIMESTAMP));
END IF;
END;
ELSE
BEGIN
INSERT
