System and method for dynamically creating keys in a database system

• US 7,739,287 B1
• Filed: 06/11/2004
• Issued: 06/15/2010
• Est. Priority Date: 06/11/2004
• Status: Active Grant

• Current Assignee: LexisNexis Risk Data Management, Inc. (RELX PLC)
• Original Assignee: Seisint, Inc.
• Inventors: Halliday, Gavin C., Poulsen, Ole D., Bayliss, David A., Hicks, Nigel G., Cobbett-Smith, Jacob, Chapman, Richard K.
• Primary Examiner(s): Cottingham; John R.
• Assistant Examiner(s): Reyes; Mariela D
• Application Number:
  US10/866,616
• Time in Patent Office: 2,195 Days
• Field of Search: None
• US Class Current: 707/741
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

A database system capable of dynamically creating one or more keys associated with one or more pre-compiled queries includes a plurality of slave nodes that are operable to execute one or more pre-compiled queries. Each of the plurality of slave nodes operates to store one or more usage statistics that relate to the execution of the one or more pre-compiled queries. The system also includes at least one master node that is coupled to each of the plurality of slave nodes. The at least one master node is operable to receive the one or more usage statistics from each of the plurality of slave nodes. Furthermore, the at least one master node is operable to identify, based at least in part on the one or more usage statistics, one or more keys to dynamically create. Moreover, each of the plurality of slave nodes dynamically creates the one or more keys for use in executing the one or more pre-compiled queries.

Claims

1. A database system capable of dynamically creating one or more keys associated with one or more pre-compiled queries, the system comprising:

a query module operating to generate one or more pre-compiled queries from a query representation received from a client;

a plurality of slave nodes operating to execute the one or more pre-compiled queries, each of the plurality of slave nodes operating to track, as the one or more pre-compiled queries are executed over a time period, one or more usage statistics identifying variables used to resolve the pre-compiled queries on each slave node; and

at least one master node coupled to each of the plurality of slave nodes, the at least one master node comprising a computer processor and operating to;

receive the one or more usage statistics from each of the plurality of slave nodes,

compute, based on the respective usage statistics from each slave node, a number of times the identified variables were used as the pre-complied queries were executed over the time period, and,

based at least in part on the number of times the identified variables were used during the time period, request the plurality of slave nodes to dynamically create one or more keys, the keys including pointers identifying locations of data on the slave nodes for resolving the pre-compiled queries;

wherein each of the plurality of slave nodes dynamically creates the one or more keys for use in executing the one or more pre-compiled queries.

2. The system of claim 1, wherein each of the plurality of slaves executes the one or more pre-compiled queries upon receiving a request to execute the one or more pre-compiled queries from the at least one master node.

3. The system of claim 1, wherein the at least one master node communicates a request to dynamically create one or more keys for use in executing the one or more pre-compiled queries to each of the plurality of slave nodes.

4. The system of claim 1, wherein the at least one master node identifies the one or more keys to dynamically create by identifying combinations of variables most used during execution of the one or more pre-compiled queries.

5. The system of claim 4, wherein the one or more usage statistics identify the combinations of variables used to resolve the one or more pre-compiled queries.

6. The system of claim 1, wherein the at least one master node identifies the one or more keys to dynamically create in response to the one or more pre-compiled queries being executed for a period of time on the database system.

7. The system of claim 1, wherein the at least one master node identifies the one or more keys to dynamically create in response to the one or more pre-compiled queries being executed a particular number of times on the database system.

8. The system of claim 1, wherein the at least one master node identifies another one or more keys to dynamically create based at least in part one or more usage statistics associated with another period of operation of the database system.

9. The system of claim 1, wherein the one or more keys are dynamically created while each of the plurality of slave nodes is capable of executing the one or more pre-compiled queries.

10. The system of claim 1, wherein at least some of the one or more pre-compiled queries are stored on the at least one master node and each of the plurality of slave nodes in advance of a user providing a data request to the database system.

11. The system of claim 1, wherein at least some of the one or more pre-compiled queries operates to resolve routine or standard data requests that are capable of having variations in input parameters or intermediate results.

12. The system of claim 1, wherein each of the plurality of slave nodes comprise one or more memory modules capable of storing one or more rows of data associated with a table.

13. The system of claim 1, wherein the one or more keys are stored in a Random Access Memory associated with each of the plurality of slave nodes.

14. The system of claim 1, wherein the pointers are stored as arrays.

