Techniques for optimization of inner queries

• US 10,152,511 B2
• Filed: 09/16/2013
• Issued: 12/11/2018
• Est. Priority Date: 09/14/2012
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Rajan, Chirag, Collins, Jesse, Bathija, Jaikumar, Kim, Hoon
• Primary Examiner(s): Alam, Hosain
• Assistant Examiner(s): Ahmed, Saba
• Application Number:
  US14/028,316
• Publication Number:
  US 20140081950A1
• Time in Patent Office: 1,912 Days
• Field of Search: 707715
• CPC Class Codes: G06F 16/24549 Run-time optimisation
• CPC Class Codes: G06F 16/2456 Join operations

Abstract

In an on demand database system, a query engine applies a custom index for inner queries. The query engine receives a query and determines that the query has an inner query nested within the primary query. The query engine identifies that a custom index exists for a client associated with the query, and applies the custom index to filter results for the query. The custom index includes a subset of information of a table that includes accounts for multiple different clients. By using the custom index, the query engine can filter the results more efficiently that if solely using the multiple client table.

Claims

1. A method for query optimization in an on demand database system in which a single table within a database of the database system stores account information for multiple different clients, the multiple different clients having corresponding custom database objects, the method comprising:

receiving, an original query having an outer query and one or more inner queries at a query engine of the on demand database system having at least one hardware processor coupled with at least one storage device storing the database, the original query associated with a client of the on demand database system and to be performed using data in the database belonging to the client including at least data stored on a custom database object corresponding to the client;

identifying, with the query engine, one or more inner queries nested within the original query;

identifying, with the query engine, a client-specific custom index for the client to which the original query corresponds, the client-specific custom index stored in the database, wherein the client-specific custom index includes a subset of entries specific to the client;

applying, with the query engine, the client-specific custom index to each of the one or more inner queries treating each or the one or more inner queries as independent queries;

performing, with the query engine, the optimized one or more inner queries on data stored in the single table prior to generating results for the original query; and

joining, with the query engine, results from the optimized one or more inner queries with results for the outer query to generate a result for the original query.

2. The method of claim 1, wherein the single table stores accounts for all clients of the on demand database system.

3. The method of claim 1, wherein the client-specific custom index includes a set of custom objects.

4. The method of claim 1, wherein determining the query has the nested query comprises parsing the query with the query engine into one or more nested queries with results to be combined as a result to the original query.

5. The method of claim 1, wherein the on demand database system includes multiple databases referenced by the single table, and wherein each of the multiple databases includes a query optimizer specific to queries for the respective database.

6. The method of claim 5, wherein applying the client-specific custom index further comprises the query engine identifying the client-specific custom index to the query optimizers.

7. An article of manufacture comprising a non-transitory computer readable storage medium having content stored thereon instructions, which when executed by one or more processors, for query optimization in an on demand database system in which a single table within a database of the database system stores account information for multiple different clients and the multiple different clients, the multiple different clients having corresponding custom database objects, the instructions to cause a machine to perform operations configurable to:

receive, an original query having an outer query and one or more inner queries at a query engine of the on demand database system having at least one hardware processor coupled with at least one storage device storing the database, the original query associated with a client of the on demand database system and to be performed using data in the database belonging to the client including at least data stored on a custom database object corresponding to the client;

identify, with the query engine, one or more inner queries nested within the original query;

identify, with the query engine, a client-specific custom index for the client to which the original query corresponds, the client-specific custom index stored in the database, wherein the client-specific custom index includes a subset of entries specific to the client;

apply, with the query engine, the client-specific custom index to each of the one or more inner queries treating each or the one or more inner queries as independent queries;

perform, with the query engine, the optimized one or more inner queries on data stored in the single table prior to generating results for the original query; and

join, with the query engine, results from the optimized one or more inner queries with results for the outer query to generate a result for the original query.

8. The article of manufacture of claim 7, wherein the single table stores accounts for all clients of the on demand database system.

9. The article of manufacture of claim 7, wherein the client-specific custom index includes a set of custom objects.

10. The article of manufacture of claim 7, wherein determining the query has the nested query comprises parsing the query with the query engine into one or more nested queries with results to be combined as a result to the original query.

11. The article of manufacture of claim 7, wherein the on demand database system includes multiple databases referenced by the single table, and wherein each of the multiple databases includes a query optimizer specific to queries for the respective database.

12. The article of manufacture of claim 11, wherein the content for applying the client-specific custom index further comprises content for the query engine identifying the client-specific custom index to the query optimizers.

13. A computer system comprising:

network interface hardware to connect to a multitenant database system (MTS), wherein the MTS stores data for multiple client organizations each identified by a tenant identifier (ID) and one or more users are associated with the tenant ID, wherein the one or more users of each client organization access data identified by the tenant ID associated with the respective client organization, and wherein the multitenant database is hosted by an entity separate from the client organization; and

a processor device to execute a query engine for the MTS, configurable to

receive, an original query having an outer query and one or more inner queries at a query engine of the on demand database system having at least one hardware processor coupled with at least one storage device storing the database, the original query associated with a client of the on demand database system and to be performed using data in the database belonging to the client including at least data stored on a custom database object corresponding to the client;

identify, with the query engine, one or more inner queries nested within the original query;

identify, with the query engine, a client-specific custom index for the client to which the original query corresponds, the client-specific custom index stored in the database, wherein the client-specific custom index includes a subset of entries specific to the client;

apply, with the query engine, the client-specific custom index to each of the one or more inner queries treating each or the one or more inner queries as independent queries;

perform, with the query engine, the optimized one or more inner queries on data stored in the single table prior to generating results for the original query; and

join, with the query engine, results from the optimized one or more inner queries with results for the outer query to generate a result for the original query.

14. The computer system of claim 13, wherein the single table stores accounts for all clients of the on demand database system.

15. The computer system of claim 13, wherein the query engine is to determine the query has the inner query by parsing the query with the query engine into one or more inner queries with results to be combined as a result to the query.

16. The computer system of claim 13, wherein the MTS includes multiple databases referenced by the single table, and wherein each of the multiple databases includes a query optimizer specific to queries for the respective database.

17. The computer system of claim 16, wherein the query engine is to apply the client-specific custom index by identifying the client-specific custom index to the query optimizers.