METHODS AND SYSTEMS FOR OPTIMIZING QUERIES IN A MULTI-TENANT STORE

• US 20130238595A1
• Filed: 04/23/2013
• Published: 09/12/2013
• Est. Priority Date: 04/19/2010
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: WEISSMAN, CRAIG, TAYLOR, JAMES
• Granted Patent: US 9,507,822 B2
• Time in Patent Office: Days
• US Class Current: 707/713
• CPC Class Codes: G06F 16/24 Querying
• CPC Class Codes: G06F 16/242 Query formulation
• CPC Class Codes: G06F 16/2425 Iterative querying; Query formulation based on the results of a preceding query
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24534 Query rewriting; Transformation
• CPC Class Codes: G06F 16/24537 of operators
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/24578 using ranking
• CPC Class Codes: G06F 16/256 in federated or virtual databases

Abstract

Methods and systems for optimizing queries in a multi-tenant store are described. In one embodiment, such a method includes retrieving data from a multi-tenant database system having a relational data store and a non-relational data store, receiving a request specifying data to be retrieved, retrieving one or more locations of the data to be retrieved, generating a database query based on the request, in which the database query specifies a plurality of data elements to be retrieved, the plurality of data elements including one or more data elements residing within the non-relational data store and one or more other data elements residing within the relational data store, generating an optimized database query having an optimized query syntax that is distinct from a query syntax of the database query, and executing the optimized database query against the multi-tenant database system to retrieve the data.

Claims

1. A method of optimizing database queries in a multi-tenant database system having a relational data store and a non-relational data store, the method comprising:

receiving a request at a host system for the multi-tenant database system, the request specifying data to be retrieved from the multi-tenant database system;

retrieving, based on the request via the host system, one or more locations of the data to be retrieved;

generating, at the host system, a database query based on the request, wherein the database query specifies a plurality of data elements to be retrieved, the plurality of data elements including one or more data elements residing within the non-relational data store and one or more other data elements residing within the relational data store;

optimizing the database query via a query optimizer to generate an optimized database query having an optimized query syntax that is distinct from a query syntax of the database query; and

executing the optimized database query against the multi-tenant database system to retrieve the data.

2. The method of claim 1:

wherein the database query comprises a specified order for two or more database join operations; and

wherein optimizing the database query comprises modifying the specified order for the two or more database join operations.

3. The method of claim 2, wherein at least one of the database join operations comprises joining a child table from the non-relational data store with a master table from the relational data store.

4. The method of claim 1, wherein optimizing the database query comprises:

identifying a first sub-query to a table within relational data store, the first sub-query corresponding to the first portion of the data to be retrieved;

identifying a second sub-query to the non-relational data store;

the second sub-query corresponding to the second portion of the data to be retrieved;

replicating the table within the relational data store to the non-relational data store; and

generating the optimized database query with one or more sub-queries to fetch both the first portion of the data to be retrieved and the second portion of the data to be retrieved from the non-relational data store.

5. The method of claim 1, wherein optimizing the database query comprises:

identifying a first sub-query to the non-relational data store;

identifying a second sub-query to the relational data store, wherein the second sub-query comprises a data delta query to an append log of the relational data store for new or updated data elements within the scope of the first sub-query to the non-relational data store which are not yet flushed from the append log of the relational data store to the non-relational data store;

executing a flush against the relational data store to flush the new or updated data elements within the append log of the relational data store to the non-relational data store; and

eliminating the second sub-query to the relational data store from the optimized database query.

6. The method of claim 1, wherein executing the optimized database query against the multi-tenant database system to retrieve the data comprises:

designating the data to be retrieved as a massive data set based on a comparison of an estimated or actual size of the data to be retrieved against a massive query threshold;

breaking up the optimized database query into a plurality of optimized sub-queries, each optimized sub-query corresponding to a portion of the massive data set;

allocating execution of the plurality of optimized sub-queries to one work thread processor within a pool of work thread processors, wherein the one work thread processor to establish a spooling algorithm internal to the one work thread processor to spool/cache one or more portions of the massive data set returned to the one work thread processor responsive to execution of one or more of the plurality of optimized sub-queries, and wherein the spooling algorithm retrieves a re-requested portion of the massive data set from the spool/cache, when available, in place of re-executing a corresponding optimized sub-query to retrieve the re-requested portion.

7. The method of claim 1, wherein executing the optimized database query against the multi-tenant database system to retrieve the data comprises:

sending the optimized database query to a query layer agent of the multi-tenant database system, wherein the query layer agent executes a plurality of sub-queries constituting the optimized database query against the relational data store and the non-relational data store based in part on the customer schema and based further on an optimized query syntax defined by the plurality of optimized sub-queries.

8. The method of claim 1, wherein the multi-tenant database system further comprises elements of hardware and software that are shared by a plurality of separate and distinct customer organizations, each of the separate and distinct customer organizations being remotely located from a host organization having the multi-tenant database system executing therein.

9. A non-transitory computer readable storage medium having instructions stored thereon that, when executed by processor in a host system, perform a method comprising:

receiving a request at a host system for the multi-tenant database system, the request specifying data to be retrieved from the multi-tenant database system;

retrieving one or more locations of the data to be retrieved based on the request;

generating a database query based on the request, wherein the database query specifies a plurality of data elements to be retrieved, the plurality of data elements including one or more data elements residing within the non-relational data store and one or more other data elements residing within the relational data store;

optimizing the database query to generate an optimized database query having an optimized query syntax that is distinct from a query syntax of the database query; and

executing the optimized database query against the multi-tenant database system to retrieve the data.

10. A system comprising:

a processor and a memory;

a communications interface to a multi-tenant database system having a relational data store and a non-relational data store implemented therein;

a request processor to receive a request specifying data to be retrieved from the multi-tenant database system;

a customer schema processor to retrieve, based on the request, one or more locations of the data to be retrieved;

a query layer agent to generate a database query based on the request, wherein the database query specifies a plurality of data elements to be retrieved, the plurality of data elements including one or more data elements residing within the non-relational data store of the multi-tenant database system and one or more other data elements residing within the relational data store of the multi-tenant database system;

a query optimizer to optimize the database query to generate an optimized database query having an optimized query syntax that is distinct from a query syntax of the database query; and

wherein the query layer agent to execute the optimized database query against the multi-tenant database system to retrieve the data.