Slow Stored Procedure

[hahahar dididi]

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 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));

IF EXISTS(SELECT MSISDN FROM HIS_MSISDN_IMEI_ACTIV_HIST WHERE MSISDN = inMSISDN AND ACTIV_TIME > FROM_UNIXTIME(inREQ_TIMESTAMP)) THEN
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 IF;
END;

END IF;

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 NO_OF_IMEI_CHANGES = vNO_OF_IMEI_CHANGES, NO_OF_TAC_CHANGES = vNO_OF_TAC_CHANGES WHERE MSISDN = inMSISDN;

END;
END IF;

#COMMIT;
#DELETE FROM HIS_NTMS_MSISDN_EDUM_REQ WHERE MSISDN = inMSISDN AND IMEI = inIMEI AND REQ_TIMESTAMP = inREQ_TIMESTAMP;


END $$

DELIMITER ;

------------------------------------------------------------------

And these are the tables involved:

#
# Table structure for table his_msisdn_imei
#

CREATE TABLE `his_msisdn_imei` (
`MSISDN` varchar(23) NOT NULL,
`IMEI` varchar(20) NOT NULL,
`IMSI` varchar(20) default NULL,
`PHONE_TYPE` varchar(100) NOT NULL,
`LSTMODIF_TIME` datetime NOT NULL,
`NO_OF_IMEI_CHANGES` smallint(5) unsigned NOT NULL default '0',
`NO_OF_TAC_CHANGES` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`MSISDN`,`IMEI`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


#
# Table structure for table his_msisdn_imei_activ_hist
#

CREATE TABLE `his_msisdn_imei_activ_hist` (
`MSISDN` varchar(23) NOT NULL,
`ACTIV_IMEI` varchar(20) NOT NULL,
`ACTIV_PHONE_TYPE` varchar(100) NOT NULL,
`PREV_IMEI` varchar(20) default NULL,
`PREV_PHONE_TYPE` varchar(100) default NULL,
`ACTIV_TIME` datetime NOT NULL,
PRIMARY KEY (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


#
# Table structure for table his_msisdn_imei_prov_hist
#

CREATE TABLE `his_msisdn_imei_prov_hist` (
`MSISDN` varchar(23) NOT NULL,
`IMEI` varchar(20) NOT NULL,
`IMSI` varchar(20) default NULL,
`PHONE_TYPE` varchar(100) NOT NULL,
`PROV_SOURCE` smallint(5) unsigned default NULL,
`PROV_TIMESTAMP` datetime NOT NULL,
`IS_DELETED_FRM_NTMS` enum('No','Yes') NOT NULL default 'No',
PRIMARY KEY (`MSISDN`,`IMEI`,`PROV_TIMESTAMP`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;