Statistics management for database querying

• US 8,577,870 B2
• Filed: 06/15/2012
• Issued: 11/05/2013
• Est. Priority Date: 06/21/2011
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Rajan, Chirag, Collins, Jesse, Yang, Seung-Yeoul, Kim, Hoon
• Primary Examiner(s): Nguyen, Loan T
• Application Number:
  US13/525,124
• Publication Number:
  US 20120330926A1
• Time in Patent Office: 508 Days
• Field of Search: None
• US Class Current: 707/713
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/2455 Query execution

Abstract

Methods and systems are provided for querying a database. One exemplary method for obtaining data from an on-demand database supporting one or more tenants involves obtaining model database statistics based on expected utilization information for a tenant and providing the model database statistics to the on-demand database. The on-demand database utilizes the model database statistics to generate a query plan and executes the query plan to obtain data from the model database statistics.

Claims

1. A method of obtaining data from a multi-tenant database, the method comprising:

modifying a development database representative of the multi-tenant database based on expected utilization information for a tenant supported by the multi-tenant database;

generating model database statistics based on the development database after modifying the development database, wherein generating the model database statistics comprises;

obtaining a subset of database statistics determined by a database management system of the development database after modifying the development database;

creating the model database statistics having values representative of an anticipated state of the multi-tenant database based at least in part on the obtained subset of the database statistics determined by the database management system of the development database; and

providing the model database statistics to the multi-tenant database by modifying on-demand database statistics maintained by the multi-tenant database to reflect the model database statistics, a query optimizer of the multi-tenant database generating a query plan based on the on-demand database statistics, wherein the query plan generated by the multi-tenant database to obtain the data is influenced by the model database statistics.

2. The method of claim 1, further comprising obtaining the expected utilization information for the multi-tenant database from the tenant.

3. The method of claim 1, further comprising determining the expected utilization information for the multi-tenant database based at least in part on existing utilization information for the tenant.

4. The method of claim 1, wherein creating the model database statistics comprises creating an extensible markup language (XML) file including the obtained subset of database statistics determined by the database management system.

5. The method of claim 4, further comprising:

maintaining, by a development server, the XML file including the obtained subset of database statistics determined by the database management system; and

maintaining, by an application server communicatively coupled to the development server, a second XML file containing reference database statistics for the multi-tenant database; and

modifying, by the application server, the second XML file to reflect the XML file maintained by the development server, wherein;

providing the model database statistics comprises automatically modifying, by the application server, the on-demand database statistics maintained by the multi-tenant database to reflect the second XML file after modifying the second XML file.

6. The method of claim 1, the expected utilization information corresponding to a new table to be created in the multi-tenant database, wherein modifying the development database comprises creating the new table in the development database.

7. The method of claim 6, the expected utilization information including an expected number of entries for the new table, wherein creating the new table comprises creating the new table having the expected number of entries.

8. The method of claim 6, the expected utilization information including expected value information for the new table, wherein creating the new table comprises creating an entry in the new table having an expected value corresponding to the expected value information.

17. The method of claim 1, the expected utilization information corresponding to a first table in the multi-tenant database, the multi-tenant database maintaining the on-demand database statistics including a subset corresponding to reference database statistics for the first table determined automatically by a database management system of the multi-tenant database in response to creating the first table, wherein providing the model database statistics to the multi-tenant database comprises modifying the subset of the on-demand database statistics to reflect the model database statistics.

18. The method of claim 1, further comprising:

maintaining, by a development server, an extensible markup language (XML) file including the model database statistics; and

maintaining, by an application server communicatively coupled to the development server, a second XML file containing reference database statistics for the multi-tenant database, wherein;

obtaining the subset of database statistics comprises a development server obtaining the subset from the development database;

creating the model database statistics comprises the development server creating the model database statistics; and

modifying the on-demand database statistics comprises;

identifying a difference between the reference database statistics and the model database statistics; and

in response to identifying the difference;

modifying, by the application server, the second XML file to reflect the XML file maintained by the development server so that portions of the reference database statistics that differ from the model database statistics have values from the XML file; and

