# US 2012/0084315 A1 · Query Plan Optimization for Prepared SQL Statements

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2012/0084315 A1
- Document ID: US20120084315A1
- Status: Published application
- Kind: A1
- Application number: 12/897,477
- Filed: 2010-10-04
- Published: 2012-04-05
- Earliest claimed: 2010-10-04
- Assignees: Sybase, Inc.
- Inventors: Peter J. SCHNEIDER; Hui ZHANG
- Web page: https://patentgrep.com/patent/US20120084315A1

## CPC

- G06F 16/24542: {Plan optimisation}
- G06F 16/2453: Query optimisation
- G06F 16/24539: {using cached or materialised query results}
- G06F 16/24545: {Selectivity estimation or determination}
- G06F 16/24558: {Binary matching operations}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

System, methods and articles of manufacture for optimizing a query plan reuse in a database server system accessible by a plurality of client connections. An embodiment comprises providing at least one global cache storage and a private cache storage to a plurality of client connections, and coordinating utilization of the at least one global cache storage and the private cache storage to share light weight stored procedures and query plans for prepared SQL statements across the plurality of client connections via the at least one global cache storage while avoiding a spinlock access for executing the prepared SQL statements.

## Claims

### Claim 1

A method for optimizing a query plan reuse in a database server system, comprising:
providing at least one global cache storage and a private cache storage to a plurality of client connections; and
coordinating utilization of the at least one global cache storage and the private cache storage to share light weight stored procedures (LWPs) and query plans for prepared SQL statements across the plurality of client connections via the at least one global cache storage while avoiding a spinlock access for executing the prepared SQL statements.

### Claim 2 (depends on 1)

The method of claim 1, wherein coordinating utilization further comprises:
initiating a Dynamic SQL Prepare process, wherein the Dynamic SQL Prepare process generates a first LWP entry that is accessible by the plurality of client connections; and
initiating a Dynamic SQL Execute process, wherein the Dynamic SQL Execute process uses the first LWP entry to determine if a query plan was generated by at least one client connection in the plurality of client connections.

### Claim 3 (depends on 2)

The method of claim 2, wherein the Dynamic SQL Prepare process further comprises:
receiving a prepared SQL statement;
searching a first global cache storage for the first LWP entry associated with the prepared SQL statement;
generating a second LWP entry in a local cache storage if the first LWP entry is found; and
generating a new LWP entry, storing the new LWP entry as the first LWP entry in the first global cache storage and as the second LWP entry in the local cache storage if the first LWP entry is not found.

### Claim 4 (depends on 2)

The method of claim 2, wherein the prepared SQL statement comprises a prepared SQL name and wherein a prepared SQL text is retrieved from the database server system based on the prepared SQL name.

### Claim 5 (depends on 2)

The method of claim 2, wherein the first LWP entry and the second LWP entry further comprise of an LWP object identifier, an LWP name and an LWP query text and wherein the searching for the first LWP entry comprises comparing the first LWP query text to the prepared SQL text.

### Claim 6 (depends on 2)

The method of claim 2, wherein the Dynamic SQL Execute process comprises:
receiving an execute command for the prepared SQL statement;
retrieving the second LWP entry from the local cache storage, wherein the second LWP entry is associated with the prepared SQL statement;
determining if the query plan associated with the second LWP entry is stored in a second global cache storage;
retrieving the query plan from the second global cache storage and executing the query plan if the query plan is found; and
generating a new query plan, storing the new query plan in the second global cache storage, and executing the new query plan if the query plan is not found.

### Claim 7 (depends on 6)

The method of claim 6, further comprising:
using the first global cache storage to determine if the query plan exists in the second global cache storage if the query plan is not associated with the second LWP entry, and
executing the query plan and storing a reference to the query plan in the local cache storage.

### Claim 8 (depends on 6)

The method of claim 6, further comprising of generating a cloned query plan if the query plan in the first global cache storage is associated with another client connection of the plurality client connections.

### Claim 9 (depends on 6)

The method of claim 6, wherein a first execution of the Dynamic SQL Execute process generates the query plan in the second global cache storage and the reference to the query plan in the local cache storage.

