System and method for addressing inefficient query processing

• US 7,376,638 B2
• Filed: 12/24/2003
• Issued: 05/20/2008
• Est. Priority Date: 12/24/2003
• Status: Active Grant

• Current Assignee: Meta Platforms, Inc. (f/k/a Facebook, Inc.)
• Original Assignee: International Business Machines Corporation
• Inventors: Gordon, Mark R.
• Primary Examiner(s): Rimell; Sam
• Assistant Examiner(s): Bell; Cory C
• Application Number:
  US10/746,961
• Publication Number:
  US 20050165741A1
• Time in Patent Office: 1,609 Days
• Field of Search: 707/2, 707/3
• 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 method of analyzing query performance based on predicate usage, comprising:

collecting performance statistics for database queries comprising database statements entered by end-users having different predicate combinations;

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

analyzing performance of a subset of the database queries having different predicate combinations that include common predicates;

generating analysis results; and

identifying a problem from a list consisting of;

missing statistics on an added column, missing index on new column, missing multi-column index on a fact table, and predicate contains a non-indexable operator.

2. The method of claim 1, comprising the further step of automatically proposing a solution to address a database query from the subset of database queries identified as a low performer.

3. The method of claim 2, comprising the further step of comparing the performance of a second subset of database queries before and after an implementation of a proposed solution.

4. The method of claim 1, wherein the performance statistics include:

rows that satisfy a set of predicates, query elapsed time, index pages referenced, and rows processed.

5. The method of claim 4, wherein the step of analyzing performance includes the step of calculating 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.

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

collecting performance statistics for database queries comprising database statements entered by end-users having different predicate combinations;

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

analyzing performance of a subset of the database queries having different predicate combinations that include common predicates, wherein analyzing performance includes the step of calculating 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; and

generating analysis results.