Optimizing queries in a multi-tenant database system environment

• US 8,386,471 B2
• Filed: 09/17/2010
• Issued: 02/26/2013
• 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): Corrielus, Jean M
• Application Number:
  US12/885,255
• Publication Number:
  US 20110295839A1
• Time in Patent Office: 893 Days
• Field of Search: 707/812, 707/713, 707/716, 707/714, 707/715, 707/784, 707/770, 707/719, 717/173
• US Class Current: 707/716
• 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 database statistic is generated for a number of related records for one or more entities for at least one tenant and a related record is a record with a relationship to a shared record in a database table for an entity from the one or more entities, a first cost is calculated for accessing the number of related records for at least one tenant, a second cost is calculated for accessing a number of related records accessible to a user, a comparison of the first cost to the second cost is performed to determine a data access path for retrieving accessible related records, and the data access path for retrieving accessible related records is determined based upon the comparison.

Claims

1. A method for optimizing a query in a database system, the method comprising:

generating a database statistic for a number of related records for one or more entities for at least one tenant, wherein a related record comprises a record with a relationship to a shared record in a database table for an entity from the one or more entities;

calculating a first cost for accessing the number of related records for at least one tenant;

calculating a second cost for accessing a number of related records accessible to a user;

performing a comparison of the first cost to the second cost to determine a data access path for retrieving accessible related records; and

determining the data access path for retrieving accessible related records based upon the comparison.

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

retrieving a set of related records in accordance with a filter of a database query request; and

removing related records that are not accessible to the user from the set of related records.

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

retrieving one or more access rights for the user for the one or more entities; and

retrieving accessible related records in accordance with the one or more access rights.

4. The method for optimizing a query in a database system of claim 1, wherein the shared record comprises a record with access rights for one or more users.

5. The method for optimizing a query in a database system of claim 1, wherein the related record is a child record of the shared record.

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

generating a statistic for a number of public related records for the tenant;

determining whether a table for public related records is a relatively large size; and

determining the data access path for retrieving accessible related records when the table for public related records is a relatively large size, wherein the data access path for retrieving accessible related records comprises;

retrieving a set of related records in accordance with a filter of a database query request; and

removing related records that are not accessible to the user from the set of related records.

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

executing a sharing inline view query, wherein the sharing inline view query determines accessibility of a user for one or more related records.

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

generating a database statistic for a number of related records for one or more entities for at least one tenant, wherein a related record comprises a record with a relationship to a shared record in a database table for an entity from the one or more entities;

calculating a first cost for accessing the number of related records for at least one tenant;

calculating a second cost for accessing a number of related records accessible to a user;

performing a comparison of the first cost to the second cost to determine a data access path for retrieving accessible related records; and

determining the data access path for retrieving accessible related records based upon the comparison.

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

retrieving a set of related records in accordance with a filter of a database query request; and

removing related records that are not accessible to the user from the set of related records.

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

retrieving one or more access rights for the user for the one or more entities; and

retrieving accessible related records in accordance with the one or more access rights.

11. The computer-readable storage medium of claim 8, wherein the shared record comprises a record with access rights for one or more users.

12. The computer-readable storage medium of claim 8, wherein the related record is a child record of the shared record.

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

generating a statistic for a number of public related records for the tenant;

determining whether a table for public related records is a relatively large size; and

determining the data access path for retrieving accessible related records when the table for public related records is a relatively large size, wherein the data access path for retrieving accessible related records comprises;

retrieving a set of related records in accordance with a filter of a database query request; and

removing related records that are not accessible to the user from the set of related records.

14. A system for optimizing a query in a 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 carry out;

generating a database statistic for a number of related records for one or more entities for at least one tenant, wherein a related record comprises a record with a relationship to a shared record in a database table for an entity from the one or more entities;

calculating a first cost for accessing the number of related records for at least one tenant;

calculating a second cost for accessing a number of related records accessible to a user;

performing a comparison of the first cost to the second cost to determine a data access path for retrieving accessible related records; and

determining the data access path for retrieving accessible related records based upon the comparison.

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

retrieving a set of related records in accordance with a filter of a database query request; and

removing related records that are not accessible to the user from the set of related records.

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

retrieving one or more access rights for the user for the one or more entities; and

retrieving accessible related records in accordance with the one or more access rights.

17. The system for optimizing a query in a database system of claim 14, wherein the shared record comprises a record with access rights for one or more users.

18. The system for optimizing a query in a database system of claim 14, wherein the related record is a child record of the shared record.

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

generating a statistic for a number of public related records for the tenant;

determining whether a table for public related records is a relatively large size; and

determining the data access path for retrieving accessible related records when the table for public related records is a relatively large size, wherein the data access path for retrieving accessible related records comprises;

retrieving a set of related records in accordance with a filter of a database query request; and

removing related records that are not accessible to the user from the set of related records.

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

executing a sharing inline view query, wherein the sharing inline view query determines accessibility of a user for one or more related records.