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;