# Optimize slow SQL stored procedure that calculate Test Case failures in a non-optimal schema

I need to heavily optimize the following sql stored procedure (MySQL 5.6). Requirements:

• Should be as fast as possible (this is a key)
• Lots of records (10 M now, possibly more)
• Stored procedure should allow any custom filtering, including on the tables that are not part of this question. Custom filtering filters only Test Results (see further), but with any rules (for example filter those test runs that happen during this time, on this machine etc.)

There are Test Results, each contains Test Case Results. Each Test Case Result corresponds to a Test Case that ran during a test. Sometimes Test Casefails and sometimes it can be repeated producing several Test Case Results for a single Test Case in a single Test Result. In such cases field RetryRun is increased with each try, meaning that the maximum RetryRun in each group of Test Result and Test Case is the only one significant. If one Test Case fails several times, further Test Cases usually are not executed (in fact they can, but only in debug scenario and we do not care much about that)

Produce the query that shows for each Test Case how many times it has caused a failure of Test Result and how many times each Test Case ran totally.

The database schema is not under review. It has issues, but it is long established now and cannot be changed.

Of course, I'm writing only important for the question fields and tables here.

Tables:

CREATE TABLE testresult (
TestResultPK int(11) NOT NULL AUTO_INCREMENT,
Passed tinyint(1) NOT NULL,
CreatedDate datetime DEFAULT NULL,
PRIMARY KEY (TestResultPK),
UNIQUE KEY TestResultPK (TestResultPK),
KEY CreatedDateIndex (CreatedDate)
) ENGINE=InnoDB AUTO_INCREMENT=94755 DEFAULT CHARSET=latin1;
CREATE TABLE testcasedataresult (
TestCaseDataResultPK int(11) NOT NULL AUTO_INCREMENT,
Passed tinyint(1) DEFAULT NULL,
RetryRun int(11) DEFAULT NULL,
TestCaseDataFK int(11) DEFAULT NULL,
TestResultFK int(11) DEFAULT NULL,
PRIMARY KEY (TestCaseDataResultPK),
UNIQUE KEY TestCaseDataResultPK (TestCaseDataResultPK),
KEY TestCaseDataFK (TestCaseDataFK),
KEY TestResultFK (TestResultFK),
#CONSTRAINT FK469417F32EBE75FE FOREIGN KEY (TestCaseDataFK) REFERENCES testcasedata (TestCaseDataPK),
CONSTRAINT FK469417F3C87DE8C6 FOREIGN KEY (TestResultFK) REFERENCES testresult (TestResultPK)
) ENGINE=InnoDB AUTO_INCREMENT=20193325 DEFAULT CHARSET=latin1;


Please note that Test Case Result does not have a time stamp. So in normal circumstances, the Test Case Result that has failed the Test is the one that has Passed = 0 and maximum RetryRun (there is no record of the same Test Case that is Passed = 1 and has bigger RetryRun for the given Test Result).

My procedure:

DELIMITER $$CREATE DEFINER=system@% PROCEDURE failedtestcasestatistics(in trWhere LONGTEXT) BEGIN DROP TABLE if EXISTS failedtcdr_foreach_tr; DROP TABLE if EXISTS alltcdr; SET @failedtcdrSql = CONCAT(' create temporary table failedtcdr_foreach_tr select tcdr.TestResultFK, max(tcdr.TestCaseDataResultPK) MaxTestCaseDataResultPK from testcasedataresult tcdr, testresult tr, (', trWhere, ') trWhereTable where tcdr.TestResultFK = tr.TestResultPK and tr.Passed = 0 and trWhereTable.TestResultPK = tcdr.TestResultFK group by tcdr.TestResultFK'); PREPARE failedTcdrStmt FROM @failedtcdrSql; EXECUTE failedTcdrStmt; DEALLOCATE PREPARE failedTcdrStmt; SET @alltcdrSql = CONCAT(' create temporary table alltcdr select tcdr.TestCaseDataFK as TestCaseDataFK, count(*) as total from testcasedataresult tcdr, (', trWhere, ') trWhereTable where tcdr.TestResultFK = trWhereTable.TestResultPK group by tcdr.TestCaseDataFK '); PREPARE allTcdrStmt FROM @alltcdrSql; EXECUTE allTcdrStmt; DEALLOCATE PREPARE allTcdrStmt; #select * from alltcdr; select failedGroup.TestCaseDataFK, failedGroup.failures, allt.total as total from (select tcdr.TestCaseDataFK TestCaseDataFK, count(*) failures from failedtcdr_foreach_tr trs, testcasedataresult tcdr where trs.MaxTestCaseDataResultPK = tcdr.TestCaseDataResultPK group by tcdr.TestCaseDataFK) failedGroup, alltcdr as allt where failedGroup.TestCaseDataFK = allt.TestCaseDataFK; DROP TABLE failedtcdr_foreach_tr; DROP TABLE alltcdr; END$$
DELIMITER ;


How do I call it:

CALL failedtestcasestatistics('select tr.TestResultPK from testresult tr');


I'm using a trick with PREPARE stmt and CONCAT to be able to apply any custom filtering on the stored procedure

How can I optimize the procedure?

• Are you saving program test results into a database? – dustytrash Apr 3 at 12:52
• @dustytrash The stored procedure should be executed from the external code – Archeg Apr 3 at 12:56