Re: VERY VERY SLOW PROCEDURE - SOMETIMES

[Graham White]

Hi Rick

below is the create statememnt for the main table. I know it's not a great layout but this is a proof of concept project seeing if very ealy notification of acute renal failure makes any difference to outcomes. Ideally I'd normalise the table but, for the sake of ease and debugging, it's easier like this at present until I get more coding time.

The version of MySQL is 5.5. and the workbench is 5.2.

This works fine on a laptop with around 1,000,000 records and a processing time of <1 second for a new record addition. On the work PC we are at 10 minutes per record.

Thanks

G



delimiter $$

CREATE TABLE `main` (
`LabNo` varchar(12) NOT NULL,
`Surname` varchar(20) NOT NULL,
`DoB` date NOT NULL,
`Forename` varchar(20) DEFAULT NULL,
`Sex` varchar(1) DEFAULT NULL,
`Source` varchar(10) DEFAULT NULL,
`Clinician` varchar(10) DEFAULT NULL,
`Sampled` datetime NOT NULL,
`Creat` int(11) NOT NULL,
`eGFR` int(11) DEFAULT NULL,
`Urea` decimal(5,2) DEFAULT NULL,
`Sodium` int(11) DEFAULT NULL,
`Albumin` int(11) DEFAULT NULL,
`CRP` int(11) DEFAULT NULL,
`KDIGO` int(11) DEFAULT NULL COMMENT 'Evaluation of the RIFLE criteria',
`AKIN` int(11) DEFAULT NULL COMMENT 'Evaluation of the AKIN criteria',
`AKIN7` int(11) DEFAULT NULL,
`AKIN30` int(11) DEFAULT NULL,
`AKIN90` int(11) DEFAULT NULL,
`Waikar` int(11) DEFAULT NULL COMMENT 'Evaluation of the Waikar criteria',
`NHSNo` varchar(12) DEFAULT NULL COMMENT 'Assigned NHS number',
`HospNo` varchar(10) DEFAULT NULL COMMENT 'Assigned hospital number',
`To_Output` tinyint(1) DEFAULT '0' COMMENT 'Set to True if the request has a comment that has not yet been output',
`Processed` tinyint(1) DEFAULT '0' COMMENT 'Set to TRUE once the record has been evaluated. Set to FALSE to reevaluate',
`Difference` int(11) DEFAULT NULL COMMENT 'Difference between the current value and the immediate previous value',
`CKD` tinyint(1) DEFAULT '0' COMMENT 'Set to TRUE if the highest previous eGFR in last year was < 60',
`AKIComment` varchar(5) DEFAULT NULL COMMENT 'The comment output based on AKIN and CKD flags',
PRIMARY KEY (`LabNo`),
UNIQUE KEY `idxLabNo` (`LabNo`),
KEY `idxAKIStage` (`AKIN`),
KEY `idxCreat` (`Creat`),
KEY `idxDoB` (`DoB`),
KEY `idxNameDoB` (`Surname`,`DoB`),
KEY `idxProcessed` (`Processed`),
KEY `idxSampled` (`Sampled`),
KEY `idxSurname` (`Surname`),
KEY `idxTo_Output` (`To_Output`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=165$$

delimiter $$


This is the main procedure which then calls other procedures to calculate outcomes as required. I've not listed everything for clarity.

Thanks

G
CREATE DEFINER=``@`` PROCEDURE `Do_Evaluation`(IN o_LabNo VARCHAR(12))
COMMENT 'Perform all evaluations on the given lab number'
BEGIN

DECLARE o_Count INT DEFAULT 0;
DECLARE o_Surname VARCHAR(20);
DECLARE o_DoB DATE;
DECLARE o_Sampled DATETIME;
DECLARE o_Creat INT;
DECLARE o_eGFR INT;
DECLARE o_Sex VARCHAR(1);

DECLARE o_PrevCreat_1 INT;
DECLARE o_PrevCreat_2 INT;
DECLARE o_PrevCreat_7 INT;
DECLARE o_PrevCreat_30 INT;
DECLARE o_PrevCreat_90 INT;
DECLARE o_DeltaCreat INT;
DECLARE o_PrevGFR INT;

SELECT Surname
, DoB
, Sampled
, Creat
, Sex
, eGFR
INTO
o_Surname, o_DoB, o_Sampled, o_Creat, o_Sex, o_eGFR
FROM
Main
WHERE
LabNo = o_LabNo;

CALL Do_eGFR(o_Creat, o_DoB, o_Sampled, o_Sex, o_LabNo); -- calculate eGFR on current request

CALL GetPreviousCreat(o_PrevCreat_1, o_Surname, o_DoB, o_Sampled, -1);
CALL GetPreviousCreat(o_PrevCreat_2, o_Surname, o_DoB, o_Sampled, -2);
CALL GetPreviousCreat(o_PrevCreat_7, o_Surname, o_DoB, o_Sampled, -7);
CALL GetPreviousCreat(o_PrevCreat_30, o_Surname, o_DoB, o_Sampled, -30);
CALL GetPreviousCreat(o_PrevCreat_90, o_Surname, o_DoB, o_Sampled, -90);
CALL PreviousCreatinine(o_Surname, o_DoB, o_Sampled, o_DeltaCreat);
CALL GetPreviouseGFR(o_PrevGFR, o_Surname, o_DoB, o_Sampled, -7);

CALL Do_Delta_Difference(o_LabNo, o_Creat, o_DeltaCreat);
CALL Do_RIFLE_Stage(o_PrevCreat_2, o_PrevCreat_7, o_Creat, o_eGFR, o_PrevGFR, o_LabNo);
CALL Do_AKIN2(o_LabNo, o_Creat, o_PrevCreat_2);
CALL Do_AKIN7(o_LabNo, o_Creat, o_PrevCreat_7);
CALL Do_AKIN30(o_LabNo, o_Creat, o_PrevCreat_30);
CALL Do_AKIN90(o_LabNo, o_Creat, o_PrevCreat_90);

CALL Do_Waikar(o_Creat, o_PrevCreat_1, o_PrevCreat_2, o_LabNo);
CALL IS_CKD(o_Surname, o_DoB, o_Sampled, o_LabNo);
CALL Do_AKIComment(o_LabNo);

CALL IsProcessed(o_LabNo);
END$$

delimiter $$

CREATE DEFINER=``@`` PROCEDURE `GetPreviousCreatinine`(IN i_LabNo VARCHAR(10),
IN i_UniqueID INT,
IN i_SampDate DATE,
IN i_Days INT,
OUT i_Creat INT
)
BEGIN
SELECT Result
INTO
i_Creat
FROM
Requests
INNER JOIN Results
ON requests.Unique_ID = Results.Unique_ID
WHERE
requests.Unique_ID = i_UniqueID
AND results.assay = 'Creatinine'
AND Requests.Sample_Date BETWEEN i_SampDate - INTERVAL 1 SECOND
AND i_SampDate - INTERVAL i_Days DAY
ORDER BY
Requests.Sample_Date DESC
LIMIT
1;
END$$