Transforming queries in a multi-tenant database system

• US 9,122,722 B2
• Filed: 12/22/2010
• Issued: 09/01/2015
• Est. Priority Date: 05/27/2010
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Collins, Jesse, Bathija, Jaikumar
• Primary Examiner(s): Pham, Khanh
• Assistant Examiner(s): HARPER, ELIYAH STONE
• Application Number:
  US12/977,041
• Publication Number:
  US 20110295838A1
• Time in Patent Office: 1,714 Days
• Field of Search: 707/705, 707/713
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2264 Multidimensional index structures
• CPC Class Codes: G06F 16/242 Query formulation
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24534 Query rewriting; Transformation
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/319 Inverted lists
• CPC Class Codes: G06F 16/9535 Search customisation based on user profiles and personalisation

Abstract

In a method, system, and computer-readable medium having instructions for optimizing a query in a database system, a query request is received with a query predicate to filter data returned in response to the query request and the query predicate has a formula, the query request is transformed to a transformed query request by preprocessing the formula in the query predicate, and the query request is optimized using the transformed query request.

Claims

1. A method for optimizing a query by a database system in a multi-tenant database system, the method comprising:

receiving a query request with a query predicate to filter data returned in response to the query request, wherein the query predicate comprises a formula;

accessing an index generated to correspond to one tenant of the multi-tenant database system;

preprocessing the formula in the query predicate based upon the generated index for the tenant to create a transformed query request, wherein the preprocessing includes;

applying the generated index to a database field referenced in the formula, and

replacing at least one reference to a database field within the formula with a reference to a second database field based upon the generated index;

optimizing the query request using the transformed query request;

receiving a query request with a reference to a first database field in the query predicate, wherein the first database field comprises the formula in the query predicate, wherein the formula comprises a reference to a second database field; and

transforming the query request to a transformed query request by replacing the reference to the first database field within the query request with at least one reference to the second database field.

2. The method for optimizing a query in a database system of claim 1, the method further comprising:

preprocessing the formula by performing an inverse function on a function within the formula.

4. The method for optimizing a query in a database system of claim 1, wherein the formula has a nested formula and the formula is flattened during preprocessing.

5. The method for optimizing a query in a database system of claim 1, the method further comprising:

preprocessing the formula by performing a range scan for the formula.

6. The method for optimizing a query in a database system of claim 1, the method further comprising:

generating a SQL database request with the transformed query request.

3. A method for optimizing a query in a multi-tenant database system, the method comprising:

receiving a query request with a query predicate to filter data returned in response to the query request, wherein the query predicate comprises a formula;

accessing an index generated to correspond to one tenant of the multi-tenant database system;

preprocessing the formula in the query predicate based upon the generated index for the tenant to create a transformed query request, wherein the preprocessing includes;

applying the generated index to a database field referenced in the formula, and

replacing at least one reference to a database field within the formula with a reference to a second database field based upon the generated index;

optimizing the query request using the transformed query request by determining a selective filter, selecting indexes, choosing where to apply hash joins and/or nested loop joins, and determine where to perform a full table order;

receiving a query request with a reference to a first database field in the query predicate, wherein the first database field comprises the formula in the query predicate, wherein the formula comprises a reference to a second database field; and

transforming the query request to a transformed query request by replacing the reference to the first database field within the query request with at least one reference to the second database field.

7. A non-transitory computer-readable storage medium having one or more instructions thereon for optimizing a query in a multi-tenant database system, the instructions when executed by one or more processors causing the one or more processors to carry out:

receiving a query request with a query predicate to filter data returned in response to the query request, wherein the query predicate comprises a formula;

accessing an index generated to correspond to one tenant of the multi-tenant database system;

preprocessing the formula in the query predicate based upon the generated index for the tenant to create a transformed query request, wherein the preprocessing includes;

applying the generated index to a database field referenced in the formula, and replacing at least one reference to a database field within the formula with a reference to a second database field based upon the generated index;

optimizing the query request using the transformed query request;

receiving a query request with a reference to a first database field in the query predicate, wherein the first database field comprises the formula in the query predicate, wherein the formula comprises a reference to a second database field; and

transforming the query request to a transformed query request by replacing the reference to the first database field within the query request with at least one reference to the second database field.

8. The non-transitory computer-readable storage medium of claim 7, the instructions further comprising:

preprocessing the formula by performing an inverse function on a function within the formula.

9. The non-transitory computer-readable storage medium of claim 7, wherein the formula has a nested formula and the formula is flattened during preprocessing.

10. The non-transitory computer-readable storage medium of claim 7, the instructions further comprising:

generating a SQL database request with the transformed query request.

11. A system for optimizing a query in a multi-tenant database system, the system comprising:

a processor; and

one or more stored sequences of instructions which, when executed by the processor, cause the processor to;

receiving a query request with a query predicate to filter data returned in response to the query request, wherein the query predicate comprises a formula;

generating an index corresponding to one tenant of the multi-tenant database system;

preprocessing the formula in the query predicate based upon the generated index for the tenant to create a transformed query request, wherein the preprocessing includes;

applying the generated index to a database field referenced in the formula,

replacing at least one reference to a database field within the formula with a reference to a second database field based upon the generated index; and

optimizing the query request using the transformed query request,

receiving a query request with a reference to a first database field in the query predicate, wherein the first database field comprises the formula in the query predicate, wherein the formula comprises a reference to a second database field; and

transforming the query request to a transformed query request by replacing the reference to the first database field within the query request with at least one reference to the second database field.

12. The system for optimizing a query in a database system of claim 11, wherein the one or more stored sequences of instructions which, when executed by the processor, cause the processor to further carry out:

preprocessing the formula by performing an inverse function on a function within the formula.

13. The system for optimizing a query in a database system of claim 11, wherein the formula has a nested formula and the formula is flattened during preprocessing.

14. The system for optimizing a query in a database system of claim 11, wherein the one or more stored sequences of instructions which, when executed by the processor, cause the processor to further carry out:

generating a SQL database request with the transformed query request.

15. The system for optimizing a query in a database system of claim 11, wherein the one or more stored sequences of instructions which, when executed by the processor, cause the processor to further carry out:

preprocessing the formula by performing a range scan for the formula.