SYSTEM AND METHODS OF IMPROVING A MULTI-TENANT DATABASE QUERY USING CONTEXTUAL KNOWLEDGE ABOUT NON-HOMOGENEOUSLY DISTRIBUTED TENANT DATA

• US 20130060792A1
• Filed: 09/14/2012
• Published: 03/07/2013
• Est. Priority Date: 09/23/2003
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Weissman, Craig, Moellenhoff, Dave, Wong, Simon
• Granted Patent: US 9,275,105 B2
• Time in Patent Office: Days
• US Class Current: 707/754
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24575 using context
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries

Abstract

In embodiments, methods and systems for improving a query in a database system are provided. These method and system embodiments can enable greater contextual knowledge about the types and use of data in tables underlying a relational database to be employed to improve query efficiency. By employing contextual information, embodiments can provide improved queries and/or make recommendations to a query optimizer of a database system to improve its operation based upon knowledge of the data and/or application gathered. Embodiments can be useful in improving query performance in multi-tenant database systems.

Claims

1. A computer program product, comprising a non-transitory computer usable medium having a computer readable program code embodied therein, the computer readable program code adapted to be executed to implement a method of improving a query in a database, the method comprising:

storing, in a database, tenant specific data for each of at least two tenants;

storing contextual information for each of the at least two tenants, the contextual information describing a characteristic of the tenant specific data stored for each of the at least two tenants, and the contextual information including permissions to perform a specific operation for each of the at least two tenants;

receiving a query directed to the database;

determining an improved query based at least in part upon the received query and the stored contextual information for a particular one of the at least two tenants to which the query is addressed, such that the improved query is customized for the tenant specific data specific to the particular one of the at least two tenants to which the query is addressed; and

providing, in place of the query, the improved query.

2. The computer program product of claim 1, wherein providing, in place of the query, an improved query includes:

selecting a set of contextual information applicable to the query from the contextual information applicable to the plurality of tenants in the database.

3. The computer program product of claim 1, further including storing at least one metadata table for use in determining the improved query.

4. The computer program product of claim 3, wherein each of the stored metadata tables is associated with each of the at least two tenants.

5. The computer program product of claim 3, wherein the at least one metadata table is used as a filter.

6. The computer program product of claim 3, wherein the tenant specific data is stored in the at least one metadata table for use in determining the improved query.

7. A method, comprising:

storing, in a database, tenant specific data for each of at least two tenants;

storing contextual information for each of the at least two tenants, the contextual information describing a characteristic of the tenant specific data stored for each of the at least two tenants, and the contextual information including permissions to perform a specific operation for each of the at least two tenants;

receiving a query directed to the database;

determining an improved query based at least in part upon the received query and the stored contextual information fora particular one of the at least two tenants to which the query is addressed, such that the improved query is customized for the tenant specific data specific to the particular one of the at least two tenants to which the query is addressed; and

providing, in place of the query, the improved query.

8. A system, comprising:

a processor; and

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

storing, in a database, tenant specific data for each of at least two tenants;

storing contextual information for each of the at least two tenants, the contextual information describing a characteristic of the tenant specific data stored for each of the at least two tenants, and the contextual information including permissions to perform a specific operation for each of the at least two tenants;

receiving a query directed to the database;

determining an improved query based at least in part upon the received query and the stored contextual information for a particular one of the at least two tenants to which the query is addressed, such that the improved query is customized for the tenant specific data specific to the particular one of the at least two tenants to which the query is addressed; and

providing, in place of the query, the improved query.