SERVER SIDE BUCKETIZATION OF PARAMETERIZED QUERIES

• US 20080065589A1
• Filed: 08/28/2006
• Published: 03/13/2008
• 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
• Granted Patent: US 7,865,515 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• 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 method for bucketizing at least one parameter of a parameterized expression of a query language for querying a data store, comprising:

receiving at least one parameterized query expression of a query language from a requesting object; and

generating at least one new parameterized query expression based on the at least one 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 type according to at least one pre-defined optimization algorithm.

2. The method according to claim 1, further comprising:

as a prerequisite for replacing the type of a given parameter of the at least one parameter, determining for the parameter whether said replacing of the type of the parameter will change any semantic information of the at least one parameterized query expression, and performing said replacing only if said semantic information will remain unchanged.

3. The method of claim 2, wherein said determining includes determining whether said semantic information of the at least one parameterized query expression will change as a result of said replacing by using a mechanism for determining, by a database server, which literals of a non-parameterized query statement are parameterizable to a parameterized query expression.

4. The method according to claim 1, wherein said generating includes replacing a type of the at least one parameter having a length n with a new type having a length greater than n.

5. The method according to claim 1, wherein said generating includes replacing the type of at least one variable of the at least one parameterized query expression with a new type according to at least one optimization algorithm.

6. The method according to claim 1, wherein said new type is broader than said type.

7. The method according to claim 1, wherein said new type is more specialized than said type.

8. A computer readable medium comprising computer executable instructions for performing the method of claim 1.

9. A computer device comprising means for performing the method of claim 1.

10. A database server for maintaining and updating a data store, comprising:

a parameter type bucketization logic component that receives at least one parameterized query expression of a query language and generates at least one new parameterized query expression based on the at least one parameterized query expression by converting a type of at least one parameter of the at least one parameterized query expression to a new type according to at least one optimization algorithm; and

a query optimizer component that receives the at least one new parameterized query expression and generates an optimized query plan according to the at least one new parameterized query expression.

11. The database server of claim 10, wherein the parameter type bucketization logic component includes an extension to a server-side parameterization framework of a structured query language (SQL) server that determines whether semantic information of the at least one parameterized query expression will change as a result of converting the type to the new type.

12. The database server of claim 11, wherein the extension employs a mechanism of the server-side parameterization framework that determines which literals of a non-parameterized query statement are parameterizable to a parameterized query expression to determine whether the semantic information of the at least one parameterized query expression will change.

13. The database server of claim 10, wherein said parameter type bucketization logic component converts a type having a length n within a range to a new type having a length greater than n such that any parameter of the type having a length n within the range is converted to said length greater than n.

14. The database server of claim 10, wherein the parameter type bucketization logic component transforms a plurality of parameterized query expressions which would otherwise generate separate query plans by the optimizer component to a new parameterized query expression that is common to transforming each of the plurality of parameterized query expressions.

15. The database server of claim 14, wherein the execution plan generated by the query optimizer component for the new parameterized query expression is cached and shared in connection with executing each of the plurality of parameterized query expressions.

16. The database server of claim 10, wherein said type is any of a char type, varchar type or a varbinary type.

17. The database server of claim 10, wherein the parameter type bucketization logic component converts the type of the at least one parameter of the at least one parameterized query expression to the new type according to at least one pre-defined optimization algorithm and re-names the at least one parameter according to naming convention.

18. An application programming interface for interfacing with the database server of claim 10.

19. A method for processing a plurality of parameterized expressions of a query language, comprising:

receiving a first parameterized query expression of the query language by a database server object, 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 parameter type;

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

receiving a second parameterized query expression of the query language by the database server object, wherein the second parameterized query expression includes at least one literal including a second parameter type 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 parameter type; and

accessing said query execution plan generated based on the first bucketized query expression when executing said second bucketized query expression, wherein said first bucketized query expression is equivalent to said second bucketized query expression.

20. The method of claim 19, further including:

prior to said transforming steps, determining whether the semantics of said first and second parameterized query expressions will change if transformed according to said transforming steps.