modifying, by the application server, the on-demand database statistics maintained by the multi-tenant database to reflect the second XML file after modifying the second XML file.

19. The method of claim 1, the multi-tenant database maintaining reference database statistics for a first table of the multi-tenant database, wherein:

modifying the development database comprises modifying the first table of the development database based on the expected utilization information for the tenant for the first table; and

generating the model database statistics comprises generating the model database statistics for the first table based on the development database after modifying the first table of the development database.

20. The method of claim 1, further comprising:

maintaining, by a development server, an extensible markup language (XML) file including the model database statistics, the development server obtaining the subset from the development database and creating the model database statistics; and

maintaining, by an application server communicatively coupled to the development server, a second XML file containing reference database statistics for the multi-tenant database, wherein modifying the on-demand database statistics comprises;

identifying a difference between the reference database statistics and the model database statistics; and

in response to identifying the difference;

modifying, by the application server, the second XML file to reflect the XML file maintained by the development server so that portions of the reference database statistics that differ from the model database statistics have values from the XML file; and

modifying, by the application server, the on-demand database statistics maintained by the multi-tenant database to reflect the second XML file after modifying the second XML file.

9. A method of querying an on-demand database in a multi-tenant system including an application server providing instances of a virtual application over a network to a plurality of tenants, the method comprising:

instantiating a development database representative of the on-demand database;

obtaining expected utilization information for the on-demand database from one or more tenants of the plurality of tenants;

modifying the development database based on the expected utilization information;

generating model database statistics based on the development database after modifying the development database, wherein generating the model database statistics comprises;

obtaining a subset of database statistics determined by a database management system of the development database after modifying the development database;

creating the model database statistics having values representative of an anticipated state of the on-demand database based at least in part on the obtained subset of database statistics determined by the database management system of the development database;

obtaining, by the application server, the model database statistics;

modifying, by the application server, on-demand database statistics maintained by the on-demand database to reflect the model database statistics, resulting in updated database statistics maintained by the on-demand database; and

providing, by the application server, a query statement to the on-demand database, wherein a query optimizer of the on-demand database determines a query plan based on the query statement and the updated database statistics.

10. The method of claim 9, the on-demand database including one or more tables containing data for the plurality of tenants, wherein instantiating the development database comprises copying the one or more tables to the development database.

11. The method of claim 10, the expected utilization information corresponding to an expected number of entries for a first table of the one or more tables of the on-demand database, wherein modifying the development database comprises modifying the first table of the development database to have the expected number of entries.

12. The method of claim 10, the expected utilization information corresponding to an expected number of entries for a future table in the on-demand database, wherein modifying the development database comprises creating a new table in the development database having the expected number of entries.

13. The method of claim 9, the development database maintaining the database statistics determined after modifying the development database, wherein:

obtaining the subset of the database statistics comprises obtaining, from the development database, the subset of the database statistics determined by the development database after modifying the development database; and

creating the model database statistics comprises creating an extensible markup language (XML) file containing the model database statistics based at least in part on the obtained subset.

14. The method of claim 9, wherein the virtual application comprises a customer relationship management (CRM) application.

15. A computing system comprising a processor and a memory, wherein the memory comprises computer-executable instructions that, when executed by the processor, cause the computing system to:

obtain model database statistics for an on-demand database from a development database modified based on expected utilization information for one or more tenants having data maintained in the on-demand database, the model database statistics being created based at least in part on an obtained subset of database statistics determined by a database management system of the development database after the development database is modified, the model database statistics having values representative of an anticipated state of the on-demand database;

update on-demand database statistics maintained by the on-demand database to reflect the model database statistics; and

provide a query statement to the on-demand database, wherein a query optimizer of the on-demand database determines a query plan based on the updated on-demand database statistics and the query statement.

16. The computing system of claim 15, wherein the computer-executable instructions cause the computer system to modify a development database representative of the on-demand database based on the expected utilization information, the model database statistics being generated based on the development database after modifying the development database.