### Claim 10 (depends on 8)

The method of claim 8 wherein generating the cloned query plan further comprises:
identifying if the query plan is reserved by one client connection of the plurality client connections;
generating the cloned query plan from the query plan if the query plan is reserved; and
associating the cloned query plan with a second client connection of the plurality client connections.

### Claim 11 (depends on 10)

The method of claim 10, wherein the generating further comprises:
identifying a value portion of the query plan wherein the value portion holds values associated with the prepared SQL statement;
allocating a new storage space for the value portion of the cloned query plan;
copying the value portion of the query plan into the new storage space; and
storing values associated with the second client connection in the new storage space.

### Claim 12 (depends on 10)

The method of claim 10, wherein the associating comprises of storing the reference to the cloned query plan in the local cache storage.

### Claim 13 (depends on 10)

The method of claim 10, wherein the identifying is performed by a global cache storage manager.

### Claim 14 (depends on 10)

The method of claim 10, wherein an instruction portion of the query plan is shared by the query plan and one or more cloned query plans.

### Claim 15

A database server system for optimizing a query plan reuse comprising:
a first global cache storage configured to store a plurality of LWPs;
a second global cache storage configured to store a plurality of query plans, wherein the first and second global cache storage are accessible to a plurality of client connections;
a local cache storage configured to store a subset of LWPs wherein the subset of LWPs is associated with one client connection of the plurality of client connections; and
a coordination module configured to access the first and second global cache storage and the local cache storage and determine if a query plan for a prepared SQL statement generated by at least one client connection of the plurality of client connections exists in the database server system and avoid an access spinlock for executing the prepared SQL statement.

### Claim 16 (depends on 15)

The system of claim 15, wherein the coordination module further comprises:
a Dynamic SQL Prepare module, wherein the Dynamic SQL Prepare module further comprises:
a module configured to receive the prepared SQL Statement;
a module configured to search the first global cache storage for a first LWP entry associated with the prepared SQL statement;
a module configured to generate a second LWP entry in the local cache storage if the first LWP entry is found; and
a module configured to generate a new LWP entry, store the new LWP entry as the first LWP entry in the first global cache storage and as the second LWP entry in the local cache storage if the first LWP entry is not found.

### Claim 17 (depends on 15)

The system of claim 15, wherein the coordination module further comprises:
a Dynamic SQL Execute module, wherein the Dynamic SQL Execute module comprises:
a module configured to receive an execute command for the prepared SQL statement;
a module configured to receive a second LWP entry from the local cache storage, wherein the second LWP entry is associated with the prepared SQL statement;
a module configured to determine if the query plan associated with the second LWP entry is stored in the second global cache storage;
a module configured to retrieve the query plan from the second global cache storage and executing the query plan if the query plan is found; and
a module configured to generate a new query plan, store the new query plan in the second global cache storage, and execute the new query plan if the query plan is not found.

### Claim 18 (depends on 15)

The system of claim 15, wherein the Dynamic SQL Execute module further comprises a cloning module, wherein the cloning module comprises:
a module configured to identify if the query plan is reserved by one client connection of the plurality of client connections;
a module configured to generate a cloned query plan from the query plan if the query plan is reserved; and
a module configured to associate the cloned query plan with a second client connection of the plurality of client connections.

### Claim 19

An article of manufacture including a computer-readable medium having instructions stored thereon that, when executed by a computing device, cause said computing device to perform operations comprising:
providing at least one global cache storage and a private cache storage to a plurality of client connections; and
coordinating utilization of the at least one global cache storage and the private cache storage to share LWPs and query plans for prepared SQL statements across the plurality of client connections via the global cache storage while avoiding a spinlock access for executing the prepared SQL statements.

### Claim 20 (depends on 19)

The article of manufacture of claim 19, the operations further comprising:
initiating a Dynamic SQL Prepare process, wherein the Dynamic SQL Prepare process generates a first LWP entry that is accessible by the plurality of client connections; and
initiating a Dynamic SQL Execute process, wherein the Dynamic SQL Execute process uses the first LWP entry to determine if a query plan was generated by at least one client connection in the plurality of client connections.
