DYNAMIC OPTIMIZATION OF PREPARED STATEMENTS IN A STATEMENT POOL

• US 20080028382A1
• Filed: 10/10/2007
• Published: 01/31/2008
• Est. Priority Date: 05/23/2002
• Status: Active Grant

• Current Assignee: Meta Platforms, Inc. (f/k/a Facebook, Inc.)
• Original Assignee: International Business Machines Corporation
• Inventors: Arnold, Jeremy, Santosuosso, John, Barsness, Eric, Dettinger, Richard
• Granted Patent: US 7,653,620 B2
• Time in Patent Office: Days
• US Class Current: 717/155
• 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 method of managing a statement pool, the method comprising:

accessing an unoptimized representation of a statement among a plurality of statements in a statement pool utilized by a database, wherein the unoptimized representation of the statement is a prepared statement;

generating an optimized representation of the statement from the unoptimized representation of the statement, wherein the optimized representation of the statement is a prepared statement; and

replacing the unoptimized representation of the statement with the optimized representation of the statement in the statement pool.

2. The method of claim 1, further comprising tracking usage of at least a subset of the plurality of statements, wherein generating the optimized representation of the statement is performed based upon the tracked usage.

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

4. The method of claim 3, further comprising associating with the optimized representation of the statement a usage statistic generated for the unoptimized representation of the statement.

5. The method of claim 2, further comprising generating an optimized representation of a second statement among the plurality of statements, wherein generating the optimized representation of the second statement is performed subsequent to generation of the optimized representation of the first statement based upon respective usage statistics associated with the first and second statements.

6. The method of claim 1, further comprising associating with the optimized representation of the statement a statement signature for the 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.

7. The method of claim 1, wherein generating the optimized representation of the statement includes modifying the unoptimized representation of the statement to instruct the database to convert returned data to a different format prior to returning the data.

8. The method of claim 7, wherein generating the optimized representation of 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.

9. The method of claim 7, 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 instructing the database to convert the data to the Unicode format prior to returning the data.

10. The method of claim 1, wherein generating the optimized representation of the statement includes modifying an access method to be used during execution of the statement.

11. The method of claim 1, wherein generating the optimized representation of the statement includes reordering a retrieval order for the data to be returned in response to execution of the statement.

12. The method of claim 1, wherein generating the optimized representation of the statement includes modifying the unoptimized representation of the statement to reference at least one stored procedure.

13. The method of claim 1, further comprising, after replacing the unoptimized representation of the statement with the optimized representation of the statement, processing a request to access the unoptimized representation of the statement by accessing the optimized representation of the statement.

14. 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 accessing an unoptimized representation of statement among a plurality of statements in the statement pool, generating an optimized representation of the statement from the unoptimized representation of the statement, and replacing the unoptimized representation of the statement with the optimized representation of the statement in the statement pool, wherein each of the unoptimized and optimized representations of the statement is a prepared statement.

15. The apparatus of claim 14, wherein the program code is further configured to track usage of at least a subset of the plurality of statements, and to generate the optimized representation of the statement based upon the tracked usage.

16. The apparatus of claim 15, 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 subset of statements is used.

17. The apparatus of claim 16, wherein the unoptimized representation of the statement includes a statement object including at least one usage statistic.

18. The apparatus of claim 15, wherein the program code is further configured to generate an optimized representation of a second statement among the plurality of statements subsequent to generation of the optimized representation of the first statement based upon respective usage statistics associated with the first and second statements.

19. The apparatus of claim 14, wherein the program code is further configured to associate with the optimized representation of the statement a statement signature for the unoptimized representation of the statement, such that the program code accesses 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.

20. The apparatus of claim 14, wherein the program code is configured to generate the optimized representation of the statement by modifying the 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.

21. The apparatus of claim 20, wherein the program code is configured to generate the optimized representation of the statement further by 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, and to modify the unoptimized representation of the statement by modifying the unoptimized representation of the statement to instruct the database to convert the data to the first format prior to returning the data.

22. The apparatus of claim 14, wherein the program code is further configured to, after replacing the unoptimized representation of the statement with the optimized representation of the statement, process a request to access the unoptimized representation of the statement by accessing the optimized representation of the statement.

23. A program product, comprising:

program code configured to be executed by the processor to manage a statement pool utilized by a database by accessing an unoptimized representation of statement among a plurality of statements in a statement pool, generating an optimized representation of the statement from the unoptimized representation of the statement, and replacing the unoptimized representation of the statement with the optimized representation of the statement in the statement pool, wherein each of the unoptimized and optimized representations of the statement is a prepared statement; and

a recordable medium bearing the program code.