System and method for addressing inefficient query processing

• US 20050165741A1
• Filed: 12/24/2003
• Published: 07/28/2005
• Est. Priority Date: 12/24/2003
• Status: Active Grant

• Current Assignee: Meta Platforms, Inc. (f/k/a Facebook, Inc.)
• Original Assignee: International Business Machines SA (International Business Machines Corporation)
• Inventors: Gordon, Mark R.
• Granted Patent: US 7,376,638 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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 and method 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;

an aggregation system that organizes the performance statistics based on predicate combinations of the database queries; and

an evaluation system that analyzes performance of queries having similar predicate combinations.

2. The system of claim 1, further comprising a problem identification system that identifies a predicate which when added to a query results in a low performing query.

3. The system of claim 2, further comprising a solution proposal system that automatically proposes a solution to address the low performing query.

4. The system of claim 3, further comprising a feedback system for comparing the performance of a 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 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. The system of claim 1, wherein the evaluation system calculates an estimate improvement for adding a predicate given by:

Estimated improvement=Time−

(((count/sec with added predicate)/(count/second))*Time), where Time is a sum of time for queries containing the added predicate.

8. The system of claim 2, wherein the problem identification system selects a problem from a problem list including:

missing statistics on the added column;

missing index on new column;

missing multi-column index on the fact table; and

predicate contains non-indexable operator.

9. A method of analyzing query performance based on predicate usage, comprising:

collecting performance statistics for database queries;

organizing the performance statistics based on predicate combinations of the database queries; and

analyzing performance of queries having similar predicate combinations.

10. The method of claim 9, comprising the further step of identifying a problem from a list consisting of:

missing statistics on the added column;

missing index on new column;

missing multi-column index on the fact table; and

predicate contains non-indexable operator.

11. The method of claim 9, comprising the further step of automatically proposing a solution to address a low performing query.

12. The method of claim 11, comprising the further step of comparing the performance of a set of queries before and after an implementation of a proposed solution.

13. The method of claim 9, wherein the performance statistics include:

rows that satisfy the predicates, query elapsed time, index pages referenced, and rows processed.

14. The method of claim 13, wherein the step of analyzing performance includes the step of calculating an estimate improvement for adding a predicate given by:

Estimated improvement=Time−

(((count/sec with added predicate)/(count/second))*Time), where Time is a sum of time for queries containing the added predicate.

15. A program product stored on a recordable medium for analyzing query performance based on predicate usage, comprising:

means for collecting performance statistics for database queries;

means for organizing the performance statistics based on predicate combinations of the database queries; and

means for analyzing the performances of queries having similar predicate combinations.

16. The program product of claim 15, wherein each predicate combination comprises a set of dimensions.

17. The program product of claim 15, further comprising means for generating a performance improvement measure for each of a set of queries based on an order of the predicates in the query.

18. The program product of claim 15, further comprising means for proposing a solution to address a low performing query.

19. The program product of claim 18, further comprising means for comparing the performance of a query before and after an implementation of a proposed solution.

20. The program product of claim 15, wherein the performance statistics are selected from the group consisting of:

rows that satisfy the predicates, query elapsed time, index pages referenced, and rows processed.