SYSTEM AND METHOD FOR QUERY EXPRESSION OPTIMIZATION

• US 20110055197A1
• Filed: 08/26/2009
• Published: 03/03/2011
• 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.
• Granted Patent: US 8,204,873 B2
• Time in Patent Office: Days
• 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 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; and

a query processor within the database engine, wherein the query processor is configured to evaluate the efficiency of the query language elements in the query expression after the flag is set by comparing the evaluation counter value for each query language element against a set of optimization criteria.

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

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

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

initiating query processing of the query expression;

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

setting a flag within the database engine when the evaluation counter value reaches a threshold value; and

evaluating the efficiency of an ordering of the predicates in the query expression after the flag is set by comparing the evaluation counter value for each predicate against the evaluation counter value of the other predicates.

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 reordering the 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.

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

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

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

10. The method of claim 3, further comprising assigning a cost value to each predicate of the plurality of predicates determined by a duration of time a query processor uses to evaluate each predicate.

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

12. The method of claim 3, further comprising incrementing 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.

13. The method of claim 12, further comprising:

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

reordering predicates in the query expression in order of predicate efficiency, wherein predicate efficiency is defined as the evaluation counter value divided by a product of the seen counter value and the cost value.

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

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

16. 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.

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

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

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

initiating query processing of the query expression;

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

setting a flag within the database engine when the evaluation counter value reaches a threshold value; and

evaluating the efficiency of the query language elements in the query expression after the flag is set by comparing the evaluation counter value for each query language element against a set of optimization criteria.

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

20. The method of claim 18, wherein the set of optimization criteria is the evaluation counter value of the other query language elements.