Server side bucketization of parameterized queries

• US 7,865,515 B2
• Filed: 08/28/2006
• Issued: 01/04/2011
• Est. Priority Date: 08/28/2006
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Krishnamoorthy, Ganapathy Subramaniam, Birka, Adrian
• Primary Examiner(s): Channavajjala; Srirama
• Assistant Examiner(s): Le; Jessica N
• Application Number:
  US11/467,802
• Publication Number:
  US 20080065589A1
• Time in Patent Office: 1,590 Days
• Field of Search: None
• US Class Current: 707/759
• CPC Class Codes: G06F 16/24537 of operators
• CPC Class Codes: G06F 16/2454 Optimisation of common expressions

Abstract

Efficient server-side query statement parameter bucketization is provided for a query language, such as the structured query language (SQL). A process is applied for replacing the parameters of a parameterized query with new parameters, whose types are normalized to enable more query execution plan sharing. In one embodiment, in response to receiving a parameterized query expression of a query language, the bucketization process operates to bucketize parameter(s) of the parameterized expression based on the type(s) of the parameter(s) by replacing the type(s) with new broader, or more specific, type(s). An existing server-side parameterization framework that includes a mechanism for determining when parameterization is “safe,” is extended, and is applied to bucketize parameters by using the mechanism to determine when replacing a parameter(s) of parameterized expressions with new parameter(s) of different type(s) is safe.

Claims

1. A computer-implemented method, comprising:

receiving, from a requesting object, at least one parameterized query expression of a structured query language (SQL);

generating at least one new parameterized query expression based on the at least one received parameterized query expression, wherein the generating includes replacing a type of at least one parameter of the at least one parameterized query expression with a new normalized parameter type, wherein the replacing is executed, according to at least one pre-defined conversion algorithm, the at least one parameter being associated with first metadata to be returned by a query;

determining for the at least one parameter, whether said replacing the type of the at least one parameter will;

change semantic information of the at least one parameterized query expression; and

cause second metadata to be returned by the query, wherein the first metadata to be returned by the query is different from the second metadata to be returned by the query;

performing said replacing the type of the at least one parameter based, at least, on the determining for the at least one parameter that;

said semantic information of the at least one parameterized query expression will remain unchanged; and

the first metadata to be returned by the query is the same as the second metadata to be returned by the query; and

identifying whether literals of a non-parameterized query statement are parameterizable to a parameterized query expression by;

determining whether replacing the literals with a parameter will not change data or metadata transmitted back to a client; and

identifying as literals that are parameterizable to the parameterized query expression, ones of the literals for which the replacing will not change data or metadata transmitted back to the client;

wherein the type of the at least one parameter has a length in a range from 1 to n and the new normalized parameter type has a length equal to n, wherein said n is at least one of an integer or a real number.

2. The computer-implemented method according to claim 1, wherein, for at least one of variable length non-Unicode character data (varchar), length Unicode character data (nvarchar) or variable length binary data (varbinary) types.

3. The computer-implemented method according to claim 1, wherein said new normalized parameter type is broader than said type of the at least one parameter.

4. The computer-implemented method according to claim 1, wherein said new normalized parameter type is more specialized than said type of the at least one parameter.

12. The computer-implemented method according to claim 1, further comprising:

generating an optimized query plan according to the at least one new parameterized query expression.

13. The computer-implemented method according to claim 1, further comprising:

transforming a plurality of parameterized query expressions to a new parameterized query expression that is common to each of the plurality of parameterized query expressions, wherein one or more of the plurality of parameterized query expressions would be associated with separate query execution plans if the transforming the plurality of parameterized query expressions to a new parameterized query expression that is common to each of the plurality of parameterized query expressions was not performed.

5. A database server-side system configured to maintain and update a data store, the database server-side system comprising:

a processor; and

a computer-readable storage medium storing instructions that, when executed by the processor, cause the processor to implement at least one of a parameter type bucketization logic component or a query optimizer component, said instructions executable for;

