REUSING EXISTING QUERY PLANS IN A DATABASE SYSTEM

• US 20130198166A1
• Filed: 01/30/2013
• Published: 08/01/2013
• Est. Priority Date: 01/30/2012
• Status: Active Grant

• Current Assignee: Singlestore, Inc.
• Original Assignee: MemSQL, Inc.
• Inventors: Tintor, Marko, PROUT, Adam, Skidanov, Alex
• Granted Patent: US 9,317,552 B2
• Time in Patent Office: Days
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: G06F 16/2453 Query optimisation

Abstract

A system and method for executing query plans preprocesses a user'"'"'s queries to identify and extract select parameters of the query, creates a skeletal query with reference to the extracted parameters, creates and compiles an executable version of the skeletal query, then executes the compiled version of the skeletal query using the particular parameters in the current query. The compiled version of the parameterized skeletal query is stored, and when another query is submitted that matches the skeletal query (but with potentially different parameters), the previously compiled skeleton query is executed with the parameters of this latter query.

Claims

1. A method comprising:

receiving a user query for selecting data from a database;

parsing the user query to identify a form of the user query and one or more arguments associated with the user query;

determining whether the form of the user query corresponds to a skeletal query form of a set of skeletal query forms; and

,

if the form of the user query corresponds to the skeletal query form;

retrieving an executable copy of code corresponding to the skeletal query form;

executing the code corresponding to the skeletal query form using the one or more arguments associated with the user query; and

returning results of the execution of the code in response to the user query.

2. The method of claim 1, including:

if the form of the user query does not correspond to a skeletal query form;

creating a new skeletal query form corresponding to the form of the user query with referential arguments corresponding to the identified arguments associated with the user query; and

providing executable code corresponding to the new skeletal query form for executing the user query with a reference to the one or more arguments associated with the user query;

executing the executable code using the one or more arguments associated with the user query and

returning results of the execution of the code in response to the user query.

3. The method of claim 2, including storing the executable code corresponding to the new skeletal query form for subsequent retrieval.

4. The method of claim 1, wherein the identified one or more arguments includes a reference to a plurality of parameter arguments and the parsing of the query includes generating values of at least some of the arguments associated with the user query.

5. The method of claim 1, wherein if the form of the user query does not correspond to the form of a skeletal query of the set of skeletal query forms, the method includes submitting the user query, unaltered, to an underlying query system.

6. The method of claim 5, including, after submitting the user query to the underlying query system:

creating a new skeletal query form corresponding to the form of the user query with referential arguments corresponding to the identified arguments associated with the user query;

providing executable code for executing the user query with a reference to the one or more arguments associated with the user query; and

storing the executable code for subsequent retrieval.

7. The method of claim 1, wherein the user query includes a search of the database for records having a parameter value equal to one or more user identified values, and the one or more arguments of the user query include the one or more user identified values.

8. The method of claim 1, wherein the user query conforms to a Structured Query Language (SQL) query.

9. A non-transitory computer readable medium that includes a computer program, which, when executed by a processing system, causes the processing system to:

receive a user query for selecting data from a database;

parse the user query to identify a form of the user query and one or more arguments associated with the user query;

determine whether the form of the user query corresponds to a skeletal query form of a set of skeletal query forms; and

,

if the form of the user query corresponds to the skeletal query form;

retrieve an executable copy of code corresponding to the skeletal query form;

execute the code corresponding to the skeletal query form using the one or more arguments associated with the user query; and

return results of the execution of the code in response to the user query.

10. The medium of claim 9, wherein, if the form of the user query does not correspond to a skeletal query form, the computer program causes the processing system to:

create a new skeletal query form corresponding to the form of the user query with referential arguments corresponding to the identified arguments associated with the user query; and

provide executable code corresponding to the new skeletal query form for executing the user query with a reference to the one or more arguments associated with the user query;

execute the executable code using the one or more arguments associated with the user query; and

return results of the execution of the code in response to the user query.

11. The medium of claim 10, wherein the computer program causes the processing system to store the executable code corresponding to the new skeletal query form for subsequent retrieval.

12. The medium of claim 9, wherein the identified one or more arguments includes a reference to a plurality of parameter arguments and the parsing of the query includes generating values of at least some of the arguments associated with the user query.

13. The medium of claim 9, wherein if the form of the user query does not correspond to the form of a skeletal query of the set of skeletal query forms, the computer program causes the processing system to submit the user query, unaltered, to an underlying query system.

14. The medium of claim 13, wherein the computer program causes the processing system to subsequently, after the user query is submitted to the underlying query system:

create a new skeletal query form corresponding to the form of the user query with referential arguments corresponding to the identified arguments associated with the user query;

provide executable code for executing the user query with a reference to the one or more arguments associated with the user query; and

store the executable code for subsequent retrieval.

15. The medium of claim 9, wherein the user query includes a search of the database for records having a parameter value equal to one or more user identified values, and the one or more arguments of the user query include the one or more user identified values.

16. The medium of claim 9, wherein the user query conforms to a Structured Query Language (SQL) query.

17. A system comprising:

a database,

a query parser, and

a processor,

wherein;

the query parser receives a user query for selecting data from the database, and parses the user query to identify a form of the user query and one or more arguments associated with the user query; and

the processor determines whether the form of the user query corresponds to a skeletal query form of a set of skeletal query forms, and, if the form of the user query corresponds to the skeletal query form;

retrieves an executable copy of code corresponding to the skeletal query form;

executes the code corresponding to the skeletal query form using the one or more arguments associated with the user query; and

returns results of the execution of the code in response to the user query.

18. The system of claim 17, including a query compiler, and wherein, if the form of the user query does not correspond to a skeletal query form:

the processor creates a new skeletal query form corresponding to the form of the user query with referential arguments corresponding to the identified arguments associated with the user query; and

the query compiler creates executable code corresponding to the new skeletal query form for executing the user query with a reference to the one or more arguments associated with the user query; and

the processor executes the executable code and returns results of the execution of the code in response to the user query.

19. The system of claim 18, including a memory, and wherein the processor stores the executable code corresponding to the new skeletal query form in the memory for subsequent retrieval.

20. The system of claim 17, wherein the identified one or more arguments includes a reference to a plurality of parameter arguments and query parser generates values of at least some of the arguments associated with the user query.

21. The system of claim 17, wherein if the form of the user query does not correspond to the form of a skeletal query of the set of skeletal query forms, the processor submits the user query, unaltered, to an underlying query system.

22. The system of claim 21, including a query compiler and a memory, and wherein, after the user query is submitted to the underlying query system:

the processor creates a new skeletal query form corresponding to the form of the user query with referential arguments corresponding to the identified arguments associated with the user query;

the query compiler creates executable code for executing the user query with a reference to the one or more arguments associated with the user query; and

the processor stores the executable code in the memory for subsequent retrieval.

23. The system of claim 17, wherein the user query includes a search of the database for records having a parameter value equal to one or more user identified values, and the one or more arguments of the user query include the one or more user identified values.

24. The system of claim 17, wherein the user query conforms to a Structured Query Language (SQL) query.