System and method for query expression optimization

• US 8,204,873 B2
• Filed: 08/26/2009
• Issued: 06/19/2012
• Est. Priority Date: 08/26/2009
• Status: Active Grant

• Current Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Original Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Inventors: Chavan, Shasank K.
• Primary Examiner(s): Lu, Kuen
• Assistant Examiner(s): Pham, Tuan A
• Application Number:
  US12/548,352
• Publication Number:
  US 20110055197A1
• Time in Patent Office: 1,028 Days
• Field of Search: None
• US Class Current: 707/713
• CPC Class Codes: G06F 16/2454 Optimisation of common expressions

Abstract

A method provided for optimizing a query expression on a database engine of a database server. The query expression is sent to the database engine. The query expression contains a plurality of query language elements. The database engine initiates query processing of the query expression. An evaluation counter within the database engine increments an evaluation counter value corresponding to a query language element result. A flag is set within the database engine when the evaluation counter value reaches a threshold value. The database engine can then evaluate the efficiency of the query language elements in the query expression by comparing the evaluation counter value for each query language element against a set of optimization criteria.

Claims

1. A system for optimizing execution of a query expression on a database engine of a database server, comprising:

a computer processor;

a database engine on a database server for receiving the query expression with a plurality of query language elements;

an evaluation counter module within the database engine, wherein the evaluation counter module increments an evaluation counter value corresponding to a query language element result;

a flag within the database engine, wherein the flag is configured to be set in the database engine when the evaluation counter value reaches a threshold value;

a seen counter module within the database engine, wherein the seen counter module increments a seen counter value corresponding to each of the query language elements when a query language element is evaluated;

a cost value module within the database engine, wherein the cost value module assigns a cost value to each of the query language elements determined by a duration of time that a query processor uses to evaluate each query language element; and

a module that reorders query language elements in the query expression in order of a most efficient query language element with a greatest probability of an early exit of the query expression evaluated first and a least efficient query language element with a lowest probability of an early exit of the query expression evaluated last in the query expression, wherein query language element efficiency is defined as the evaluation counter value divided by a product of the seen counter value and the cost value.

2. The system of claim 1, wherein the query language element evaluation results in a Boolean result.

3. A computer implemented method for optimizing a query expression on a database engine of a database server, comprising:

sending, by the computer, the query expression to the database engine, wherein the query expression contains a plurality of predicates;

initiating, by the computer, query processing of the query expression;

incrementing, by the computer, an evaluation counter, wherein the counter increments an evaluation counter value corresponding to a predicate when a predicate evaluation results in a Boolean result;

setting, by the computer, a flag within the database engine when the evaluation counter value reaches a threshold value;

incrementing, by the computer, a seen counter, wherein the seen counter increments a seen counter value corresponding to each of the plurality of predicates when a predicate is evaluated;

assigning, by the computer, a cost value to each predicate of the plurality of predicates determined by a duration of time that a query processor uses to evaluate each predicate; and

reordering, by the computer, predicates in the query expression in order of a most efficient predicate with a greatest probability of an early exit of the query expression evaluated first and a least efficient predicate with a lowest probability of an early exit of the query expression evaluated last in the query expression, wherein predicate efficiency is defined as the evaluation counter value divided by a product of the seen counter value and the cost value.

4. The method of claim 3, wherein the evaluation counter increments the evaluation counter values for the plurality of predicates and sets the flag using instrumented code.

5. The method of claim 3, further comprising reordering the predicates in the query expression in order of a most efficient predicate with a highest evaluation counter value evaluated first in the query expression and a least efficient predicate with a lowest evaluation counter value evaluated last in the query expression.

6. The method of claim 3, further comprising continuing to run the query with reordered predicates in the query expression.

7. The method of claim 3, wherein reordering the predicates in the query expression uses an intermediate code that is interpreted by an evaluation engine at runtime.

8. The method of claim 3, wherein reordering the predicates in the query expression uses a database machine code evaluated by a query processor.

9. The method of claim 3, further comprising reordering, by the computer, an evaluation order of predicates in the query expression in order of a second predicate efficiency, wherein the second predicate efficiency is defined as the evaluation counter divided by the cost value.

10. The method of claim 3, wherein the evaluation counter value is the seen counter value for a predetermined predicate of the query expression.

11. The method of claim 3, wherein incrementing the evaluation counter value increments when each row in a database table is accessed.

12. The method of claim 3, further comprising continuing to run the query on a first query processor while evaluating the efficiency of an ordering of the predicates in the query expression on a second query processor.

13. The method of claim 3, wherein evaluating the efficiency of an ordering of the predicates in the query expression is evaluated during query execution.

14. A computer implemented method for optimizing a query expression on a database engine of a database server, comprising:

sending, by the computer, the query expression to the database engine, wherein the query expression contains a plurality of query language elements;

initiating, by the computer, query processing of the query expression;

incrementing, by the computer, an evaluation counter, wherein the counter increments an evaluation counter value corresponding to a query language element when a specified query language element evaluation results;

setting, by the computer, a flag within the database engine when the evaluation counter value reaches a threshold value;

incrementing, by the computer, a seen counter, wherein the seen counter increments a seen counter value corresponding to each of the query language elements when a query language element is evaluated;

assigning, by the computer, a cost value to each of the query language elements determined by a duration of time that a query processor uses to evaluate each query language element; and

reordering, by the computer, query language elements in the query expression in order of a most efficient query language element with a greatest probability of an early exit of the query expression evaluated first and a least efficient query language element with a lowest probability of an early exit of the query expression evaluated last in the query expression, wherein query language element efficiency is defined as the evaluation counter value divided by a product of the seen counter value and the cost value.

15. The method of claim 14, wherein the query language elements are selected from the group consisting of predicates, nulls, columns, constants, indexes, keys, sub-queries, expressions, and combination thereof.