Dynamic optimization of prepared statements in a statement pool

• US 7,912,848 B2
• Filed: 10/10/2007
• Issued: 03/22/2011
• Est. Priority Date: 05/23/2002
• Status: Expired due to Term

• Current Assignee: Meta Platforms, Inc. (f/k/a Facebook, Inc.)
• 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,225
• Publication Number:
  US 20080034007A1
• Time in Patent Office: 1,259 Days
• Field of Search: None
• US Class Current: 707/759
• 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:

identifying, from among a plurality of statements in a statement pool utilized by a database, a statement that requests data in a first format from a field in the database that stores the data in a second format; and

modifying the identified statement in the statement pool using at least one processor to instruct the database to convert the data to the first format prior to returning the data; and

tracking usage of at least a subset of the plurality of statements, wherein the modifying the identified statement is performed based upon the tracked usage, wherein the 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.

2. The method of claim 1, further comprising associating with the modified statement a usage statistic generated for the statement.

3. The method of claim 1, further comprising associating with the identified statement a statement signature, the method further comprising accessing the modified statement in response to a user request to access a statement having a statement signature corresponding to the identified statement.

4. The method of claim 1, wherein the identified statement is processed by a Java Database Connectivity driver and requests the data in Unicode format, and wherein modifying the identified statement includes instructing the database to convert the data to the Unicode format prior to returning the data.

5. An apparatus, comprising:

a processor; and

program code configured to be executed by the processor to manage a statement pool by identifying, from among a plurality of statements in a statement pool utilized by a database, a statement that requests data in a first format from a field in the database that stores the data in a second format, and modifying the identified statement in the statement pool to instruct the database to convert the data to the first format prior to returning the data, wherein the program code is further configured to track usage of at least a subset of the plurality of statements, and to modify the identified statement based upon the tracked usage, and 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.

6. The apparatus of claim 5, wherein the program code is further configured to associate with the modified statement a usage statistic generated for the statement.

7. The apparatus of claim 5, wherein the program code is further configured to associate with the identified statement a statement signature, and to access the modified statement in response to a user request to access a statement having a statement signature corresponding to the identified statement.

8. The apparatus of claim 5, wherein the identified statement is processed by a Java Database Connectivity driver and requests the data in Unicode format, and wherein the program code is configured to modify the identified statement by instructing the database to convert the data to the Unicode format prior to returning the data.

9. A program product, the product comprising:

program code configured to be executed by a processor to manage a statement pool by identifying, from among a plurality of statements in a statement pool utilized by a database, a statement that requests data in a first format from a field in the database that stores the data in a second format, and modifying the identified statement in the statement pool to instruct the database to convert the data to the first format prior to returning the data; and

a recordable medium storing the program code, wherein the program code is further configured to track usage of at least a subset of the plurality of statements, and to modify the identified statement based upon the tracked usage, and 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.

10. The program product of claim 9, wherein the program code is further configured to associate with the modified statement a usage statistic generated for the statement.

11. The program product of claim 9, wherein the program code is further configured to associate with the identified statement a statement signature, and to access the modified statement in response to a user request to access a statement having a statement signature corresponding to the identified statement.

12. The program product of claim 9, wherein the identified statement is processed by a Java Database Connectivity driver and requests the data in Unicode format, and wherein the program code is configured to modify the identified statement by instructing the database to convert the data to the Unicode format prior to returning the data.