the parameter type bucketization logic component configured to receive at least one parameterized query expression of a structured query language (SQL) and generate at least one new parameterized query expression based on the at least one parameterized query expression, wherein the at least one parameterized expression includes a parameter type associated with first metadata to be returned by a query, and wherein a generated at least one new parameterized query expression includes a parameter type associated with a second metadata to be returned by the query, wherein the parameter type bucketization logic component includes an extension to a server-side parameterization framework of the structured query language (SQL) server and is configured to;

determine whether, based on the at least one new parameterized query expression;

semantic information of the at least one parameterized query expression will change; and

the second metadata to be returned by the query will be different from the first metadata to be returned by the query; and

generate the at least one new parameterized query expression based, at least, on a determination that the semantic information will not change and the second metadata will be the same as the first metadata;

a query optimizer component configured to receive the at least one new parameterized query expression and generate an optimized query plan according to the at least one new parameterized query expression,

wherein the extension is configured to employ a mechanism of the server-side parameterization framework, wherein the mechanism is configured to identify whether literals of a non-parameterized query statement are parameterizable to a parameterized query expression by;

determining whether replacing the literals with a parameter will not change data or metadata transmitted back to a client; and

identifying as literals that are parameterizable to the parameterized query expression, ones of the literals for which the replacing will not change data or metadata transmitted back to the client,

wherein said parameter type bucketization logic component is configured to convert the parameter type having a length between 1 and n to a new type having a length equal to n, wherein said n is at least one of an integer or a real number.

6. The database server-side system of claim 5, wherein the parameter type bucketization logic component is configured to transform a plurality of parameterized query expressions, to a new parameterized query expression that is common to each of the plurality of the parameterized query expressions, wherein the plurality of the parameterized query expressions are those for which the optimizer component is configured to generate separate query plans.

7. The database server-side system of claim 6, wherein an execution plan generated by the query optimizer component for the new parameterized query expression is configured to be cached and shared with each of the plurality of parameterized query expressions.

8. The database server-side system of claim 5, wherein said type of the at least one parameter is at least one of a fixed length character (char) type, a variable length non-Unicode character data (varchar) type or a variable length binary data (varbinary) type.

9. The database server-side system of claim 5, wherein the parameter type bucketization logic component is further configured to rename a type for the at least one parameter according to a naming convention, wherein the bucketization logic component is configured to rename the type for the at least one parameter based, at least, on a value passed for the at least one parameter.

10. An application programming interface embodied on a computer-readable storage medium and configured to interface with the database server-side system of claim 5, and facilitate performing functions for which the parameter type bucketization logic component of the database server-side system is configured.

11. A computer-implemented method comprising:

receiving, by a database server-side object, a first parameterized query expression of a structured query language (SQL), wherein the first parameterized query expression includes at least one literal including a first parameter type;

transforming the first parameterized query expression to a first bucketized query expression according to a transformation process that changes the first parameter type to a new normalized parameter type, wherein the first parameter type is associated with first metadata to be returned by a query, and wherein the new normalized parameter type is associated with second metadata to be returned by the query, and wherein the transforming is performed based, at least, on determining that the second metadata would be the same as the first metadata;

generating a query execution plan based on the first bucketized query expression;

receiving, by the database server-side object, a second parameterized query expression of the query language, wherein the second parameterized query expression includes at least one literal including a second parameter type, wherein the second parameter type is different than the first parameter type;

transforming the second parameterized query expression to a second bucketized query expression according to the transformation process, whereby the transformation process changes the second parameter type to said new normalized parameter type, wherein the second parameter type is associated with third metadata to be returned by a query, and wherein the transforming is performed based, at least, on determining that the third metadata would be the same as the first metadata;

accessing said query execution plan generated based on the first bucketized query expression based, at least, on executing said second bucketized query expression;

identifying whether literals of a non-parameterized query statement are parameterizable to a parameterized query expression by;

determining whether replacing the literals with a parameter will not change data or metadata transmitted back to a client; and

identifying as literals that are parameterizable to the parameterized query expression, ones of the literals for which the replacing will not change data or metadata transmitted back to the client;

wherein the first parameter type and the second parameter type have a length in a range from 1 to n and the new normalized parameter type has a length equal to n, wherein said n is at least one of an integer or a real number.