SYSTEM FOR ADDRESSING INEFFICIENT QUERY PROCESSING

• US 20080168058A1
• Filed: 03/20/2008
• Published: 07/10/2008
• Est. Priority Date: 12/24/2003
• Status: Abandoned Application

• Current Assignee: Mark R. Gordon
• Original Assignee: Mark R. Gordon
• Inventors: Gordon, Mark R.
• Application Number:
  US12/052,063
• Publication Number:
  US 20080168058A1
• Time in Patent Office: Days
• US Class Current: 707/5
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

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.

Claims

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.