Patent Application
20120330924
Embodiments of the subject matter described herein relate generally to computer systems and networks configured to support applications executing on behalf of users accessing them as services. More particularly, embodiments of the subject matter relate to methods and systems for efficiently querying a database service being provided in an on demand environment.
Modern software development is evolving away from the client-server model toward network-based processing systems that provide access to data and services via the Internet or other networks. In contrast to traditional systems that host networked applications on dedicated server hardware, a “cloud” computing model allows applications to be provided over the network supplied by an infrastructure provider. The infrastructure provider typically abstracts the underlying hardware and other resources used to deliver a customer-developed application so that the customer no longer needs to operate and support dedicated server hardware. The cloud computing model can often provide substantial cost savings to the customer over the life of the application because the customer no longer needs to provide dedicated network infrastructure, electrical and temperature controls, physical security and other logistics in support of dedicated server hardware.
Cloud-based architectures have been developed to improve collaboration, integration, and community-based cooperation between customer tenants without sacrificing data security. During operation, there are numerous situations in which data and/or information needs to be retrieved (e.g., for presentation to users) from a database being provided to customers in such a cloud-based environment. Conventional databases include management software that determines what the database manufacturer considers to be an “optimal query plan” for executing the query and retrieving the desired set of data and/or information. However, the manufacturer's database management software is not always fully adapted for use in the cloud environment, so the “optimal query plan” generated by such conventional systems does not reflect the nature of users querying the database, and therefore, may actually produce “optimal” query plans that are not in fact optimal when used in the cloud environment. What is really needed is a remedy to this and other shortcomings of the traditional database manager.
A more complete understanding of the subject matter may be derived by referring to the detailed description and claims when considered in conjunction with the following figures, wherein like reference numbers refer to similar elements throughout the figures.
Select embodiments may employ the techniques described herein implemented as one or a combination of methods, systems or processor executed code to form an improved query plan based at least in part upon a query to a database received from a submitter in an on demand environment. In one example embodiment, guidance information may be used to provide a “hint” to an on-demand database service's database management system in accordance with the improved query plan. Guidance information may be determined at least in part on an identity of source of the input query, for example. As used herein, guidance information may take one or a combination of forms, such as without limitation, a metric, a value, integer, numeral, text, or the like, that is indicative of or otherwise influenced by one or more entries, qualities or characteristics of a particular submitter in the on demand database service. As will be described below with reference to specific embodiments, hints provided to a database management system may indicate to the database management system, and/or query optimizer, how at least a portion of an improved query plan can be can be generated or otherwise constructed. Accordingly, exploiting such guidance information can thereby enable querying an on demand database service more efficiently.
While implementation specific differences exist, some embodiments may employ multi-tenancy techniques when providing the above described benefits, however multi-tenancy is not required by all embodiments. Generally speaking, multi-tenancy refers to a technique where a hardware and software platform simultaneously supports multiple user groups (also referred to as “organizations” or “tenants”) from a common computational resource, such as a data storage element. An example type of multi-tenant data storage is a relational database (referred to as a “multi-tenant database”), however embodiments may be realized using object oriented and other types of databases as well. For example, a “tenant” or an “organization” can be used to refer to a group of one or more users that shares access to common subset of the data within the multi-tenant database. In this regard, each tenant includes one or more users associated with, assigned to, or otherwise belonging to that respective tenant. Tenants may represent customers, customer departments, business or legal organizations, and/or any other entities that maintain data for particular sets of users within a common multi-tenant system.
Although multiple tenants may share access to the server 102 and the database 130, the particular data and services provided from the server 102 to each tenant can be securely isolated from those provided to other tenants. The multi-tenant architecture therefore allows different sets of users to share functionality without necessarily sharing any of the data 132 belonging to or otherwise associated with other tenants. Multi-tenant design choices can enable one or more advantages over conventional server virtualization systems. First, the multi-tenant platform operator can often make improvements to the platform based upon collective information from the entire tenant community. Additionally, because all users in the multi-tenant environment execute applications within a common processing space, it is relatively easy to grant or deny access to specific sets of data for any user within the multi-tenant platform, thereby improving collaboration and integration between applications and the data managed by the various applications. The multi-tenant architecture therefore allows convenient and cost effective sharing of similar application features between multiple sets of users.
Turning now to
The database 130 is any sort of repository or other data storage system capable of storing and managing the data 132 associated with any number of submitters. The database 130 may be implemented using any type of conventional database server hardware. In various embodiments, the database 130 shares processing hardware 104 with the server 102. In other embodiments, the database 130 is implemented using separate physical and/or virtual database server hardware that communicates with the server 102 to perform the various functions described herein. In an exemplary embodiment, the database 130 includes a database management system or other equivalent software capable of determining a query plan for retrieving and providing a particular subset of the data 132 to an instance of virtual application 128 in response to a query initiated or otherwise provided by a user of a client device 140, as described in greater detail below.
In practice, the data 132 may be organized and formatted in any manner to support the application platform 110. In various embodiments, the data 132 is suitably organized into a relatively small number of large data tables to maintain a semi-amorphous “heap”-type format. The data 132 can then be organized as needed for a particular virtual application 128. In various embodiments, conventional data relationships are established using any number of pivot tables 134 that establish indexing, uniqueness, relationships between entities, and/or other aspects of conventional database organization as desired. Further data manipulation and report formatting is generally performed at run-time using a variety of metadata constructs. Metadata within a universal data directory (UDD) 136, for example, can be used to describe any number of forms, reports, workflows, user access privileges, business logic and other constructs that are common Tenant-specific formatting, functions and other constructs may be maintained as tenant-specific metadata 138 for each tenant, as desired. Rather than forcing the data 132 into an inflexible global structure that is common to all tenants and applications, the database 130 is organized to be relatively amorphous, with the pivot tables 134 and the metadata 138 providing additional structure on an as-needed basis. To that end, the application platform 110 suitably uses the pivot tables 134 and/or the metadata 138 to generate “virtual” components of the virtual applications 128 to logically obtain, process, and present the relatively amorphous data 132 from the database 130.
The server 102 is implemented using one or more actual and/or virtual computing systems that collectively provide the dynamic application platform 110 for generating the virtual applications 128. For example, the server 102 may be implemented using a cluster of actual and/or virtual servers operating in conjunction with each other, typically in association with conventional network communications, cluster management, load balancing and other features as appropriate. The server 102 operates with any sort of conventional processing hardware 104, such as a processor 105, memory 106, input/output features 107 and the like. The input/output features 107 generally represent the interface(s) to networks (e.g., to the network 145, or any other local area, wide area or other network), mass storage, display devices, data entry devices and/or the like. The processor 105 may be implemented using any suitable processing system, such as one or more processors, controllers, microprocessors, microcontrollers, processing cores and/or other computing resources spread across any number of distributed or integrated systems, including any number of “cloud-based” or other virtual systems. The memory 106 represents any non-transitory short or long term storage or other computer-readable media capable of storing programming instructions for execution on the processor 105, including any sort of random access memory (RAM), read only memory (ROM), flash memory, magnetic or optical mass storage, and/or the like. The computer-executable programming instructions, when read and executed by the server 102 and/or processor 105, cause the server 102 and/or processor 105 to establish, generate, or otherwise facilitate the application platform 110 and/or virtual applications 128 and perform additional tasks, operations, functions, and processes herein. It should be noted that the memory 106 represents one suitable implementation of such computer-readable media, and alternatively or additionally, the server 102 could receive and cooperate with computer-readable media (not separately shown) that is realized as a portable or mobile component or platform, e.g., a portable hard drive, a USB flash drive, an optical disc, or the like.
The application platform 110 is any sort of software application or other data processing engine that generates the virtual applications 128 that provide data and/or services to the client devices 140. In a typical embodiment, the application platform 110 gains access to processing resources, communications interfaces and other features of the processing hardware 104 using any sort of conventional or proprietary operating system 108. The virtual applications 128 are typically generated at run-time in response to input received from the client devices 140. For the illustrated embodiment, the application platform 110 includes a bulk data processing engine 112, a query generator 114, a search engine 116 that provides text indexing and other search functionality, and a runtime application generator 120. Each of these features may be implemented as a separate process or other module, and many equivalent embodiments could include different and/or additional features, components or other modules as desired.
The runtime application generator 120 dynamically builds and executes the virtual applications 128 in response to specific requests received from the client devices 140. The virtual applications 128 are typically constructed in accordance with the tenant-specific metadata 138, which describes the particular tables, reports, interfaces and/or other features of the particular application 128. In various embodiments, each virtual application 128 generates dynamic web content that can be served to a browser or other client program 142 associated with its client device 140, as appropriate.
The runtime application generator 120 suitably interacts with the query generator 114 to efficiently obtain data 132 from the database 130 as needed in response to input queries initiated or otherwise provided by users of the client devices 140. In a typical embodiment, the query generator 114 considers the identity of the user requesting a particular function (as well as possibly the user's associated tenant in some implementations), and then builds and executes queries to the database 130 using system-wide metadata 136, user specific metadata 138, pivot tables 134, and/or any other available resources. The query generator 114 in this example therefore maintains security of the common database 130 by ensuring that queries are consistent with access privileges granted to the user that initiated the request.
As will be described in greater detail below with reference to specific embodiments of
Still referring to
In operation, developers may use the application platform 110 to create data-driven virtual applications 128 for the customers that they support. Such virtual applications 128 may make use of interface features such as tenant-specific, for example, screens 124, universal screens 122 or the like. Any number of tenant-specific and/or universal objects 126 may also be available for integration into virtual applications 128. The data 132 associated with each virtual application 128 is provided to the database 130, as appropriate, and stored until it is requested or is otherwise needed, along with the metadata 138 that describes the particular features (e.g., reports, tables, functions, etc.) of that particular virtual application 128. For example, a virtual application 128 may include a number of objects 126 accessible to users of a particular tenant, for example, wherein for each object 126, information pertaining to its object type along with values for various fields associated with that respective object type are maintained as metadata 138 in the database 130. In this regard, the object type can define the structure (e.g., the formatting, functions and other constructs) of each respective object 126 and the various fields associated therewith.
Still referring to
As illustrated in
In an exemplary embodiment, the database management system 212 receives queries from the query generator 208. As illustrated, the database management system 212 generates or otherwise supports a query engine 214 that determines an improved query plan for performing the query. The database management system 212 then executes or otherwise performs the query in accordance with the query plan determined by the query engine 214 to retrieve the desired subset of the data maintained in the tables 210 and/or database 204, and the database management system 212 provides the retrieved data to the query generator 208 and/or virtual application 206 as the result of the query.
In one example embodiment, employing multi-tenancy referenced in the context of
Referring to
Still referring to
In an exemplary embodiment, in response to receiving the input query, the querying process 300 continues by identifying or otherwise determining the guidance information associated with the input query, such as utilization statistics of a particular submitter, or tenant, for example, and determining one or more database hints based on the input query and the guidance information, for the associated with the query submitter or tenant or the like (tasks 306, 308). In accordance with one embodiment, the virtual application 206 provides the query generator 208 with identifiers that indicate the appropriate guidance information, such as for example and without limitation an identity of a tenant and/or the submitter, associated with the virtual application 206 that generated the input query. The query generator 208 accesses or otherwise utilizes those identifiers to determine the appropriate guidance information (for example, the tenant associated with the submitter of the client device 140), and then obtains (for example, from memory 106) the guidance information corresponding to the query (for example, the database utilization statistics for the various tables of the database 204 that are associated with the query submitter).
After obtaining the statistics for the query, the query generator 208 determines one or more database hints based on the input query and the database utilization statistics for the query. In some embodiments, the database hints may be tenant-specific, in that the same input query from two different tenants may produce two different sets of database hints based on differences between the database utilization statistics for the two different tenants for example. In an exemplary embodiment, the database hints can include access paths (e.g., tables to be queried, indexes to be utilized, and the like), join methods or operations (e.g., the manner in which two or more tables should be combined for purposes of the query), join orders (e.g., the order in which two or more tables should be combined for purposes of the query), and/or the like. For example, based on the input query, the query generator 208 may initially identify or otherwise determine the possible combination of indexes and/or columns of the various tables 210 maintained by the database 204 to be queried. Based on the statistics associated with the query, the query generator 208 may then determine which of the tables 210 and/or indexes to be queried first and/or which of the tables 210 of the database 204 to be joined and/or combined to most likely obtain the results of the input query in accordance with some criterion or criteria, such as for example and without limitation, the lowest cost (e.g., least amount of time and/or computing resources required). To put it another way, based on the statistics for the query and the input query, the query generator 208 determines or otherwise identifies the most selective combinations of tables, indexes, access paths, join operations and/or join orders.
For example, the query generator 208 may determine that the input query requires data and/or information from a first column (Column 1) in a first table (Table 1) of the database 204 that has 100,000 entries (or rows) along with data and/or information from a second column (Column 2) in a second table (Table 2) of the database 204 that has 20,000 entries (or rows). Based on the number of rows in each of the identified tables that are associated with the query, the query generator 208 may determine or otherwise identify one of the columns as the primary index (or key) for executing the query. For example, if there are 5,000 entries applicable to the query in Table 1 and 10,000 entries applicable to the query in Table 2, the query generator 208 may determine that the database management system 212 should begin performing the query on Column 1 of Table 1 rather than Column 2 of Table 2, and thus, determine a database hint for the database management system 212 and/or query engine 214 that indicates the query plan should begin with Column 1 of Table 1 (or that Table 1 should proceed Table 2 in a particular join operation). In other situations, based on the statistics, the query generator 208 may determine that Column 2 of Table 2 is more selective (e.g., a greater number of distinct values across the entries applicable to the query) than Column 1 of Table 1 and determine that the database management system 212 should begin performing the query on Column 2 of Table 2 rather than Column 1 of Table 1 (or that Table 2 should proceed Table 1 in a particular join operation).
Still referring to
It should be noted that by virtue of the techniques described, embodiments can enable providing an improved query plan that can be more efficient or otherwise have a lower cost than the “optimal” query plan that a conventional database management system would have otherwise chosen. For example, absent the techniques described herein, in a join of Table 1 and Table 2, a conventional database management system may otherwise choose a query plan that begins with Table 2 (e.g., by having Table 2 precede Table 1 in a join order) based on the total number of entries in Table 2 (20,000) relative to the total number of entries in Table 1 (100,000), when in fact, there are fewer entries in Table 1 (e.g., 5,000 as compared to 10,000) that are applicable to the query, as discussed above. Thus, in contrast to such failings of conventional approaches, the improved query plans achieved by embodiments like those described herein may be more selective, more efficient, or otherwise achieve lower cost by enabling consideration for database utilization statistics which are otherwise unknown by the conventional approaches.
Referring again to
The foregoing description is merely illustrative in nature and is not intended to limit the embodiments of the subject matter or the application and uses of such embodiments. Furthermore, there is no intention to be bound by any expressed or implied theory presented in the technical field, background, or the detailed description. As used herein, the word “exemplary” means “serving as an example, instance, or illustration.” Any implementation described herein as exemplary is not necessarily to be construed as preferred or advantageous over other implementations, and the exemplary embodiments described herein are not intended to limit the scope or applicability of the subject matter in any way.
For the sake of brevity, conventional techniques related to computer programming, computer networking, database querying, and other functional aspects of the systems (and the individual operating components of the systems) may not be described in detail herein. In addition, those skilled in the art will appreciate that embodiments may be practiced in conjunction with any number of system and/or network architectures, data transmission protocols, and device configurations, and that the system described herein is merely one suitable example. Furthermore, certain terminology may be used herein for the purpose of reference only, and thus is not intended to be limiting. For example, the terms “first”, “second” and other such numerical terms do not imply a sequence or order unless clearly indicated by the context.
Embodiments of the subject matter may be described herein in terms of functional and/or logical block components and with reference to symbolic representations of operations, processing tasks, and functions that may be performed by various computing components or devices. Such operations, tasks, and functions are sometimes referred to as being computer-executed, computerized, software-implemented, or computer-implemented. In this regard, it should be appreciated that the various block components shown in the figures may be realized by any number of hardware, software, and/or firmware components configured to perform the specified functions. For example, an embodiment of a system or a component may employ various integrated circuit components, e.g., memory elements, digital signal processing elements, logic elements, look-up tables, or the like, which may carry out a variety of functions under the control of one or more microprocessors or other control devices. In this regard, the subject matter described herein can be implemented in the context of any computer-implemented system and/or in connection with two or more separate and distinct computer-implemented systems that cooperate and communicate with one another. In exemplary embodiments, the subject matter described herein can be implemented in conjunction with a virtual application, such as customer relationship management (CRM) in an on demand environment.
While at least one exemplary embodiment has been presented in the foregoing detailed description, it should be appreciated that a vast number of variations exist. It should also be appreciated that the exemplary embodiment or embodiments described herein are not intended to limit the scope, applicability, or configuration of the claimed subject matter in any way. Rather, the foregoing detailed description will provide those skilled in the art with a convenient road map for implementing the described embodiment or embodiments. It should be understood that various changes can be made in the function and arrangement of elements without departing from the scope defined by the claims, which includes known equivalents and foreseeable equivalents at the time of filing this patent application.
This application claims the benefit of U.S. provisional patent application Ser. No. 61/499,304, filed Jun. 21, 2011, the entire content of which is incorporated by reference herein.
| Number | Date | Country | |
|---|---|---|---|
| 61499304 | Jun 2011 | US |
