Client-side statement cache

• US 8,832,143 B2
• Filed: 12/16/2010
• Issued: 09/09/2014
• Est. Priority Date: 12/16/2010
• Status: Active Grant

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Benadjaoud, Ghazi Nourdine, Schneider, Peter
• Primary Examiner(s): Trujillo, James
• Assistant Examiner(s): VU, THONG H
• Application Number:
  US12/970,243
• Publication Number:
  US 20120158770A1
• Time in Patent Office: 1,363 Days
• Field of Search: 707/1, 707/10, 707/802, 707/780, 707/718, 707/768, 707/769
• US Class Current: 707/769
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/951 Indexing; Web crawling techniques
• CPC Class Codes: G06Q 30/02 Marketing; Price estimation or determination; Fundraising

Abstract

Embodiments include a method, apparatus and computer program product for providing instructions from a database client to a database server. The method includes the steps of receiving from a database a representation of a first instruction and an associated identifier. The representation of the first instruction and the associated identifier are stored in a cache. A second instruction, from an application, is matched with the representation of the first instruction stored in the cache. The associated identifier is retrieved from the cache based on the matching and the associated identifier is sent to the database server. Upon receipt of the associated identifier, the database server executes the first instruction.

Claims

1. A method of providing instructions from a database client to a database server, comprising:

receiving a plurality of embedded structured query language (SQL) instructions;

sending the plurality of embedded SQL instructions to the database server, wherein the database server analyzes the plurality of embedded SQL instructions;

receiving, from the database server and based on the analyzing by the database server, a representation of a first instruction and an associated identifier, wherein the first instruction comprises a pre-processed dynamic SQL instruction converted from a first embedded SQL instruction from the plurality of embedded SQL instructions;

storing the representation of the first instruction and the associated identifier in a cache;

receiving a second instruction from an application, wherein the second instruction is not identical to the first instruction and comprises a second embedded SQL instruction from the plurality of embedded SQL instructions;

matching the representation of the first instruction and the second instruction;

retrieving the associated identifier from the cache based on the matching; and

sending the associated identifier to the database server, wherein, upon receiving the associated identifier the database server executes the first instruction.

2. The method of claim 1,

wherein the second instruction has a parameter, and

wherein the sending the associated identifier to the database server comprises sending the associated identifier and the parameter to the database server,

wherein, upon receiving the identifier and the parameter, the database server executes the first instruction using the parameter.

3. The method of claim 1, wherein the cache is a client-side statement cache.

4. The method of claim 1, wherein the storing of the representation of the first instruction in a cache comprises:

storing the representation in both a memory and in a file, wherein upon next starting of the database client the memory is loaded from the file.

5. The method of claim 1, wherein each application that uses the database client has a different identifier associated with the first instruction.

6. The method of claim 1, wherein each application that uses the database client has the same identifier associated with the first instruction.

7. The method of claim 1, before the receiving a representation of a first instruction from a database server, further comprising:

receiving a plurality of instructions from the application, wherein one of the plurality of instructions is the second instruction;

sending the plurality of instructions to the database server, wherein the database server analyzes the plurality of instructions, and

wherein the receiving of the representation of a first instruction from a database server is based on the analyzing by the database server.

8. The method of claim 1, wherein the first instruction is a processed version of the second instruction.

9. The method of claim 1, wherein the storing the representation further comprises storing the representation of the first instruction in a hash table by using the first instruction as a hash key.

16. The method of claim 7, wherein the analyzing by the database server further comprises estimating a system resource cost of executing the plurality of instructions.

10. An apparatus in a database client, comprising:

a cache controller configured to, using a processor, receive from a database server, based on an analysis by the database server, a representation of a first instruction and an associated identifier, wherein the cache controller is further configured to store the representation of the first instruction and the associated identifier in a cache, and further wherein the first instruction comprises a pre-processed dynamic structured query language (SQL) instruction converted from a first embedded SQL instruction from a plurality of embedded SQL instructions;

an application interface coupled to an application, wherein the application interface is configured to;

receive the plurality of embedded SQL instructions;

send the plurality of embedded SQL instructions to a database server, wherein the database server analyzes the plurality of embedded SQL instructions; and

receive a second instruction from the application, wherein the second instruction is not identical to the first instruction and comprises a second embedded SQL instruction from the plurality of embedded SQL instructions;

a matcher configured to match, using a processor, the representation of the first instruction to the received second instruction;

an identifier retriever configured to retrieve an identifier from the cache based on a result from the matcher; and

a database interface configured to send the identifier to the database, wherein upon receipt of the identifier, the database server is configured to execute the first instruction.

11. The apparatus of claim 10, wherein the second instruction has a literal value, further comprising:

a parametizer configured to generate a parameter by identifying and parameterizing the literal value in the second instruction;

wherein the sending of the identifier to the database server comprises sending the identifier and the parameter to the database server, and

wherein, upon receiving the identifier and the parameter, the database server executes the first instruction using the parameter.

12. The apparatus of claim 10, wherein the cache is a client-side statement cache.

13. The apparatus of claim 10, wherein the storing of the representation of the first instruction in a cache comprises:

storing the representation in both a memory and in a file, wherein upon next starting of the database client the memory is loaded from the file.

14. The apparatus of claim 10, wherein each application that uses the database client has a different identifier associated with the first instruction.

15. The apparatus of claim 10, wherein each application that uses the database client has the same identifier associated with the first instruction.

17. A method of executing database instructions, comprising:

receiving a plurality of embedded structured query language (SQL) instructions from one or more database clients;

processing a first database instruction to create a processed version of the first database instruction, wherein the first instruction comprises a pre-processed dynamic SQL instruction converted from a first embedded SQL instruction from the plurality of embedded SQL instructions;

assigning an identifier to the processed version of the first database instruction;

storing the identifier and the processed version of the first database instruction;

sending the identifier and a representation of the processed version of the first database instruction to a cache on the one or more database clients;

receiving a value that corresponds to the identifier from the database client, wherein the identifier is received based on a second database instruction, the second database instruction being not identical to the first instruction and comprises a second embedded SQL instruction from the plurality of embedded SQL instructions; and

wherein, based on the received identifier, a database server executes the processed version of the first database instruction.

18. The method of claim 17, before the assigning of the identifier to the processed instruction, further comprising

analyzing a database instruction received from a database client;

and

determining whether to create the processed version of the database instruction based on the analyzing.

19. The method of claim 17, after the receiving the value that corresponds to the identifier from the database client, further comprising:

receiving a parameter,

wherein the processed version of the database instruction is a parameterized version of the database instruction, and

wherein the executing of the processed version of the database instruction is based on the received identifier and the received parameter.

20. A non-transitory computer-readable medium having computer-executable instructions stored thereon that, when executed by a computing device, cause the computing device to perform instructions, the instructions comprising:

receiving a plurality of embedded structured query language (SQL) instructions;

sending the plurality of embedded SQL instructions to a database server, wherein the database server analyzes the plurality of instructions;

receiving, from the database server and based on the analyzing by the database server, a representation of a first instruction and an associated identifier, wherein the first instruction comprises a pre-processed dynamic SQL instruction converted from a first embedded SQL instruction from the plurality of embedded SQL instructions;

storing the representation of the first instruction and the associated identifier in a cache;

receiving a second instruction from an application, wherein the second instruction is not identical to the first instruction and comprises a second embedded SQL instruction from the plurality of embedded SQL instructions;

matching the representation of the first instruction and the second instruction;

retrieving the associated identifier from the cache based on the matching; and

sending the associated identifier to the database server, wherein, upon receiving the associated identifier the database server executes the first instruction.