Patent Grant
8898186
A portion of the disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the records of the United States Patent and Trademark Office, but otherwise reserves all rights.
One or more implementations relate generally to database queries.
The subject matter discussed in the background section should not be assumed to be prior art merely as a result of its mention in the background section. Similarly, a problem mentioned in the background section or associated with the subject matter of the background section should not be assumed to have been previously recognized in the prior art. The subject matter in the background section merely represents different approaches, which may be unique on their own.
In conventional database systems, users access their data resources in a single logical database. A user of such a conventional database system typically retrieves data from and stores data on the database system using the user's own computing systems or devices. For example, a user system might remotely access one of a plurality of server systems that might in turn access the database system. Data retrieval from the system might include the issuance of a query from the user system to the database system. The database system might process the request for information received in the query and send to the user system information relevant to the request. The retrieval of accurate information and subsequent delivery of this information to the user system in a timely and efficient manner has been and continues to be a goal of administrators of database systems.
However, in a multi-tenant database system (“MTS”), various elements of hardware and software of the database system may be shared by one or more customers. The notion of “cloud computing” has recently developed whereby service providers offer access to hardware/software systems through a network, such as the Internet. In an MTS, for example, a specified application server may simultaneously process requests for many customers, and a specified database table may store and index data for many customers, such as contact data.
One commercially available option for retrieving information from a database is the Lucene database model. Lucene is an open source software library product used for information retrieval, supported by the Apache Software Foundation, which provides full text indexing and searching capability. See lucene.apache.org. The logical architecture of Lucene defines an index as a sequence of documents; a document as a sequence of fields; a field is a named sequence of terms; and a term is a string. However, the physical structure stores data literally, meaning the entire data string is referenced in any access or query.
Conventional database systems are necessarily limited by server architectures, namely, by how much memory and/or disk space on the server is allocated to the database. From a resources point of view, this presents the issues (i) how much memory is available, and (ii) what is the speed of memory access? Since memory access is typically several orders of magnitude faster than disk access, more recent applications emphasize memory access when possible to obtain enhanced access speeds.
It remains an objective of database managers to find improved methods of storing and accessing data for use in data operations.
Systems and methods are described for performing latency-free database searches for contact data having desired attributes. Advantageously, contact data is represented by contact identifiers that are stored in distributed memory as a two-level indexed data structure having a first decreasing integer set with MSB portions of the contact identifier stored as the first level structure and a second decreasing integer set with LSB portions of the contact identifier stored as the second level structure and linked to the corresponding MSB in the first level structure.
Common data operations, such as the intersection of sets of identifiers, and the union of sets of identifiers, are performed using the indexed data structures, not the actual data. A binary interval reduction technique is used to quickly move through the decreasing integer sets looking for common elements for the intersection set, or unique elements to add to the union set.
An intersection operation is performed as part of a routine query to find data meeting specified attributes, that is, locating common elements having the specified attributes among a number of data sets. In an exemplary intersection operation, the set of contact identifiers for a first two-level indexed data structure is compared with the set of contact identifiers for a second two-level indexed data structure in order to find common integers. Instructions for performing an intersection operation between the first and second indexed data structures include performing a first series of comparison operations between the first levels of the first and second data structures using the binary interval reduction process to identify a first matching integer. If a first matching integer is found, then a second series of comparison operations is performed between the second levels of the first and second data structures that are linked to the first matching integer in the first level structures. Again, the binary interval reduction process is used to identify a second matching integer in the second levels of the first and second data structures. If a second matching integer is identified in the second levels of the first and second data structures, then the particular contact identifier having an MSB of the first matching integer and an LSB of the second matching integer is in the intersection set. The first and second matching integers are thus stored in distributed memory as an indexed data structure representing the intersection set. The comparison steps are repeated until all comparison operations are done.
A union operation is performed as part of a routine operation to add new data to an existing data set. In an exemplary union operation, the set of contact identifiers in a first two-level indexed data structure is added to the set of contact identifiers in a second two-level indexed data structure. Instructions for performing a union operation between the first and second data structures include performing a first series of comparison operations between the first level of the first data structure and the first level of the second data structure using a binary interval reduction process to identify matched and unmatched integers in the first level of the first and second data structures. If an integer in the first data structure is not found in the second data structure, then it must be added to the second data structure, and the unique set of second-level integers that is linked to this integer is retrieved and stored in the second level of an indexed data structure, which will be linked to this integer in the union set.
If a matched integer is found in the first level of the first and second data structures, then a second series of comparison operations is performed between the second level of the first data structure and the second level of the second data structure using the binary interval reduction process to identify matched and unmatched integers in the second level of the first data structures. The unmatched integers in the second level of the first data structure are stored with a link to the matched integer in the first level of the second data structure. The matched integers are ignored because they are already in the second data structure.
Any of the above embodiments may be used alone or together with one another in any combination. The one or more implementations encompassed within this specification may also include embodiments that are only partially mentioned or alluded to or are not mentioned or alluded to at all in this brief summary or in the abstract. Although various embodiments may have been motivated by various deficiencies with the prior art, which may be discussed or alluded to in one or more places in the specification, the embodiments do not necessarily address any of these deficiencies. In other words, different embodiments may address different deficiencies that may be discussed in the specification. Some embodiments may only partially address some deficiencies or just one deficiency that may be discussed in the specification, and some embodiments may not address any of these deficiencies.
In the following drawings, like reference numbers are used to refer to like elements. Although the following figures depict various examples, the one or more implementations are not limited to the examples depicted in the figures.
1. Overview
Systems and methods are described for using a distributed memory system, such as open source memcached storage technology, to facilitate storage, indexing and searching for data as part of database operations. Advantageously, the use of a distributed memory system can be horizontally scalable in relatively arbitrary lengths to provide a significant resource for database operations and management. In particular, utilizing a distributed memory system for database operations can result in greatly improved speeds for data access and query results.
In order to take better advantage of the distributed memory system, a compressed representation of data is used for permanent storage, for temporary storage during query operations; and for indexing. The use of compressed data minimizes storage requirements and leads to much faster intersection and union operations.
In one embodiment, data is compressed by storing it as a two-level tree structure, where each level of the structure takes up only half the space required for the full integer. Each level of the data structure is an ordered set of decreasing integers, with the first or top level storing a unique set of the Most Significant Bits (MSBs) for the data items, and the second level storing a unique set of the Least Significant Bits (LSBs) for the data items and providing a link to the corresponding first level MSB.
Advantageously, a first set of query operations are performed only on the top level MSB items initially, and then a second set of query operations are performed only on the second level items that are linked to the top level items resulting from the first set of query operations.
2. Hardware/Software Environment
A database is a well known component of computer-based systems providing structured storage for electronic data records. The database is accessed by users through computer-implemented devices in a computing environment. The database is configured to allow storing, indexing, searching and retrieving of a large number of data records, as well as security and backup for the system. The database is typically hosted on a single server, and management of the database is handled by a software utility (usually called DBMS) running on the database server and programmed in accord with application needs. Although it is typical for multiple databases to be hosted on a single server, database resources are necessarily limited by physical server capacity, and additional server capacity may sometimes be required for operations involving large data sets.
In one embodiment, illustrated in
User devices 12 may be any computing device, such as a desktop computer or a digital cellular telephone, and network 14 may be any type of computing network, such as the Internet, as described in more detail below.
The operation of MTS 16 is controlled by a computer-implemented processor system 17 resident on server 16a, and network interface 15 manages inbound and outbound communications with the network 14 from the MTS. One or more applications 19 are managed and operated by the MTS through application platform 18. For example, a database management application as described herein runs on application platform 18.
MTS 16 provides the users of user systems 12 with managed access to many features and applications, including tenant data storage 22, which is configured through the MTS to maintain tenant data for multiple users/tenants. Tenant data storage 22 may be physically incorporated within MTS 16, or may alternatively be configured as remote storage 22a on server 23 (shown in dashed lines), likewise accessible and useful to the MTS to support user systems 12. There are many types of tenant data that may be maintained in tenant data storage 22, including contact data, i.e., information about an individual, including name, company, address, telephone number, etc.
A distributed memory system 28 is coupled to the MTS server 16a. The distributed memory 28 is comprised of a plurality of memcached storage 30a . . . 30n, and corresponding memcached storage servers 29a . . . 29n. The distributed memory 28 is used to store indexed data structures in a compressed format, and such storage may be permanent and/or temporary. Also, the distributed memory 28 is used for performing database operations as directed by the database manager program, as described in further detail below.
In an alternative embodiment, shown in
3. Memcached Distributed Memory
Memcached storage is a general purpose distributed memory caching system that is an open source tool, and is horizontally scalable to rather arbitrary lengths. In short, a number of memcached server instances listen on user-defined ports to access spare memory on one or more machines. All the pieces of spare memory thus form a giant hash table that may be distributed across multiple machines. See Fitzpatrick, Distributed Caching with Memcached, 124 Linux Journal, August 2004 (linuxjournal.com/article/7451). The latest memcached software release v 1.4.5 is available on the Internet at memcached.org.
Memcached storage provides an attractive alternative to traditional client/server architectures by providing a relatively arbitrary allocation of memory resources to applications, and managing those memory resources in a manner that is invisible to the client. The memory resources available to a memcached storage system may be spread across multiple servers.
4. Compressed Representation and Storage of Sets
In conventional database storage, the data is simply written out in its entirety to a linked list or an array, e.g., in 32-bit chunks. For example, in a Lucene database, the complete data value is stored literally or in an inverted mode. However, some data sets can be quite large, and the resultant impact on both storage requirements and query performance can be significant. Thus, in order to avoid dealing with entire data strings, in one embodiment, the data sets are represented by an identifier stored as a compressed data structure and the compressed representation is given a convenient name, such as INDEX_ID.
In one embodiment, data sets are represented as decreasing integer sets whose elements are k-bit integers (e.g., k=32). One example of a decreasing integer set is (35, 24, 18, 11, 5, 2), i.e., a simple listing of the integers in decreasing order. This is only a small sample set for illustration purposes, but of course there could be millions of such data items, such as listings of contacts and/or customers and/or other information, stored in the database, with access provided and possibly indexed to millions of users. Further, although integer values are shown for the convenience of discussion, the actual values stored and operated with are of course binary, and so storage requirements, both temporary and permanent, may become quite large, and operations using such large items can be cumbersome and therefore should be avoided and/or minimized. By choosing a smaller representation of the data, data operations can be effectively constrained and become more efficient. By storing and operating with these smaller representations in a distributed memory using the memcached model, significant benefits in access time and query response time result.
In one embodiment illustrated in
Each list 52, 54, 55 of decreasing integers is a list of distinct numbers, i.e., there are no duplicates. The MSBs in list 52 are used as the index values to access the data, whether for retrieval or data operations. Advantageously, the second level values are only retrieved when necessary, and the first level index values are initially used in any data operation, as will be discussed in more detail below.
Although a 32-bit example with 16-bit MSB and 16-bit LSB is described, other configurations and divisions could of course be implemented, and the description herein is intended to be illustrative only. Using this example, however, the number of items that can be represented by 16 bits is 216-1 or 65,535 items, and therefore the maximum storage requirement for any index data structure is limited to 65,535 unique index values. In contrast, 32 bits can represent over 4 billion items (232-1), and handling 32-bit data presents tremendous difficulty for storage and operations, resulting in unacceptable latency times for both access and query response. By representing the data in a compressed manner, requirements for data handling and storage are constrained to easily manageable sizes, and readily available open source memcached software can handle such data sets easily, giving query results on the order of a few milliseconds.
In the example of
Although the logical architecture is limited to 16 bits, the MSBs in the first level lists can be packed two to a 32 bit storage space, and likewise, the LSBs in the second level lists can be packed two to a 32 bit storage space.
One advantage of choosing decreasing integers is that the most recent data, whether it is customer IDs or contact IDs, is typically assigned the highest index number in a database management scheme, so by choosing the MSB as the index value, the most recent entries will reside toward the front of the decreasing integer list, and thus will get retrieved first.
Referring now to
Numerous useful data sets can be defined and stored in memcached distributed storage. For example, since the database will routinely keep track of updates, such as which contacts have been updated, and when they were updated, another useful data set might include one listing all contacts updated within some time period (such as the last month, week, day or hour). Other useful data sets can be predefined or formed through a query, such as all contacts having “Vice-President” and “engineering” in the title, or all contacts whose company location (state) is “California,” etc. Access to the data sets in memcached distributed storage, whether for retrieval or use in data operations, is very fast, on the order of milliseconds as part of the database,
5. Searches: Intersections of Data Sets
A routine query may be expressed as an intersection of data sets, that is, the common elements among the sets, and advantageously, all the data sets are stored in memcached storage as an indexed data structure having the two-level tree representation as described above. For example, customer C1 may want to know which of his contacts have been updated within the last week. This query may be expressed as a simple intersection of all the contacts owned by customer C1 with the contacts that have been updated within the last week.
Such a query may not be insignificant. For example, if customer C1 owns three million contacts, and one hundred thousand have changed in the last week, then using a conventional database intersection technique, each data set would be loaded completely into memory, and then some sort of intersection logic would be applied to these data items. Three million contacts times 32 bits is a lot of data, and just the loading operation requires a tremendous amount of memory, and data operations take some time to complete. For example, a routine index update on a Lucene managed database may take 8-12 hours to complete, and thus it is also routine to expect such tasks to be accomplished overnight rather than in real time. However, through the use of a binary search technique on the indexed data structures stored in memcached storage as described above, query results including index updates may be obtained virtually in real time.
It is obvious from a visual inspection of
Advantageously, query operations are initially performed only at top level of the two level tree structure. Thus, only the first level data sets will initially be retrieved and stored into memory, and second level sets will be retrieved only when needed.
In step 601, the two top level sets 52 and 62 are retrieved from memcached storage and placed in local memory. In step 602, the pointers A and B as well as indices for pointers A and B are initialized by having them point to the highest value in sets 52 and 62 respectively (i.e., the left-most value in each decreasing integer set). This position is shown in
In step 603, the value at pointer A in set 52 (“18”) is compared to the value at pointer B in set 62 (“16”), and if the A value is less than or equal to the B value, then the A value might be in the intersection. However, that is not true at this point in the example, and that means that the A value is greater than the B value. Further, since the sets are stored as a decreasing integers sets, the A value cannot be in the intersection, and we discard that value and move on. In step 604, pointer A is updated and moved to a new value in set 52 (discussed in more detail below), and an index for pointer A is incremented. In step 605, the pointer A index is compared to a maximum, and if the maximum is exceeded, then this part of the search is done. If the index maximum is not exceeded then the process returns to step 603 for a new comparison.
In a conventional search and compare approach, pointer A might be moved one value to the right of its current value for the next round of comparisons, then one value to the right again, seriatim through the data set. In one embodiment, however, a binary interval reduction technique is used to quickly reduce the search interval in the data set. The objective of the binary search technique for this part of the intersection analysis is to find the largest value in the first data set 52 that is less than or equal to the value at pointer B in set 62. The technique involves moving the pointer back and forth within the set, while reducing the interval to be search, starting from the highest value in the set, then jumping to the lowest value in the set, and then jumping to other integer values within the set that are located halfway into the remaining interval to be searched.
Thus, after the first search and compare, shown in
In accord with the binary search technique, pointer A is now moved back to the left, roughly halfway to the prior position of this pointer on the left, pointing to the value “5” as shown in
Returning to step 603, the A value is again compared to the B value, and once again the condition is true since “5” is less than or equal to “16.” In step 606, the A value is compared to the B value for equality, and again, the values are not equal. In step 607, the last jump was the left, and so the binary technique will move the pointer further to the left, and we have obtained the largest value in set 52 that is less than or equal to the B value.
If an equality is found in step 606, then the MSB values match, and that match will be saved to temporary storage in step 608 as a possible intersection value. In step 609, a bottom level process is initiated to compare data sets 56 and 64.
When step 607 returns a true condition, i.e., the last jump was to the left, then the current A value is a candidate for the intersection, and we initiate a binary search and comparison process of the second top level set 62 to look for a match for the current A value in step 610. The process is similar to that which was performed on the first set, and is illustrated in
In step 611, the A value “5” is compared to the B value “3.” Since the A value is not less than or equal to the B value, the B value may or may not be in the intersection, but cannot be excluded for now. That determination is left for later, and this B value is ignored for now. In step 612, pointer is moved and it index is incremented. According to the binary search technique, pointer B is now moved to the left halfway toward the previous position of the pointer in that direction. This is shown in
Returning to step 611, the A value “5” is compared to the B value “5,” and the condition is true since it is less than or equal. If equality is not found in step 614, then check if the last jump was to the left in step 615. If so, then there are no matches, the intersection is the null set, and the process ends. If the last jump was to the right in step 615, then the process returns to step 612 to move pointer B and update its index.
However, if equality is found in step 614, as here, then there is a match of top level MSBs and the possibility of finding a common LSB value for this matched MSB value. As before, a possible match (e.g., the MSB value “5”) is saved to temporary storage in step 608, and a jump to a bottom level process in step 609 will be made to try and find one or more common LSB values for this MSB value.
In another example, suppose that the second set 62 of
The process for evaluating the bottom level LSBs in sets 56 and 64 is identical to the top level process shown in
When the second level compare operation is completed, the process returns to the first level and looks for more matches using the processes shown in
Advantageously, when more than two data sets must be intersected, the two sets having the fewest data elements are intersected first, and this result is then intersected with the next set having the fewest data elements, etc. This is a logical approach to further simplifying computational needs.
6. Searches: Unions of Data Sets
Another common data operation is a union of data sets. Most commonly, a union operation is used to add new data to an existing set of data, for example, when customer C1 purchases a batch of new contacts, they must be added to the existing set of contacts. This scenario is depicted in
New list T1 is built as a two layer tree representation as described above and stored in local memory as the user prepares the list T1. New list T1 includes the MSB set 52, and each MSB in set 52 is linked to at least one LSB set, such as LSB set 56 which is linked to MSB value “5.” Existing list S1 resides in memcached storage, and includes MSB set 62, and each MSB in set 52 is linked to at least one LSB set, such as LSB set 56 which is linked to MSB value “5”. A union operation is performed between new list T1 and existing list S1 and the result causes updates in the memcached storage.
The technique for performing the union operation is similar in principle and structure to the intersection operation, and relies on the binary search technique to achieve fast results. The process is shown in
In step 901, the pointers A and B as well as indices for pointers A and B are initialized by having them point to the highest value in sets 52 and 62 respectively (i.e., the left-most value in each decreasing integer set). This position is shown in
Thus, in accord with the binary search technique, pointer A is moved from the highest value to the lowest value “1” in set 52, as shown in
Pointer A is now moved back to the left, halfway toward the previous pointer location on the left, to the value “5” as shown in
If there was an equality in step 907, then the MSB value is already in the existing set S1, but there may be additional LSB values in new set T1 that need to be added to set S1. Thus, step 915 jumps to a bottom level process to determine the union set for LSBs of the MSB value “5.” In the bottom level process, sets 56 and 64 are evaluated by the same binary search technique to find the LSB values in set 56 that are not already in set 64. Those values are then formed as a resultant union set and loaded into memcache to replace set 64.
Referring now to
According to the binary search technique, pointer B is now moved to the left, halfway toward the previous position on the left, pointing to the value “5” as shown in
If in step 914 the values were not equal, then last jump left is checked in step 913, then the process returns to step 911 to move the pointer and make more comparisons.
7. Powerful Parallel Processing
One additional benefit of the use of the data structure with memcached storage is extraordinary parallel processing capability. Consider the scenario where thousands of subscribers are making queries to the database simultaneously. Because of the distributed nature of memcached storage, there is generally little overlap in such queries, and results can be obtained very quickly.
8. Detailed System Overview
Environment 110 is an environment in which an on-demand database service 116 exists. User system 112 may be any machine or system that is used by a user to access a database user system. For example, any of user systems 112 can be a handheld computing device, a mobile phone, a laptop computer, a work station, and/or a network of computing devices. As illustrated in
An on-demand database service, such as system 116, is a database system that is made available to outside users that are not necessarily concerned with building and/or maintaining the database system, but instead, only that the database system be available for their use when needed (e.g., on the demand of the users). Some on-demand database services may store information from one or more tenants into tables of a common database image to form a multi-tenant database system (MTS). Accordingly, “on-demand database service 116” and “system 116” will be used interchangeably herein. A database image may include one or more database objects. A relational database management system (RDMS) or the equivalent may execute storage and retrieval of information against the database object(s). Application platform 118 may be a framework that allows the applications of system 116 to run, such as the hardware and/or software, e.g., the operating system. In an embodiment, on-demand database service 116 may include an application platform 118 that enables creation, managing and executing one or more applications developed by the provider of the on-demand database service, users accessing the on-demand database service via user systems 112, or third party application developers accessing the on-demand database service via user systems 112.
The users of user systems 112 may differ in their respective capacities, and the capacity of a particular user system 112 might be entirely determined by permissions (permission levels) for the current user. For example, where a salesperson is using a particular user system 112 to interact with system 116, that user system has the capacities allotted to that salesperson. However, while an administrator is using that user system to interact with system 116, that user system has the capacities allotted to that administrator. In systems with a hierarchical role model, users at one permission level may have access to applications, data, and database information accessible by a lower permission level user, but may not have access to certain applications, database information, and data accessible by a user at a higher permission level. Thus, different users will have different capabilities with regard to accessing and modifying application and database information, depending on a user's security or permission level.
Network 114 is any network or combination of networks of devices that communicate with one another. For example, network 114 can be any one or any combination of a LAN (local area network), WAN (wide area network), telephone network, wireless network, point-to-point network, star network, token ring network, hub network, or other appropriate configuration. As the most common type of computer network in current use is a TCP/IP (Transfer Control Protocol and Internet Protocol) network, such as the global internetwork of networks often referred to as the “Internet” with a capital “I,” that network will be used in many of the examples herein. However, it should be understood that the networks that the one or more implementations might use are not so limited, although TCP/IP is a frequently implemented protocol.
User systems 112 might communicate with system 116 using TCP/IP and, at a higher network level, use other common Internet protocols to communicate, such as HTTP, FTP, AFS, WAP, etc. In an example where HTTP is used, user system 112 might include an HTTP client commonly referred to as a “browser” for sending and receiving HTTP messages to and from an HTTP server at system 116. Such an HTTP server might be implemented as the sole network interface between system 116 and network 114, but other techniques might be used as well or instead. In some implementations, the interface between system 116 and network 114 includes load sharing functionality, such as round-robin HTTP request distributors to balance loads and distribute incoming HTTP requests evenly over a plurality of servers. At least as for the users that are accessing that server, each of the plurality of servers has access to the MTS' data; however, other alternative configurations may be used instead.
In one embodiment, system 116 implements a web-based customer relationship management (CRM) system. For example, in one embodiment, system 116 includes application servers configured to implement and execute CRM software applications as well as provide related data, code, forms, web pages and other information to and from user systems 112 and to store to, and retrieve from, a database system related data, objects, and Webpage content. With a multi-tenant system, data for multiple tenants may be stored in the same physical database object, however, tenant data typically is arranged so that data of one tenant is kept logically separate from that of other tenants so that one tenant does not have access to another tenant's data, unless such data is expressly shared. In certain embodiments, system 116 implements applications other than, or in addition to, a CRM application. For example, system 116 may provide tenant access to multiple hosted (standard and custom) applications, including a CRM application. User (or third party developer) applications, which may or may not include CRM, may be supported by the application platform 118, which manages creation, storage of the applications into one or more database objects and executing of the applications in a virtual machine in the process space of the system 116.
One arrangement for elements of system 116 is shown in
Several elements in the system shown in
According to one embodiment, each user system 112 and all of its components are operator configurable using applications, such as a browser, including computer code run using a central processing unit such as an Intel Pentium® processor or the like. Similarly, system 116 (and additional instances of an MTS, where more than one is present) and all of their components might be operator configurable using application(s) including computer code to run using a central processing unit such as processor system 117, which may include an Intel Pentium® processor or the like, and/or multiple processor units. A computer program product embodiment includes a machine-readable storage medium (media) having instructions stored thereon/in which can be used to program a computer to perform any of the processes of the embodiments described herein. Computer code for operating and configuring system 116 to intercommunicate and to process webpages, applications and other data and media content as described herein are preferably downloaded and stored on a hard disk, but the entire program code, or portions thereof, may also be stored in any other volatile or non-volatile memory medium or device as is well known, such as a ROM or RAM, or provided on any media capable of storing program code, such as any type of rotating media including floppy disks, optical discs, digital versatile disk (DVD), compact disk (CD), microdrive, and magneto-optical disks, and magnetic or optical cards, nanosystems (including molecular memory ICs), or any type of media or device suitable for storing instructions and/or data. Additionally, the entire program code, or portions thereof, may be transmitted and downloaded from a software source over a transmission medium, e.g., over the Internet, or from another server, as is well known, or transmitted over any other conventional network connection as is well known (e.g., extranet, VPN, LAN, etc.) using any communication medium and protocols (e.g., TCP/IP, HTTP, HTTPS, Ethernet, etc.) as are well known. It will also be appreciated that computer code for implementing embodiments can be implemented in any programming language that can be executed on a client system and/or server or server system such as, for example, C, C++, HTML, any other markup language, Java™, JavaScript, ActiveX, any other scripting language, such as VBScript, and many other programming languages as are well known may be used. (Java™ is a trademark of Sun Microsystems, Inc.).
According to one embodiment, each system 116 is configured to provide web pages, forms, applications, data and media content to user (client) systems 112 to support the access by user systems 112 as tenants of system 116. As such, system 116 provides security mechanisms to keep each tenant's data separate unless the data is shared. If more than one MTS is used, they may be located in close proximity to one another (e.g., in a server farm located in a single building or campus), or they may be distributed at locations remote from one another (e.g., one or more servers located in city A and one or more servers located in city B). As used herein, each MTS could include one or more logically and/or physically connected servers distributed locally or across one or more geographic locations. Additionally, the term “server” is meant to include a computer system, including processing hardware and process space(s), and an associated storage system and database application (e.g., OODBMS or RDBMS) as is well known in the art. It should also be understood that “server system” and “server” are often used interchangeably herein. Similarly, the database object described herein can be implemented as single databases, a distributed database, a collection of distributed databases, a database with redundant online or offline backups or other redundancies, etc., and might include a distributed database or storage network and associated processing intelligence.
User system 112, network 114, system 116, tenant data storage 122, and system data storage 124 were discussed above in
Application platform 118 includes an application setup mechanism 238 that supports application developers' creation and management of applications, which may be saved as metadata into tenant data storage 122 by save routines 236 for execution by subscribers as one or more tenant process spaces 204 managed by tenant management process 210 for example. Invocations to such applications may be coded using PL/SOQL 234 that provides a programming language style interface extension to API 232. A detailed description of some PL/SOQL language embodiments is discussed in commonly owned co-pending U.S. Provisional Patent Application 60/828,192, entitled Programming Language Method And System For Extending APIs To Execute In Conjunction With Database APIs, filed Oct. 4, 2006, which is incorporated in its entirety herein for all purposes. Invocations to applications may be detected by one or more system processes, which manage retrieving application metadata 216 for the subscriber making the invocation and executing the metadata as an application in a virtual machine.
Each application server 200 may be communicably coupled to database systems, e.g., having access to system data 125 and tenant data 123, via a different network connection. For example, one application server 2001 might be coupled via the network 114 (e.g., the Internet), another application server 200N-1 might be coupled via a direct network link, and another application server 200N might be coupled by yet a different network connection. Transfer Control Protocol and Internet Protocol (TCP/IP) are typical protocols for communicating between application servers 200 and the database system. However, it will be apparent to one skilled in the art that other transport protocols may be used to optimize the system depending on the network interconnect used.
In certain embodiments, each application server 200 is configured to handle requests for any user associated with any organization that is a tenant. Because it is desirable to be able to add and remove application servers from the server pool at any time for any reason, there is preferably no server affinity for a user and/or organization to a specific application server 200. In one embodiment, therefore, an interface system implementing a load balancing function (e.g., an F5 Big-IP load balancer) is communicably coupled between the application servers 200 and the user systems 112 to distribute requests to the application servers 200. In one embodiment, the load balancer uses a least connections algorithm to route user requests to the application servers 200. Other examples of load balancing algorithms, such as round robin and observed response time, also can be used. For example, in certain embodiments, three consecutive requests from the same user could hit three different application servers 200, and three requests from different users could hit the same application server 200. In this manner, system 116 is multi-tenant, wherein system 116 handles storage of, and access to, different objects, data and applications across disparate users and organizations.
As an example of storage, one tenant might be a company that employs a sales force where each salesperson uses system 116 to manage their sales process. Thus, a user might maintain contact data, leads data, customer follow-up data, performance data, goals and progress data, etc., all applicable to that user's personal sales process (e.g., in tenant data storage 122). In an example of a MTS arrangement, since all of the data and the applications to access, view, modify, report, transmit, calculate, etc., can be maintained and accessed by a user system having nothing more than network access, the user can manage his or her sales efforts and cycles from any of many different user systems. For example, if a salesperson is visiting a customer and the customer has Internet access in their lobby, the salesperson can obtain critical updates as to that customer while waiting for the customer to arrive in the lobby.
While each user's data might be separate from other users' data regardless of the employers of each user, some data might be organization-wide data shared or accessible by a plurality of users or all of the users for a given organization that is a tenant. Thus, there might be some data structures managed by system 116 that are allocated at the tenant level while other data structures might be managed at the user level. Because an MTS might support multiple tenants including possible competitors, the MTS should have security protocols that keep data, applications, and application use separate. Also, because many tenants may opt for access to an MTS rather than maintain their own system, redundancy, up-time, and backup are additional functions that may be implemented in the MTS. In addition to user-specific data and tenant specific data, system 116 might also maintain system level data usable by multiple tenants or other data. Such system level data might include industry reports, news, postings, and the like that are sharable among tenants.
In certain embodiments, user systems 112 (which may be client systems) communicate with application servers 200 to request and update system-level and tenant-level data from system 116 that may require sending one or more queries to tenant data storage 122 and/or system data storage 124. System 116 (e.g., an application server 200 in system 116) automatically generates one or more SQL statements (e.g., one or more SQL queries) that are designed to access the desired information. System data storage 124 may generate query plans to access the requested data from the database.
Each database can generally be viewed as a collection of objects, such as a set of logical tables, containing data fitted into predefined categories. A “table” is one representation of a data object, and may be used herein to simplify the conceptual description of objects and custom objects. It should be understood that “table” and “object” may be used interchangeably herein. Each table generally contains one or more data categories logically arranged as columns or fields in a viewable schema. Each row or record of a table contains an instance of data for each category defined by the fields. For example, a CRM database may include a table that describes a customer with fields for basic contact information such as name, address, phone number, fax number, etc. Another table might describe a purchase order, including fields for information such as customer, product, sale price, date, etc. In some multi-tenant database systems, standard entity tables might be provided for use by all tenants. For CRM database applications, such standard entities might include tables for Account, Contact, Lead, and Opportunity data, each containing pre-defined fields. It should be understood that the word “entity” may also be used interchangeably herein with “object” and “table”.
In some multi-tenant database systems, tenants may be allowed to create and store custom objects, or they may be allowed to customize standard entities or objects, for example by creating custom fields for standard objects, including custom index fields. U.S. Pat. No. 7,779,039, entitled “Custom Entities and Fields in a Multi-Tenant Database System,” and which is hereby incorporated herein by reference, teaches systems and methods for creating custom objects as well as customizing standard objects in a multi-tenant database system. In certain embodiments, for example, all custom entity data rows are stored in a single multi-tenant physical table, which may contain multiple logical tables per organization. It is transparent to customers that their multiple “tables” are in fact stored in one large table or that their data may be stored in the same table as the data of other customers.
While one or more implementations have been described by way of example and in terms of the specific embodiments, it is to be understood that one or more implementations are not limited to the disclosed embodiments. To the contrary, it is intended to cover various modifications and similar arrangements as would be apparent to those skilled in the art. Therefore, the scope of the appended claims should be accorded the broadest interpretation so as to encompass all such modifications and similar arrangements.
This application claims the benefit of U.S. Provisional Patent Application 61/391,486 entitled A HIGHLY EFFICIENT AND SCALABLE SYSTEM FOR LATENCY-FREE MY CONTACTS SEARCH AND FOR INTERSECTION QUERIES, by Arun Kumar Jagota et al., filed Oct. 8, 2010, the entire contents of which are incorporated herein by reference.
| Number | Name | Date | Kind |
|---|---|---|---|
| 5551024 | Waters | Aug 1996 | A |
| 5577188 | Zhu | Nov 1996 | A |
| 5608872 | Schwartz | Mar 1997 | A |
| 5649104 | Carleton | Jul 1997 | A |
| 5715450 | Ambrose et al. | Feb 1998 | A |
| 5757900 | Nagel et al. | May 1998 | A |
| 5761419 | Schwartz | Jun 1998 | A |
| 5819038 | Carleton | Oct 1998 | A |
| 5821937 | Tonelli et al. | Oct 1998 | A |
| 5831610 | Tonelli et al. | Nov 1998 | A |
| 5852822 | Srinivasan et al. | Dec 1998 | A |
| 5873096 | Lim et al. | Feb 1999 | A |
| 5878431 | Potterveld et al. | Mar 1999 | A |
| 5918159 | Fomukong et al. | Jun 1999 | A |
| 5963953 | Cram et al. | Oct 1999 | A |
| 6092083 | Brodersen et al. | Jul 2000 | A |
| 6169534 | Raffel et al. | Jan 2001 | B1 |
| 6178425 | Brodersen et al. | Jan 2001 | B1 |
| 6189011 | Lim et al. | Feb 2001 | B1 |
| 6216135 | Brodersen et al. | Apr 2001 | B1 |
| 6233617 | Rothwein et al. | May 2001 | B1 |
| 6266669 | Brodersen et al. | Jul 2001 | B1 |
| 6295530 | Ritchie et al. | Sep 2001 | B1 |
| 6324568 | Diec | Nov 2001 | B1 |
| 6324693 | Brodersen et al. | Nov 2001 | B1 |
| 6336137 | Lee et al. | Jan 2002 | B1 |
| D454139 | Feldcamp et al. | Mar 2002 | S |
| 6367077 | Brodersen et al. | Apr 2002 | B1 |
| 6393605 | Loomans | May 2002 | B1 |
| 6405220 | Brodersen et al. | Jun 2002 | B1 |
| 6434550 | Warner et al. | Aug 2002 | B1 |
| 6446089 | Brodersen et al. | Sep 2002 | B1 |
| 6535909 | Rust | Mar 2003 | B1 |
| 6549908 | Loomans | Apr 2003 | B1 |
| 6553563 | Ambrose et al. | Apr 2003 | B2 |
| 6560461 | Fomukong et al. | May 2003 | B1 |
| 6574635 | Stauber et al. | Jun 2003 | B2 |
| 6577726 | Huang et al. | Jun 2003 | B1 |
| 6601087 | Zhu | Jul 2003 | B1 |
| 6604117 | Lim et al. | Aug 2003 | B2 |
| 6604128 | Diec | Aug 2003 | B2 |
| 6609150 | Lee et al. | Aug 2003 | B2 |
| 6621834 | Scherpbier | Sep 2003 | B1 |
| 6654032 | Zhu | Nov 2003 | B1 |
| 6665648 | Brodersen et al. | Dec 2003 | B2 |
| 6665655 | Warner et al. | Dec 2003 | B1 |
| 6684438 | Brodersen et al. | Feb 2004 | B2 |
| 6711565 | Subramaniam et al. | Mar 2004 | B1 |
| 6724399 | Katchour et al. | Apr 2004 | B1 |
| 6728702 | Subramaniam et al. | Apr 2004 | B1 |
| 6728960 | Loomans et al. | Apr 2004 | B1 |
| 6732095 | Warshavsky et al. | May 2004 | B1 |
| 6732100 | Brodersen et al. | May 2004 | B1 |
| 6732111 | Brodersen et al. | May 2004 | B2 |
| 6754681 | Brodersen et al. | Jun 2004 | B2 |
| 6763351 | Subramaniam et al. | Jul 2004 | B1 |
| 6763501 | Zhu | Jul 2004 | B1 |
| 6768904 | Kim | Jul 2004 | B2 |
| 6782383 | Subramaniam et al. | Aug 2004 | B2 |
| 6804330 | Jones et al. | Oct 2004 | B1 |
| 6826565 | Ritchie et al. | Nov 2004 | B2 |
| 6826582 | Chatterjee et al. | Nov 2004 | B1 |
| 6826745 | Coker et al. | Nov 2004 | B2 |
| 6829655 | Huang et al. | Dec 2004 | B1 |
| 6842748 | Warner et al. | Jan 2005 | B1 |
| 6850895 | Brodersen et al. | Feb 2005 | B2 |
| 6850949 | Warner et al. | Feb 2005 | B2 |
| 7062502 | Kesler | Jun 2006 | B1 |
| 7278008 | Case et al. | Oct 2007 | B1 |
| 7340411 | Cook | Mar 2008 | B2 |
| 7401094 | Kesler | Jul 2008 | B1 |
| 7620655 | Larsson | Nov 2009 | B2 |
| 7698160 | Beaven et al. | Apr 2010 | B2 |
| 8010663 | Firminger et al. | Aug 2011 | B2 |
| 8082301 | Ahlgren et al. | Dec 2011 | B2 |
| 8095413 | Beaven et al. | Jan 2012 | B1 |
| 8095594 | Beaven et al. | Jan 2012 | B2 |
| 8275836 | Beaven et al. | Sep 2012 | B2 |
| 20010044791 | Richter et al. | Nov 2001 | A1 |
| 20020072951 | Lee et al. | Jun 2002 | A1 |
| 20020082892 | Raffel | Jun 2002 | A1 |
| 20020129352 | Brodersen et al. | Sep 2002 | A1 |
| 20020140731 | Subramanian et al. | Oct 2002 | A1 |
| 20020143997 | Huang et al. | Oct 2002 | A1 |
| 20020162090 | Parnell et al. | Oct 2002 | A1 |
| 20020165742 | Robbins | Nov 2002 | A1 |
| 20030004971 | Gong | Jan 2003 | A1 |
| 20030018705 | Chen et al. | Jan 2003 | A1 |
| 20030018830 | Chen et al. | Jan 2003 | A1 |
| 20030066031 | Laane et al. | Apr 2003 | A1 |
| 20030066032 | Ramachandran et al. | Apr 2003 | A1 |
| 20030069936 | Warner et al. | Apr 2003 | A1 |
| 20030070000 | Coker et al. | Apr 2003 | A1 |
| 20030070004 | Mukundan et al. | Apr 2003 | A1 |
| 20030070005 | Mukundan et al. | Apr 2003 | A1 |
| 20030074418 | Coker | Apr 2003 | A1 |
| 20030120675 | Stauber et al. | Jun 2003 | A1 |
| 20030151633 | George et al. | Aug 2003 | A1 |
| 20030159136 | Huang et al. | Aug 2003 | A1 |
| 20030187921 | Diec et al. | Oct 2003 | A1 |
| 20030189600 | Gune et al. | Oct 2003 | A1 |
| 20030204427 | Gune et al. | Oct 2003 | A1 |
| 20030206192 | Chen et al. | Nov 2003 | A1 |
| 20040001092 | Rothwein et al. | Jan 2004 | A1 |
| 20040015981 | Coker et al. | Jan 2004 | A1 |
| 20040027388 | Berg et al. | Feb 2004 | A1 |
| 20040128001 | Levin et al. | Jul 2004 | A1 |
| 20040186860 | Lee et al. | Sep 2004 | A1 |
| 20040193510 | Catahan et al. | Sep 2004 | A1 |
| 20040199489 | Barnes-Leon et al. | Oct 2004 | A1 |
| 20040199536 | Barnes-Leon et al. | Oct 2004 | A1 |
| 20040249854 | Barnes-Leon et al. | Dec 2004 | A1 |
| 20040260534 | Pak et al. | Dec 2004 | A1 |
| 20040260659 | Chan et al. | Dec 2004 | A1 |
| 20040268299 | Lei et al. | Dec 2004 | A1 |
| 20050050555 | Exley et al. | Mar 2005 | A1 |
| 20050091098 | Brodersen et al. | Apr 2005 | A1 |
| 20060004691 | Sifry | Jan 2006 | A1 |
| 20090063415 | Chatfield et al. | Mar 2009 | A1 |
| Number | Date | Country | |
|---|---|---|---|
| 20120089638 A1 | Apr 2012 | US |
| Number | Date | Country | |
|---|---|---|---|
| 61391486 | Oct 2010 | US |
