System for addressing inefficient query processing

Abstract

A system for analyzing predicate usage and proposing solutions to improve query performance. The system comprises a system for collecting performance statistics for database queries; an aggregation system that organizes the performance statistics based on predicate combinations used in the database queries; and an evaluation system that compares the performances of queries having similar predicate combinations. Further features include a problem identification system that identifies a predicate, which when added to a query, results in a low performing query, and a solution proposal system that automatically proposes a solution to address the low performing query.

1 . A query predicate analysis system comprising:
 a system for collecting performance statistics for database queries, wherein the database queries include statements submitted by end-users having different predicate combinations;   an aggregation system that organizes the performance statistics based on the different predicate combinations of the database queries; and   an evaluation system that analyzes performance of a subset of the database queries having different predicate combinations that include common predicates.   
   
   
       2 . The system of  claim 1 , further comprising a problem identification system that identifies a first predicate which when added to an identified database query results in a substantial degradation of database query performance. 
   
   
       3 . The system of  claim 2 , further comprising a solution proposal system that automatically proposes a solution to address the substantial degradation of database query performance. 
   
   
       4 . The system of  claim 3 , further comprising a feedback system for comparing the performance of the identified database query before and after an implementation of a proposed solution. 
   
   
       5 . The system of  claim 3 , further comprising a regression testing system for evaluating performance of a second subset of database queries referencing a changed predicate. 
   
   
       6 . The system of  claim 1 , wherein the performance statistics include: rows that satisfy the predicates, query elapsed time, index pages referenced, and rows processed. 
   
   
       7 . A computer program product comprising a computer usable medium having computer usable program code for analyzing query performance based on predicate usage, which when executed on a computer comprises:
 means for collecting performance statistics for database queries comprising database statements entered by end-users having different predicate combinations;   means for organizing the performance statistics based on the different predicate combinations of the database queries;   means for analyzing the performances of a subset of the database queries having different predicate combinations that include common predicates; and   means for generating analysis results.   
   
   
       8 . The computer program product of  claim 7 , wherein each predicate combination comprises a set of dimensions. 
   
   
       9 . The computer program product of  claim 7 , further comprising means for generating a performance improvement measure for each of a second subset of the database queries based on an order of the predicates. 
   
   
       10 . The computer program product of  claim 7 , further comprising means for proposing a solution to address a database query from the subset of database queries identified as a low performer. 
   
   
       11 . The computer program product of  claim 10 , further comprising means for comparing the performance of a database query from the subset of database queries before and after an implementation of a proposed solution. 
   
   
       12 . The computer program product of  claim 7 , wherein the performance statistics are selected from the group consisting of: rows that satisfy a set of predicates, query elapsed time, index pages referenced, and rows processed. 
   
   
       13 . A query predicate analysis system comprising:
 a system for collecting performance statistics for database queries, wherein the database queries include statements submitted by end-users having different predicate combinations;   an aggregation system that organizes the performance statistics based on the different predicate combinations of the database queries; and   an evaluation system that analyzes performance of a subset of the database queries having different predicate combinations that include common predicates, wherein the evaluation system calculates an estimate improvement for adding a predicate given by: Estimated improvement=Time−(((count/second with added predicate)/(count/second))*Time), where Time is a sum of time for database queries containing an added predicate, count/second is a rate for running a database query without the added predicate and count/second with added predicate is a rate for running the database query with the added predicate.   
   
   
       14 . A query predicate analysis system comprising:
 a system for collecting performance statistics for database queries, wherein the database queries include statements submitted by end-users having different predicate combinations;   an aggregation system that organizes the performance statistics based on the different predicate combinations of the database queries;   an evaluation system that analyzes performance of a subset of the database queries having different predicate combinations that include common predicates; and   a problem identification system that identifies a first predicate which when added to an identified database query results in a substantial degradation of database query performance, wherein the problem identification system selects a problem from a problem list including: missing statistics on an added column; missing index on a new column; missing multi-column index on a fact table; and the first predicate contains a non-indexable operator.