System and method for efficient evaluation of a query that invokes a table valued function

• US 7,606,792 B2
• Filed: 06/30/2004
• Issued: 10/20/2009
• Est. Priority Date: 03/19/2004
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Sezgin, Beysim, Cunningham, Conor, Venkatesh, Ramachandran
• Primary Examiner(s): Alam; Hosian T
• Assistant Examiner(s): Saeed; Usmaan
• Application Number:
  US10/881,474
• Publication Number:
  US 20050209988A1
• Time in Patent Office: 1,938 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24547 Optimisations to support specific applications; Extensibility of optimisers
• CPC Class Codes: G06F 16/24553 of query operations
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• 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/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99936 Pattern matching access
• CPC Class Codes: Y10S 707/99939 Privileged access
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

The present invention describes an efficient and fast framework for implementing table-valued functions (TVF'"'"'s) in a relational database server. The framework provides desirable extensibility properties and has the ability to minimize performance degradations by integrating tightly with the optimization infrastructure in a query processor. The present invention also enables a managed execution environment (MEE) such as the Common-Language-Runtime (CLR), to efficiently and dynamically extend the logic of the database engine with user-supplied code.

Claims

1. A method for processing a query that invokes a table valued function to provide results, the method comprising:

encoding by a computer processor at least one implicit argument in the table valued function, each of the at least one implicit argument passing instructions to execute at least one relational operation normally executed separately from the table valued function in the table valued function, wherein each relational operation is a filtering operation, a grouping operation or a projection operation, and a separate runtime implementation is generated for each relational operation;

receiving a user defined class definition to instantiate a managed execution environment object;

compiling the user defined class definition into binary code;

storing the binary code at a database server;

upon a request, querying the binary code to determine metadata regarding the user defined class, wherein the binary code may be interpreted by the database server based upon the managed execution environment object;

receiving the query that invokes the table valued function;

responsive to receiving the query, generating by a computer processor an information class that describes capabilities of the table valued function;

querying by a computer processor the information class with a set of at least one binding questions regarding information about the table valued function comprising a number of columns that will be returned during query execution, types of columns, and/or names of the columns;

querying by a computer processor the information class with a set of at least one optimization questions regarding information about the table valued function comprising whether rows are grouped on any set of columns, whether any columns make up a unique key, whether the table value function is deterministic or accesses external data sources and/or statistical information;

executing by a computer processor the query in accordance with information provided in response to the set of binding questions and the set of optimization questions, wherein executing the query comprises;

generating an execution class corresponding to the table valued function;

requesting a set of data table rows as output from the execution class; and

receiving the requested rows from the execution class.

2. The method of claim 1, comprising querying the information class with a set of optimization questions comprising a request for statistical information that includes one of a number of rows that will be returned for each invocation of the table valued function, a number of rows that will be returned for a range of parameter values, a number of duplicate values that will be returned for a set of columns, and a histogram describing a distribution of values that will be returned from the table valued function.

3. The method of claim 1, further comprising binding the query in accordance with information provided in response to the set of binding questions.

4. The method of claim 1, further comprising optimizing the query in accordance with information provided in response to the set of optimization questions.

5. The method of claim 1, wherein executing the query further comprises verifying the validity of the capabilities of the table valued function.

6. The method of claim 5, further comprising:

determining that the capabilities of the table valued function are invalid; and

aborting the execution of the query.

7. The method of claim 1, further comprising invoking the table valued function to provide calculations to support hierarchical query operations.

8. The method of claim 1, further comprising invoking the table valued function to provide translations of a directory path to model a file system in a database system.

9. The method of claim 1, further comprising invoking the table valued function to provide results from a remote data source.

10. A computer readable storage medium for processing a query that invokes a table valued function to provide results, the computer readable storage medium having stored thereon instructions executed by a computer that cause the computer to perform a process comprising:

encoding at least one implicit argument in the table valued function, each of the at least one implicit argument passing instructions to execute at least one relational operation normally executed separately from the table valued function in the table valued function, wherein each relational operation is a filtering operation, a grouping operation or a projection operation, and a separate runtime implementation is generated for each relational operation;

receiving a user defined class definition to instantiate a managed execution environment object;

compiling the user defined class definition into binary code;

storing the binary code at a database server;

upon a request, querying the binary code to determine metadata regarding the user defined class, wherein the binary code may be interpreted by the database server based upon the managed execution environment object;

receiving the query that invokes the table valued function;

responsive to receiving the query, generating an information class that describes capabilities of the table valued function;

querying the information class with a set of at least one binding questions regarding information about the table valued function comprising a number of columns that will be returned during query execution, types of columns, and/or names of the columns;

querying the information class with a set of at least one optimization questions regarding information about the table valued function comprising whether rows are grouped on any set of columns, whether any columns make up a unique key, whether the table valued function is deterministic or accesses external data sources and/or statistical information;

executing the query in accordance with information provided in response to the set of binding questions and the set of optimization questions, wherein executing the query comprises;

generating an execution class corresponding to the table valued function;

requesting a set of data table rows as output from the execution class; and

receiving the requested rows from the execution class.

11. The computer readable storage medium of claim 10, wherein the statistical information includes one of a number of rows that will be returned for each invocation of the table valued function, a number of rows that will be returned for a range of parameter values, a number of duplicate values that will be returned for a set of columns, and a histogram describing a distribution of values that will be returned from the table valued function.

12. The computer readable storage medium of claim 10, wherein the process further comprises binding the query in accordance with information provided in response to the set of binding questions.

13. The computer readable storage medium of claim 10, wherein the process further comprises optimizing the query in accordance with information provided in response to the set of optimization questions.

14. The computer readable storage medium of claim 10, wherein executing the query further comprises verifying the validity of the capabilities of the table valued function.

15. The computer readable storage medium of claim 14, wherein the process further comprises:

determining that the capabilities of the table valued function are invalid; and

aborting the execution of the query.

16. The computer readable storage medium of claim 10, wherein the process further comprises invoking the table valued function to provide calculations to support hierarchical query operations.

17. The computer readable storage medium of claim 10, wherein the process further comprises invoking the table valued function to provide translations of a directory path to model a file system in a database system.

18. The computer readable storage medium of claim 10, wherein the process further comprises invoking the table valued function to provide results from a remote data source.

19. A system for processing a query that invokes a table valued function to provide results comprising:

a processor;

a memory having stored thereon instructions executed by the processor that cause the processor to perform a process comprising;

encoding at least one implicit argument in the table valued function, each of the at least one implicit argument passing instructions to execute at least one relational operation normally executed separately from the table valued function in the table valued function, wherein each relational operation is a filtering operation, a grouping operation or a projection operation, and a separate runtime implementation is generated for each relational operation;

receiving a user defined class definition to instantiate a managed execution environment object;

compiling the user defined class definition into binary code;

storing the binary code at a database server;

upon a request, querying the binary code to determine metadata regarding the user defined class, wherein the binary code may be interpreted by the database server based upon the managed execution environment object;

receiving the query that invokes the table valued function;

responsive to receiving the query, generating an information class that describes capabilities of the table valued function;

querying the information class with a set of at least one binding questions regarding information about the table valued function comprising a number of columns that will be returned during query execution, types of columns, and/or names of the columns;

querying the information class with a set of at least one optimization questions regarding information about the table valued function comprising whether rows are grouped on any set of columns, whether any columns make up a unique key, whether the table valued function is deterministic or accesses external data sources and/or statistical information;

executing the query in accordance with information provided in response to the set of binding questions and the set of optimization questions, wherein executing the query comprises;

generating an execution class corresponding to the table valued function;

requesting a set of data table rows as output from the execution class; and

receiving the requested rows from the execution class.

20. The system of claim 19, wherein the statistical information includes one of a number of rows that will be returned for each invocation of the table valued function, a number of rows that will be returned for a range of parameter values, a number of duplicate values that will be returned for a set of columns, and a histogram describing a distribution of values that will be returned from the table valued function.

21. The system of claim 19, wherein the process further comprises binding the query in accordance with information provided in response to the set of binding questions.

22. The system of claim 19, wherein the process further comprises optimizing the query in accordance with information provided in response to the set of optimization questions.

23. The system of claim 19, wherein executing the query further comprises verifying the validity of the capabilities of the table valued function.

24. The system of claim 23, wherein the process further comprises:

determining that the capabilities of the table valued function are invalid; and

aborting the execution of the query.

25. The system of claim 19, wherein the process further comprises invoking the table valued function to provide calculations to support hierarchical query operations.

26. The system of claim 19, wherein the process further comprises invoking the table valued function to provide translations of a directory path to model a file system in a database system.

27. The system of claim 19, wherein the process further comprises invoking the table valued function to provide results from a remote data source.