15. The system of claim 1, further comprising a plurality of master nodes, each of the plurality of master nodes operable to receive the one or more usage statistics from each of the plurality of slave nodes and to identify one or more keys to dynamically create based at least in part on the one or more usage statistics.

28. The database system according to claim 1, wherein at least one of the keys identifies a location that is external to the database system.

29. The database system according to claim 1, wherein at least one of the keys identifies a location that is a memory address within the database system.

30. The database system according to claim 1, wherein the at least one master node distributes the one or more pre-compiled queries to another master node within the database system.

31. The database system according to claim 1, wherein the at least one master node determines that one of the pre-compiled queries can be fully executed on the at least one master node.

32. The database system according to claim 1, wherein the at least one master node determines that one of the pre-compiled queries calls for the interaction of at least one of the plurality of slave nodes.

33. The database system according to claim 1, wherein the at least one master node communicates a message to another master node in the database system that the at least one master node is processing the usage statistics and that it is not necessary for the other master node to process the usage statistics.

16. A method of dynamically creating one or more keys associated with one or more pre-compiled queries for use on a database system, the method comprising:

generating one or more pre-compiled queries from a query representation received from a client;

tracking, as the one or more pre-compiled queries are executed on a plurality of slave nodes over a time period, one or more usage statistics identifying variables used to resolve the pre-compiled queries on each of the plurality of slave nodes;

collecting, from each of the plurality of slave nodes, the one or more usage statistics associated with the one or more pre-compiled queries;

computing a number of times the identified variables were used as the pre-compiled queries were executed over the time period based at least on the respective usage statistics from each slave node;

based at least in part on the number of times the identified variables were used during the time period, requesting that the plurality of slave nodes dynamically create one or more keys, the keys including pointers identifying locations of data on the slave nodes for resolving the pre-compiled queries; and

at each of the plurality of slave nodes, dynamically creating the one or more keys.

17. The method of claim 16, wherein the one or more usage statistics identify combinations of variables used to resolve the one or more pre-compiled queries.

18. The method of claim 16, wherein the one or more usage statistics are collected using the at least one master node coupled to each of the plurality of slave nodes.

19. The method of claim 16, wherein the one more keys are dynamically created in response to the one or more pre-compiled queries being executed for a period of time on each of the plurality of slave nodes.

20. The method of claim 16, wherein the one or more keys are stored in a Random Access Memory associated with each of the plurality of slave nodes.

21. The method of claim 16, wherein the pointers are stored as arrays.

22. The method of claim 16, wherein at least one of the one or more pre-compiled queries operates to resolve a routine data request that is capable of having variations in input parameters.

23. The method of claim 16, further comprising dynamically creating another one or more keys for use in executing the one or more pre-compiled queries, the dynamic creation of the another one or more keys based at least in part on the collection of the one or more usage statistics associated with another period of operation of the database system.

24. The method of claim 16, further comprising:

distributing at least some of the one or more pre-compiled queries to a plurality of master nodes and each of the plurality of slave nodes in advance of a user providing a data request to the database system;

receiving a request to execute at least one of the one or more pre-compiled queries from at least one master node;

executing the pre-compiled query on each of the plurality of slave nodes; and

communicating the one or more usage statistics to one of the plurality of master nodes upon receiving a request from the at least one master node.

25. The method of claim 16, further comprising:

identifying the one or more keys to dynamically create based at least in part on the one or more usage statistics; and

communicating a request to dynamically create the one or more keys to each of the plurality of slave nodes.

26. The method of claim 25, wherein at least one master node identifies the one or more keys to dynamically create by tallying the usage statistics and identifying one or more combinations of variables most used during execution of the one or more pre-compiled queries.

27. A computer-implemented system for dynamically creating one or more keys, comprising:

one or more computer processors collectively operating to;

generate one or more pre-compiled queries from a query representation received from a client;

track, as the one or more pre-compiled queries are executed on a plurality of slave nodes over a time period, one or more usage statistics identifying variables used to resolve the pre-compiled queries on each of the plurality of slave nodes;

collect the one or more usage statistics associated with the one or more pre-compiled queries from each of the plurality of slave nodes;

compute a number of times the identified variables were used as the pre-compiled queries were executed over the time period based on the respective usage statistics from each slave node; and

based at least in part on the number of times the identified variables were used during the time period, request that the plurality of slave nodes dynamically create one or more keys, the keys including pointers identifying locations of data on the slave nodes for resolving the pre-compiled queries;

wherein the plurality of slave nodes dynamically create one or more keys.