Methods and systems for determining regression of a database query

A system for determining regression of a database query associated with a first execution plan, the system comprising:

• a computer system having a processor coupled to a memory;
• and
• a data repository coupled to the computer system,
• wherein the processor is configured to:
• receive a first performance measure for the database query and the first execution plan;
• determine a presence of at least one stored performance measure for the database query in the data repository;
• in response to a determination that the at least one stored performance measure for the database query is for a different execution plan than the first execution plan:
• add the first performance measure and identifiers for the database query and the first execution plan to a synchronization list,
• retrieve a repository list including identifiers for one or more third execution plans and third performance measures associated with the database query from the data repository, and
• add the identifier for the first execution plan and the first performance measure from the synchronization list to the repository list in association with the database query;
• in response to a determination that at least one stored performance measure for the database query is present in the data repository:
• retrieve a second performance measure associated with a second execution plan from the data repository;
• calculate a difference between the first performance measure and the second performance measure;
• multiply the calculated difference by a number of executions of the first execution plan to generate the regression of the database query in a first unit of measure;
• convert the regression of the database query into a different unit of measure that corresponds to a unit of measure of the first performance measure and the second performance measure;
• and
• analyze the converted regression to determine whether the database query is regressed;
• and
• in response to a determination that the database query is regressed, add the first performance measure, the second performance measure, and identifiers for the database query and the first execution plan to a regression report.

A non-transitory computer-readable medium having software encoded thereon, the software, when executed by one or more computer systems coupled to a data repository, operable to:

• receive a first performance measure for a database query and a first execution plan associated with the database query;
• determine a presence of at least one stored performance measure for the database query in the data repository;
• in response to a determination that the at least one stored performance measure for the database query is for a different execution plan than the first execution plan:
• add the first performance measure and identifiers for the database query and the first execution plan to a synchronization list,
• retrieve a repository list including identifiers for one or more third execution plans and third performance measures associated with the database query from the data repository, and
• add the identifier for the first execution plan and the first performance measure from the synchronization list to the repository list in association with the database query;
• in response to a determination that at least one stored performance measure for the database query is present in the data repository:
• retrieve a second performance measure associated with a second execution plan from the data repository;
• calculate a difference between the first performance measure and the second performance measure;
• multiply the calculated difference by a number of executions of the first execution plan to generate the regression of the database query in a first unit of measure;
• convert the regression of the database query into a different unit of measure that corresponds to a unit of measure of the first performance measure and the second performance measure;
• and
• analyze the converted regression to determine whether the database query is regressed;
• and
• in response to a determination that the database query is regressed, add the first performance measure, the second performance measure, and identifiers for the database query and the first execution plan to a regression report.