0
\$\begingroup\$

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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.