Dynamic optimization of prepared statements in a statement pool

• US 9,146,974 B2
• Filed: 10/10/2007
• Issued: 09/29/2015
• Est. Priority Date: 04/18/2002
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Arnold, Jeremy Alan, Barsness, Eric Lawrence, Dettinger, Richard Dean, Santosuosso, John Matthew
• Primary Examiner(s): TRUONG, CAM Y T
• Application Number:
  US11/870,218
• Publication Number:
  US 20080027999A1
• Time in Patent Office: 2,911 Days
• Field of Search: 707/999.02, 707/713
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99936 Pattern matching access

Abstract

An apparatus, program product and method dynamically optimize prepared statements resident in a statement pool to permit subsequent reuses of such statements to utilize the optimized representations of such statements. The optimization of pooled statements may be performed responsive to the tracked usage of the statements in a statement pool, to direct the overhead associated with such optimization to those statements that are more likely to be utilized in the future. Among a multitude of possible optimizations, a pooled statement that requests data in a format other than that utilized by the database being accessed may be modified to request the data in the format used by the database, thereby decreasing the overhead in the database associated with format conversions.

Claims

1. A computer implemented method of managing a statement pool, the method comprising:

tracking usage of a plurality of statements in a statement pool utilized by a database;

selectively optimizing at least one of the statements from the statement pool based upon the tracked usage of the plurality of statements using at least one processor;

after selectively optimizing the statement, processing a request to access an unoptimized representation of the statement by accessing an optimized representation of the statement; and

executing a database query on the database using the optimized representation of the statement.

2. The method of claim 1, wherein tracking usage includes tracking at least one of a number of times and a last time that each of the statements is used.

3. The method of claim 1, further comprising selectively optimizing a second statement from the statement pool after selectively optimizing a first statement based upon the tracked usage indicating that the first statement has a higher usage than the second statement.

4. The method of claim 1, wherein selectively optimizing the statement further includes associating with an optimized representation of the statement a statement signature for an unoptimized representation of the statement, the method further comprising accessing the optimized representation of the statement in response to a user request to access a statement having a statement signature corresponding to the unoptimized representation of the statement.

5. The method of claim 1, wherein selectively optimizing the statement includes modifying an unoptimized representation of the statement to perform at least one of instructing the database to convert returned data to a different format prior to returning the data, modifying an access method to be used during execution of the statement, reordering a retrieval order for the data to be returned in response to execution of the statement, and referencing at least one stored procedure.

6. The method of claim 5, wherein selectively optimizing the statement further includes determining that the unoptimized representation of the statement requests data in a first format from a field in the database that stores the data in a second format, wherein modifying the unoptimized representation of the statement includes modifying the unoptimized representation of the statement to instruct the database to convert the data to the first format prior to returning the data.

7. The method of claim 6, wherein the unoptimized statement is processed by a Java Database Connectivity driver and requests the data in Unicode format, and wherein modifying the unoptimized representation of the statement includes modifying the unoptimized representation of the statement to instruct the database to convert the data to the Unicode format prior to returning the data.

8. An apparatus, comprising:

a processor; and

program code configured to be executed by the processor to manage a statement pool utilized by a database by tracking usage of a plurality of statements in the statement pool and selectively optimizing at least one of the statements from the statement pool based upon the tracked usage of the plurality of statements, wherein the program code is further configured to, after selectively optimizing the statement, process a request to access an unoptimized representation of the statement by accessing an optimized representation of the statement, and wherein the program code is further configured to execute a database query on the database using the optimized representation of the statement.

9. The apparatus of claim 8, wherein the program code is configured to track usage by tracking at least one of a number of times and a last time that each of the plurality of statements is used.

10. The apparatus of claim 8, wherein the program code is further configured to selectively optimize a second statement from the statement pool after selectively optimizing a first statement based upon the tracked usage indicating that the first statement has a higher usage than the second statement.

11. The apparatus of claim 8, wherein the program code is configured to selectively optimize the statement further by associating with an optimized representation of the statement a statement signature for an unoptimized representation of the statement, and to access the optimized representation of the statement in response to a user request to access a statement having a statement signature corresponding to the unoptimized representation of the statement.

12. The apparatus of claim 8, wherein the program code is configured to selectively optimize the statement by modifying an unoptimized representation of the statement to perform at least one of instructing the database to convert returned data to a different format prior to returning the data, modifying an access method to be used during execution of the statement, reordering a retrieval order for the data to be returned in response to execution of the statement, and referencing at least one stored procedure.

13. A program product, comprising:

program code configured to be executed by the processor to manage a statement pool utilized by a database by tracking usage of a plurality of statements in the statement pool and selectively optimizing at least one of the statements from the statement pool based upon the tracked usage of the plurality of statements, wherein the program code is further configured to, after selectively optimizing the statement, process a request to access an unoptimized representation of the statement by accessing an optimized representation of the statement, and wherein the program code is further configured to execute a database query on the database using the optimized representation of the statement; and

a non-transitory recordable medium storing the program code.

14. The program product of claim 13, wherein the program code is configured to track usage by tracking at least one of a number of times and a last time that each of the plurality of statements is used.

15. The program product of claim 13, wherein the program code is further configured to selectively optimize a second statement from the statement pool after selectively optimizing a first statement based upon the tracked usage indicating that the first statement has a higher usage than the second statement.

16. The program product of claim 13, wherein the program code is configured to selectively optimize the statement further by associating with an optimized representation of the statement a statement signature for an unoptimized representation of the statement, and to access the optimized representation of the statement in response to a user request to access a statement having a statement signature corresponding to the unoptimized representation of the statement.

17. The program product of claim 13, wherein the program code is configured to selectively optimize the statement by modifying an unoptimized representation of the statement to perform at least one of instructing the database to convert returned data to a different format prior to returning the data, modifying an access method to be used during execution of the statement, reordering a retrieval order for the data to be returned in response to execution of the statement, and referencing at least one stored procedure.