Customizing replacement functions with inverse functions to improve query performance

Abstract

Techniques and solutions are provided for using inverse functions to improve query performance. Generally, a function is used to perform operations such as data type conversion. The use of these functions may be resource-intensive, such as performing transformations on all rows of a particular relational database table if desired. The present disclosure allows registration of inverse functions that may use or at least consider use in place of functions. A given inverse function may be associated with its function using techniques such as maintaining mapping information or using a particular naming agreement. Specific grammar is provided for specifying and creating inverse functions.

Description

Technical Field

[0001] The present disclosure is generally related to improving query execution for queries involving functions. Certain embodiments relate to rewriting queries as part of query optimization to use an inverse function instead of a function specified in the query, where the inverse function is registered in the system, such as by a user. Background Art

[0002] Many software applications, particularly enterprise-level software applications, interact with data in data stores such as relational database systems. It is not uncommon for data to be stored in different formats. For example, a data object used by a software application may store a particular type of data in one data type, while a physical data object used by a relational database may store the particular type of data in a different data type. As a simple example, consider numerical data that may be represented as three digits by a software application, but where the data is stored in a physical database object as a five-digit number. Conversions may be required to convert between the data types used by the application and the data types used by the database. These conversion operations may be computationally expensive, including because they may need to be performed row by row for a result set containing a large number of records, such as thousands or even millions of rows. Therefore, there is room for improvement. Summary of the invention

[0003] This summary is provided to introduce some concepts in a simplified form, which are further described in the detailed description below. This summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter.

[0004] Techniques and solutions for improving query performance using inverse functions are provided. Typically, functions are used to perform operations such as data type conversions. The use of these functions can be resource intensive, such as if a conversion needs to be performed on all rows of a particular relational database table. The present disclosure allows registration of inverse functions that can be used or at least considered for use, in place of a function. A given inverse function can be associated with its function using techniques such as maintaining mapping information or using a specific naming convention. A specific syntax for specifying and creating an inverse function is provided.

[0005] In one aspect, the present disclosure provides a process for optimizing queries, which includes replacing a function with an inverse function. As will be described, in at least some cases, using the inverse function can provide improved query performance compared to using the function. Receive a function definition for a function. Receive an inverse function definition for an inverse function from a client, where the inverse function is the inverse of the function. Receive a query, where the query includes a call to the function. Determine that an inverse function is available for the function. Prepare a query execution plan that includes the inverse function in place of the function.

[0006] The present disclosure also includes a computing system and a tangible, non-transitory computer-readable storage medium configured to perform or include instructions for performing the above-described methods. As described herein, various other features and advantages may be incorporated into the technology as desired. BRIEF DESCRIPTION OF THE DRAWINGS

[0007] Figure 1 is a diagram depicting an example database system that can be used to implement aspects of the disclosed technology.

[0008] Figure 2 is a diagram depicting a computing environment showing how applications, virtual data model objects, and database objects may interact, including that data used by the applications, virtual data model objects, and database objects may be in different data formats.

[0009] Figure 3 A process is shown for rewriting a query from a client application calling a function to use an inverse function to provide improved query performance.

[0010] Figure 4 It shows how queries including functions or function calls are computationally expensive to execute.

[0011] Figure 5A and 5B instruct Figure 4 How a query function may be written to use an inverse function to provide improved query performance, including illustrating how an inverse function may be associated with a particular function.

[0012] Figure 6 is a flow chart of an example query optimization process using the disclosed techniques.

[0013] Figure 7 is a flowchart for optimizing a query, which includes replacing a function with its corresponding inverse function.

[0014] Figure 8 is a diagram of an example computing system in which some described embodiments may be implemented.

[0015] Fig. 9 is an example cloud computing environment that can be used in conjunction with the techniques described herein. DETAILED DESCRIPTION

[0016] Example 1 - Overview

[0017] Many software applications, particularly enterprise-level software applications, interact with data in data stores such as relational database systems. It is not uncommon for data to be stored in different formats. For example, a data object used by a software application may store a particular type of data in one data type, while a physical data object used by a relational database may store the particular type of data in a different data type. As a simple example, consider numerical data that may be represented as a three-digit number by a software application, but where the data is stored in a physical database object as a five-digit number. Conversions may be required to convert between the data types used by the application and the data types used by the database. These conversion operations may be computationally expensive, including because they may need to be performed row by row for a result set containing a large number of records, such as thousands or even millions of rows.

[0018] As an additional example of problems that can result when performing data type conversions on table data, the conversions can cause indexes to be unusable for database operations against the table. That is, indexes are typically built based on the values ​​stored locally in the row (or, more accurately, the row values ​​for a particular column or set of columns are used to build the index). Therefore, if the converted values ​​need to be evaluated, each row of the table may need to be converted and evaluated, rather than using the more efficient process of using an index. These problems can cause JOIN operations to execute more slowly.

[0019] Furthermore, as described above, in addition to not being able to use indexes, comparisons during JOINs may require transforming attribute values ​​for each row of a table, which increases the computational cost of what is typically already a resource-intensive process. Furthermore, query optimizers typically use factors such as the availability of indexes, joins, and table size to make cost estimates. However, transformations may affect the performance of the operation, so it may be more difficult for the query optimizer to make accurate estimates and select an efficient plan. Therefore, there is room for improvement.

[0020] As a specific example, consider a query defined to retrieve records matching a condition expressed as a three-bit value, where the database object stores that information as a five-bit value. Assume that truncating the five-bit value to a three-bit value does not change the meaning of the information, such as if the five-bit value is simply padded with leading zeros to the three-bit value.

[0021] In this case, there are at least two options for evaluating the condition. In one approach, a function can be used to convert the five-bit stored value to a three-bit value to match the format of the condition, thereby allowing the condition to be evaluated. However, this conversion may need to be performed for each row where the condition is being evaluated.

[0022] As another option, the three-bit condition value can be converted to a five-bit value, allowing the condition value to be compared directly with the row value. Thus, instead of performing a conversion operation on each row, a single conversion is performed on the condition, which is then used to evaluate all rows.

[0023] Typically, a function is defined that operates on the stored data. That is, typically the function converts the stored data into a format for the condition. This approach is often chosen because it is not known whether the inverse function, which converts the condition from a three-bit value to a five-bit value, would change the meaning of the information. In some database systems, inverse functions may be used for specific data types when there is no or very little chance that the data will be altered as part of the conversion. For example, due to the nature of Unicode encoding, a database system at SAP SE of Walldorf, Germany, may use an inverse function to convert data of a "longdate" data type to an "nvchar" data type. However, such conversions are typically "hard-coded," such as in rules applied by a query optimizer, and, as discussed, may be used for a very limited set of conversions.

[0024] The present disclosure provides innovations that allow for more general use of inverse functions. In particular, a user or client (such as a software developer) can determine whether an inverse function is available that will not change the "meaning" of the data, or at least more than an acceptable degree. For example, a developer can determine that padding "nvchar(3)" format data with leading zeros to provide data in "nvchar(5)" format will not change the meaning of the data, or that truncating a value in "nvchar(5)" format to "nvchar(3)" format will not result in data loss.

[0025] Thus, a user can define or specify a specific inverse function to be used with a given (non-inverse function). The query optimizer can recognize during query compilation that the function has a specified inverse function and can replace the function with the inverse in the query plan, or at least consider replacing the function even if the use of the inverse function is ultimately rejected / not chosen.

[0026] The disclosed techniques can greatly improve query execution by using inverse functions that are easier to process. The use of inverse functions can allow the use of existing indexes, can speed up JOIN and select operations by allowing direct value comparisons, and can facilitate the operation of the query optimizer by allowing more accurate cost estimates to be made, and provide the query optimizer with greater flexibility in rewriting queries or otherwise optimizing query operations. In addition, application developers are able to more flexibly accept inputs in different formats, such as when a particular type of input "makes more sense" from an end user's perspective, and still obtain good query performance. The use of inverse functions can also promote interoperability of computational processes, as it may be desirable to use a common format for the values ​​that will be evaluated by the function, but instead use the inverse function for the actual query execution.

[0027] Example 2 describes a general relational database environment in which the disclosed technology can be implemented. Example 3 describes how a software application can access data from a data source using an intermediate software layer, such as a layer including a virtual data model that is mapped to one or more physical data models of one or more data sources that hold related data.

[0028] Example 4 describes how function calls issued by a client application can be analyzed by a query optimizer and, at least in some cases, replaced with inverse functions. Example 5 provides specific examples of functions and associated inverse functions, and example commands that can be used to create inverse functions that can be recognized and accessed by a query optimizer. Example 6 describes an example query optimization process using the disclosed technology.

[0029] Example 2 - Sample database schema

[0030] Figure 1 An example database environment 100 is shown. Database environment 100 may include client 104. Although a single client 104 is shown, client 104 may represent multiple clients. One or more clients 104 may be OLAP clients, OLTP clients, or a combination thereof.

[0031] The client 104 communicates with the database server 106. Through various subcomponents, the database server 106 can process requests for database operations, such as requests to store, read, or manipulate data (i.e., CRUD operations). The session manager component 108 can be responsible for managing connections between the client 104 and the database server 106, such as clients that communicate with the database server using a database programming interface (such as Java Database Connectivity (JDBC), Open Database Connectivity (ODBC), or Database Shared Library (DBSL)). Typically, the session manager 108 can manage connections with multiple clients 104 at the same time. The session manager 108 can perform functions such as creating new sessions for client requests, assigning client requests to existing sessions, and authenticating access to the database server 106. For each session, the session manager 108 can maintain a context that stores a set of parameters related to the session, such as settings related to committing database transactions or transaction isolation levels (such as statement-level isolation or transaction-level isolation).

[0032] For other types of clients 104, such as web-based clients (such as clients using the HTTP protocol or similar transport protocol), the client can interface with the application manager component 110. Although shown as a component of the database server 106, in other implementations, the application manager 110 can be external to the database server 106, but in communication with the database server 106. The application manager 110 can initiate new database sessions with the database server 106 in a manner similar to the session manager 108 and perform other functions.

[0033] The application manager 110 may determine the type of application requesting the database operation and mediate execution of the request at the database server 106, such as by invoking or executing a procedure call, generating a query language statement, or converting data between formats usable by the client 104 and the database server 106. In a particular example, the application manager 110 receives a request for a database operation from the client 104, but does not store information related to the request, such as state information.

[0034] Once a connection is established between client 104 and database server 106, including when established through application manager 110, client requests are typically executed using a query language, such as structured query language (SQL). In executing the request, session manager 108 and application manager 110 may communicate with query interface 112. Query interface 112 may be responsible for creating a connection with the appropriate execution component of database server 106. Query interface 112 may also be responsible for determining whether the request is associated with a previously cached statement or stored procedure, and calling a stored procedure or associating a previously cached statement with the request.

[0035] At least some types of requests for database operations (such as statements that write data or manipulate data in a query language) can be associated with a transaction context. In at least some implementations, each new session can be assigned to a transaction. Transactions can be managed by a transaction manager component 114. The transaction manager component 114 can be responsible for operations such as coordinating transactions, managing transaction isolation, tracking running and closed transactions, and managing the submission or rollback of transactions. In performing these operations, the transaction manager 114 can communicate with other components of the database server 106.

[0036] The query interface 112 may communicate with a query language processor 116, such as a structured query language processor. For example, the query interface 112 may forward query language statements or other database operation requests from the client 104 to the query language processor 116. The query language processor 116 may include a query language executor 120, such as a SQL executor, which may include a thread pool 124. Some requests for database operations or components thereof may be executed directly by the query language processor 116. Other requests or components thereof may be forwarded by the query language processor 116 to another component of the database server 106. For example, transaction control statements (such as commit or rollback operations) may be forwarded by the query language processor 116 to the transaction manager 114. In at least some cases, the query language processor 116 is responsible for executing operations (e.g., select, update, delete) that retrieve or manipulate data. Other types of operations (such as queries) may be sent by the query language processor 116 to other components of the database server 106. The query interface 112 and the session manager 108 may maintain and manage context information associated with requests for database operations. In particular embodiments, query interface 112 may maintain and manage contextual information for requests received via application manager 110 .

[0037] When the session manager 108 or the application manager 110 establishes a connection between the client 104 and the database server 106, a client request such as a query can be assigned to a thread of the thread pool 124, such as using the query interface 112. In at least one embodiment, a thread is associated with a context for performing processing activities. The thread can be managed by the operating system of the database server 106, or by another component of the database server, or in combination with another component of the database server. Typically, at any point, the thread pool 124 contains multiple threads. In at least some cases, such as in response to the activity level at the database server 106, the number of threads in the thread pool 124 can be dynamically adjusted. In certain aspects, each thread of the thread pool 124 can be assigned to multiple different sessions.

[0038] When a query is received, the session manager 108 or application manager 110 may determine whether an execution plan for the query already exists, such as in the plan cache 136. If a query execution plan exists, the cached execution plan may be retrieved and forwarded to the query language executor 120, such as using the query interface 112. For example, the query may be sent to an execution thread of a thread pool 124 determined by the session manager 108 or application manager 110. In a particular example, the query plan is implemented as an abstract data type.

[0039] If the query is not associated with an existing execution plan, the query can be parsed using the query language parser 128. The query language parser 128 can, for example, check the query language statements of the query to ensure that they have correct syntax and confirm that the statements are otherwise valid. For example, the query language parser 128 can check to determine whether the tables and records specified in the query language statements are defined in the database server 106.

[0040] The query may also be optimized using a query language optimizer 132. The query language optimizer 132 may manipulate elements of a query language statement to allow for more efficient processing of the query. For example, the query language optimizer 132 may perform operations such as unnesting a query or determining an optimized execution order for various operations in a query (such as operations within a statement). After optimization, an execution plan may be generated or compiled for the query. In at least some cases, the execution plan may be cached, such as in a plan cache 136, and the execution plan may be retrieved (such as by a session manager 108 or an application manager 110) if the query is received again.

[0041] For purposes of this disclosure, one task that can be performed by the query language optimizer 132 is to determine whether a function used in a query is associated with an inverse function. If so, the query language optimizer 132 can rewrite the query to use the inverse function, optionally after determining that the inverse function results in a lower overall execution cost.

[0042] Once a query execution plan is generated or received, the query language executor 120 can oversee the execution of the query execution plan. For example, the query language executor 120 can call the appropriate subcomponents of the database server 106.

[0043] When executing a query, the query language executor 120 can call a query processor 140, which can include one or more query processing engines. The query processing engine can include, for example, an OLAP engine 142, a join engine 144, an attribute engine 146, or a calculation engine 148. The OLAP engine 142 can, for example, apply rules to create an optimized execution plan for an OLAP query. The join engine 144 can be used to implement relational operators, typically for non-OLAP queries, such as join and aggregation operations. In a particular embodiment, the attribute engine 146 can implement column data structures and access operations. For example, the attribute engine 146 can implement merge functions and query processing functions, such as scan columns.

[0044] In some cases, such as if a query involves complex or internally parallelized operations or sub-operations, the query executor 120 may send the operations or sub-operations of the query to a job executor component 154, which may include a thread pool 156. The execution plan for the query may include multiple plan operators. In particular embodiments, each job execution thread of the job execution thread pool 156 may be assigned to a separate plan operator. The job executor component 154 may be used to execute at least a portion of the operators of the query in parallel. In some cases, the plan operators may be further divided and parallelized, such as having the operations access different portions of the same table simultaneously. Using the job executor component 154 may increase the load on one or more processing units of the database server 106, but may improve the execution time of the query.

[0045] The query processing engine of the query processor 140 can access data stored in the database server 106. The data can be stored in a row store 162 in a row-by-row format or in a column store 164 in a column-by-column format. In at least some cases, the data can be transformed between the row-by-row format and the column-by-column format. Certain operations performed by the query processor 140 can access or manipulate data in the row store 162, the column store 164, or at least for certain types of operations (such as joins, merges, and subqueries), access or manipulate data in both the row store 162 and the column store 164. In at least some aspects, the row store 162 and the column store 164 can be maintained in main memory.

[0046] Persistence layer 168 may communicate with row store 162 and column store 164. Persistence layer 168 may be responsible for actions such as committing write transactions, storing redo log entries, rolling back transactions, and periodically writing data to storage to provide persistent data 172.

[0047] When executing a request for a database operation (such as a query or transaction), the database server 106 may need to access information stored at another location (such as another database server). The database server 106 may include a communication manager 180 component for managing such communications. The communication manager 180 may also mediate communications between the database server 106 and the client 104 or application manager 110 when the application manager is located outside the database server.

[0048] In some cases, database server 106 may be part of a distributed database system that includes multiple database servers. At least a portion of the database servers may include some or all of the components of database server 106. In some cases, the database servers of the database system may store multiple copies of data. For example, a table may be replicated at more than one database server. Additionally or alternatively, information in the database system may be distributed among multiple servers. For example, a first database server may save a copy of a first table, and a second database server may save a copy of a second table. In other embodiments, information may be divided among the database servers. For example, a first database server may save a first portion of a first table, and a second database server may save a second portion of the first table.

[0049] In executing requests for database operations, database server 106 may need to access other database servers or other information sources within the database system or at external systems (such as external systems where remote data objects are located). Communications manager 180 can be used to mediate such communications. For example, communications manager 180 can receive and route requests for information from components of database server 106 (or from another database server), and receive and route replies.

[0050] The database server 106 may include components for coordinating data processing operations involving remote data sources. In particular, the database server 106 includes a data federation component 190 that at least partially handles requests to access data maintained at a remote system. In performing its functions, the data federation component 190 may include one or more adapters 192, wherein the adapter may include logic, settings, or connection information that can be used to communicate with the remote system, such as for obtaining information to help generate a virtual parameterized data object or perform a request for data using a virtual parameterized data object (such as issuing a request to the remote system for data accessed using a corresponding parameterized data object of the remote system). Examples of adapters include "connectors" as implemented in technology available from SAP SE of Waldorf, Germany. In addition, the disclosed technology may use underlying technologies of data federation technology, such as SAP SE's Smart Data Access (SDA) and Smart Data Integration (SDI).

[0051] Example 3 - Example computing environment showing the relationship between applications, virtual data models, and database systems, including use of different data formats therewith

[0052] Figure 2 An example computing environment 200 is shown in which the disclosed technology can be implemented. The computing environment 200 includes a database system 204, one or more applications 208, and one or more application clients 212. The one or more application clients 212 can interact with the one or more applications 208 to perform create, read, update, or delete (CRUD) operations on data stored in the database system 204.

[0053] In some cases, application 208 may interact with objects 220 (shown as objects 220a-220e) in virtual data model 218, where those objects may interact with tables 224 (shown as tables 224a-224d) or views 226, where views may be materialized views (where data from a view source is persisted at a specific time when the view was created or last updated) or views where data is dynamically accessed from a source referenced by the view during the execution of database operations. Unless otherwise explicitly stated or clear from the surrounding discussion, the term "query" as used herein may include insert, delete, and update operations in addition to read operations.

[0054] Turning to the tables 224 of the database system 204, the tables 224a-224d are shown as having different data formats 230 (shown as data formats 230a-230c). The data formats 230a-230c can represent data formats, such as data types, used with one or more attributes (also referred to as fields or columns) of a given table 224. In practice, a table 224 typically includes multiple attributes, and those multiple attributes typically include several different data types, including data types that differ from each other by attributes such as length, size, or precision. To simplify the presentation, a single format is shown for each table 224. Different database tables 224 can differ in whole or in part in the data types used for the attributes, or they can have the same data type.

[0055] The objects 220 of the virtual data model 218 are also shown as having different data formats 234 (shown as data formats 234a-234c). As discussed with respect to table 224, in practice, the object 220 will have multiple attributes, wherein the attributes in an object may have the same or different data types, and wherein the attributes in different objects may have the same or different data types. It can be seen that the objects 220a, 220b, 220c all have a common format 234a, wherein the data format 234a is not shown as data format 230. The object 220d has a data format 234b that is different from the data formats 234a, 234b and also different from the data format 230. The object 220e has a data format 234c that corresponds to the data format 230a of the table 224a.

[0056] The application 208 may include a plurality of objects 240. The objects 240 may be, for example, class definitions (or definitions of other abstract or complex data types), and the application may use instances of the objects during execution of the application. The application 208 employs one or more data formats 242 (shown as data formats 242a-242d). The data formats 242 may correspond to the data formats 230, 234, or may be different from those data formats. Specifically, the data format 242a corresponds to the data format 234a, and the data format 242c corresponds to the data format 230a. The data format 242b is different from the data format 234 of the virtual data model 218 and the data format 230 of the database 204, and therefore requires conversion to store the data in the table 224, including if the data is also associated with the object 220 of the virtual data model.

[0057] In general, a "data format" refers to a particular data type or a particular implementation of a data type (e.g., nvchar(3) and nvchar(4)). A data format may refer to a "built-in" data format, such as a data type provided by a particular programming language, or a data type provided by various library objects included in a programming language. Even within the "built-in" data types, data types may have different classifications, such as having primitive data types such as "int" or "float", or having non-primitive data types such as strings and arrays. A "data format" may also refer to a user-defined data type, such as an abstract or composite data type.

[0058] As discussed in Example 1, the disclosed technology involves converting data between data formats, particularly when the conversion includes at least one table 224. That is, in the example computing environment 200, the application 208 may have some data formats 242 that are the same as the data formats 230 of the database 204, in which case conversion may not be required in order for the database to perform operations using data received from or requested by the application. However, the application 208 may have other data formats 242 that do not correspond to the data formats 230 of the database system 204. In at least some cases, it is possible to convert from the data format 242 to the data format 230. Some conversions may be inherently "lossless", while other conversions may involve the loss of information, but the loss of information may not be important to the operation of the application 208.

[0059] As also discussed in Example 1, there may be various reasons why the application 208, the virtual data model 218, and the database system 204 use different data formats even for data with the same semantic meaning. For example, data representing a date may be maintained as a string ("May 23, 2024"), or may be represented in a numeric data type (05232024) or in a data type specifically defined to store "date" information. In some cases, it may be preferable for the application 208 to accept and present data in a particular format, such as when a user is more likely to understand a date presented as a string rather than a date presented as a number or value in a data type. However, it may be more efficient to store or process date information in another format.

[0060] Figure 2 Another example is shown of why it may be beneficial to use different data types for the tables 214 and objects 220 of the virtual data model 212. That is, because the objects 220a-220c use the same data format 234a (which is also used by the application 208 as the data format 242), the application and the virtual data model may function more easily by using this common data format rather than using the data format 230 of the database system 204, even though the data associated with the application / object has the same semantic meaning as data stored in the database system.

[0061] As will be further described, data conversion functions can be implemented, such as functions 254 (shown as functions 254a, 254b) in the function registry 250 of the database system 204. The database system 204 may include functionality for associating these functions 254 with corresponding inverse functions 256 (shown as inverse functions 256a, 256b). This association can be implemented in a variety of ways. In one way, a naming convention can be implemented so that software logic (such as logic associated with the query optimizer 260) can search for inverse functions 256 given the name of a function 254 or other identifier. As shown, inverse function 256a has the same name as function 254a, but includes the term "inverse". Therefore, in this embodiment, the programming logic can check the existence of inverse function 256 by adding "inverse" to the name of a given function 254.

[0062] In another example, the database system 204 can store mapping information 264. The mapping information 264 is shown as a table with a function column and an inverse function column. Function 254b and inverse function 256b are shown as records of the table. Therefore, when specifying a function, the programming logic can check the mapping information 264 to determine whether an inverse function 256 has been specified for a given function 254.

[0063] In operation, the query optimizer 260 may use the rule-based logic 270 or the cost-based logic 272 to determine whether the inverse function 256 is available for a given function 254, and to determine whether to use the inverse function in some cases. That is, in one approach, the rule-based logic 270 always uses the inverse function 256 instead of the function 254 when the inverse function is available. In another approach, the cost-based logic 272 may consider the use of the function 254 or the inverse function 256, and select between them based on which function 254 or inverse function 256 provides a query plan with an overall lower cost.

[0064] Example 4 - Example query rewrite using inverse function instead of function

[0065] Figure 3 An example process 300 using the disclosed innovation is shown. In process 300, a client application 304 issues a query 308. In one embodiment, the query includes a function call 310. The function call 310 may be a function that performs a data type conversion and may correspond to a Figure 2 Function 254.

[0066] Alternatively, query 308 may be directed to an intermediate view 314, such as a view of a virtual data model. In one example, intermediate view 314 receives function call 310 from query 308 and includes the function call in query 318 from the intermediate view to database system 322. In another example, query 308 does not include function call 310, but rather introduces the function call in query 318 generated by intermediate view 314.

[0067] Query 318 may be processed at database system 322 to perform operations on one or more database objects 326, such as tables or views. When database 322 receives query 318, it may be processed by query optimizer 330. Query optimizer 330 prepares a query plan for executing query 318. In preparing the query plan, query optimizer 330 may apply rules 334. Rules 334 may consider whether an inverse function 342 has been specified for function 338 called by function call 310 of query 318. In some cases, the rules may consider naming conventions to determine whether function 338 has an inverse function 342. In other cases, the availability of inverse function 342 may be determined from mapping information 346.

[0068] If the query optimizer 330 determines from the rule-based approach or the cost-based approach that the inverse function 342 should be used instead of the function 338, the query optimizer 330 can prepare a query plan for a rewritten version 350 of the query 318 that uses the inverse function instead of the function. Optionally, the query 350 can be executed at 354 and the execution results returned to the client application 304.

[0069] It may be desirable to determine whether the use of the inverse function 342 provides the same or equivalent results as executing the query 318 using the function 338. Therefore, a validity check may be performed at 358. A validity check 362 may be defined for a particular inverse function 342 and may be associated with the inverse function via mapping information 346. Alternatively, an appropriate validity check 362 for the inverse function 342 may be determined in another manner, such as using a naming convention. In further cases, the validity check process of 358 may use a built-in equality operator defined for the database system 322.

[0070] Example 5 - Example functions and corresponding inverse functions and techniques for associating inverse functions with functions

[0071] Figure 4 , 5A5B show a specific example of how the disclosed technology can improve query performance by replacing a function with a specified inverse function. Code 404 creates a table "t1" with a single column for attribute 'k', which has an nvchar(5) data type. Code line 406 inserts an nvchar(5) value into the table. Table t1 populated with the value may represent data stored by a database system.

[0072] Now assume that a query 420 is received for table t1. In query 420, line 424 defines a temporary view "v" that uses a select clause in line 426 to obtain data selected from table t1 created by code 404. The "select" clause in line 426 includes a function "SUBSTRING" that takes a value of "K" and extracts a string starting at the second character (based on the value "2") and having a length of three characters (based on the value "3"). Assuming the example string "ABCDE", the SUBSTRING function will return BCD. The RTRIM function removes any spaces from the resulting substring, designated as "ks" within the temporary view "v". The "select" clause at line 428 extracts a value from the temporary view "v" that is equal to the condition "001".

[0073] For query 420, the query optimizer will typically generate filter condition 436 for evaluation by the database execution engine. As can be seen, filter condition 436 performs a substring operation on each value of "k". Substring operations involve additional computational complexity compared to simple value comparisons, especially when a large number of values ​​are being processed (for enterprise-level software applications, this can be thousands or even millions of rows).

[0074] Figure 5A The creation of a function 500 CONVERT_NUMC that converts a 5-character nvchar input to a 3-character nvchar output is shown. The body 504 of the function creates a string converted_NUMC corresponding to the SUBSTRING function of query 420. Query 508 shows how function 500 can be used as a filter condition in a select operation. As discussed, function 500 may suffer from degraded performance, especially when a large number of records are to be processed.

[0075] Figure 5B The creation of an inverse function 530 corresponding to function 500 is shown. In this case, function 530 returns the value of the variable inverted_NUMC as a 5-character nvchar value. The body 536 of function 530 implements the conversion by concatenating two leading zeros with the input provided. Line 542, which creates inverse function 530, includes the keyword "INVERSE" and includes the "FOR" keyword, which is followed by an identifier of a non-inverse function (such as function 500) to which function 530 is mapped.

[0076] Figure 5B Also shown is how the query optimizer can use the inverse function during query optimization. Assume that query 560 is received, which corresponds to query 508. However, in this case, the query optimizer uses, for example, a mapping created using the "FOR" keyword, or based on a namespace convention (where CONVERT_NUMC_INVERSED would be maintained as corresponding to CONVERT_NUMC, since the only difference in the function name is the addition of "INVERSED" in the inverse function name).

[0077] Based on rules or based on cost considerations, the query optimizer may rewrite query 560 into query 570 using an inverse function. According to function 530, two leading zeros will be added to the input of "001" to provide an output of "00001". The output is now in nvchar(5) format and can therefore be directly compared to the value stored in the database. Thus, it can be seen that the disclosed technology can allow database queries to be executed with fewer conversion operations. More generally, inverse functions can be defined to improve query performance, even if not by reducing the number of conversion operations. That is, for example, an inverse function can be useful even if it does not change the number of conversion operations, such as if the inverse function has a more efficient conversion process than a conversion process using a non-inverse function.

[0078] As discussed in Example 4, additional operations may be performed, such as confirming that use of inverse function 530 provides a result that corresponds to the result of function 500. Mathematically expressed, this confirmation may include determining that f'(f(X)) = X for a value "X", where f is a non-inverse function and f' is an inverse function. In a particular example, the validation may be performed based on a command to execute a query (or generate a query execution plan for later execution). An example query execution / plan compilation request may be "SELECT [...] FUNCTION CALL <verify>[…]" or "PREPARE SELECT[…]FUNCTION CALL <verify>[…]", where "[…]" indicates adding a query command.

[0079] Note that in some cases, the "inverse function" may be a "strict" inverse function, in that the function and the inverse function have the above relationship using a strict equality operator. In other cases, the "inverse function" may be a "loose" inverse function, where the relationship is satisfied using some other equality operator or test, such as a custom test defined based on a specific usage scenario of the data to be processed by the query using the function / inverse function.

[0080] Example 6 - Example query optimization

[0081] Figure 6 600 is a flow chart of a process 600 for defining a function and an inverse function and using the inverse function during query optimization. At 604, a request to register a function is received. As part of 604, the function can be registered in a registry and can be accessed during query optimization and execution. At 608, a request to register an inverse function is received, where the inverse function can also be registered in the registry. In at least some cases, the operation at 608 can include storing information mapping the function to the inverse function.

[0082] At 616, a query is received and an optimization process 614 is initiated. At 620, the optimization process 614 determines whether the query includes a function call. If the query does not include a function call, an optimization of the execution plan of the query may be performed at 624, and the process 614 may end at 628.

[0083] If it is determined at 620 that the query includes a function call, then it is determined whether an inverse function is associated with the function of the function call at 632. For example, mapping information may be retrieved to determine whether the inverse function has been mapped to the function call, or a naming convention may be used to determine an expected identifier for the inverse function, and it may be determined whether an inverse function with that identifier is available.

[0084] If it is determined at 632 that the inverse function is not associated with the function of the function call, process 600 can continue to use the function to optimize a portion of the query. The process can then return to 620 to determine whether the query includes additional function calls.

[0085] If it is determined at 632 that an inverse function is associated with the function of function all, the process can use the inverse function to optimize that portion of the query. Optionally, it can be determined at 644 whether the query utilizing the inverse function is more efficient than using the function. If not, the process 600 can reverse the processing of the query to use the function. After 644 or 648, or if 644 is omitted, the process 600 can return to 620 to determine whether the query includes additional function calls.

[0086] Example 7 - Example Operation

[0087] Figure 7 7 is a flow chart of a process 700 for optimizing queries, the process 700 including replacing a function with an inverse function. As described, in at least some cases, the use of an inverse function can provide improved query performance compared to the use of the function. At 705, a function definition of a function is received. At 710, an inverse function definition of an inverse function is received from a client, wherein the inverse function is the inverse of the function. At 715, a query is received, wherein the query includes a call to the function. At 720, it is determined that an inverse function is available for the function. At 725, a query execution plan is prepared, which includes the inverse function in place of the function.

[0088] Example 8 - Calculation System

[0089] Figure 8 A generalized example of a suitable computing system 800 is depicted in which the described innovations may be implemented. Computing system 800 is not intended to suggest any limitation as to the scope of use or functionality of the present disclosure, as the innovations may be implemented in various general-purpose or special-purpose computing systems.

[0090] refer to Figure 8 , the computing system 800 includes one or more processing units 810, 815 and memories 820, 825. Figure 8 , the basic configuration 830 is included within the dashed line. Processing units 810, 815 execute computer executable instructions, such as for implementing the database environment and associated methods described in Examples 1-7. The processing unit can be a general-purpose central processing unit (CPU), a processor in an application-specific integrated circuit (ASIC), or any other type of processor. In a multi-processing system, multiple processing units execute computer executable instructions to increase processing power. For example, Figure 8 A central processing unit 810 and a graphics processing unit or co-processing unit 815 are shown. Tangible memory 820, 825 may be volatile memory (e.g., registers, cache, RAM), non-volatile memory (e.g., ROM, EEPROM, flash memory, etc.), or some combination of both accessible by the processing units 810, 815. The memory 820, 825 stores software 880 implementing one or more innovations described herein in the form of computer-executable instructions suitable for execution by the processing units 810, 815.

[0091] The computing system 800 may have additional features. For example, the computing system 800 includes storage 840, one or more input devices 850, one or more output devices 860, and one or more communication connections 870. An interconnection mechanism (not shown) such as a bus, controller, or network interconnects the components of the computing system 800. Typically, operating system software (not shown) provides an operating environment for other software executed in the computing system 800 and coordinates the activities of the components of the computing system 800.

[0092] Tangible storage 840 may be removable or non-removable and include disks, tapes or cassettes, CD-ROMs, DVDs, or any other medium that can be used to store information in a non-transitory manner and that can be accessed within computing system 800. Storage 840 stores instructions for software 880 implementing one or more innovations described herein.

[0093] The input device 850 may be a touch input device (such as a keyboard, mouse, pen, or trackball), a voice input device, a scanning device, or another device that provides input to the computing system 800. The output device 860 may be a display, a printer, a speaker, a CD burner, or another device that provides output from the computing system 800.

[0094] Communication connection 870 enables communication with another computing entity (such as another database server) via a communication medium. The communication medium transmits information such as computer executable instructions, audio or video input or output, or other data in a modulated data signal. A modulated data signal is a signal that has one or more of its characteristics set or changed in a manner that encodes the information in the signal. By way of example and not limitation, the communication medium may use electrical, optical, RF or other carriers.

[0095] The innovation may be described in the general context of computer executable instructions, such as computer executable instructions included in a program module that is executed in a computing system on a target real or virtual processor. Typically, a program module or component includes a routine, program, library, object, class, component, data structure, etc. that performs a specific task or implements a specific abstract data type. In various embodiments, the functionality of the program modules may be combined or split between program modules as desired. The computer executable instructions for the program modules may be executed within a local or distributed computing system.

[0096] The terms "system" and "device" are used interchangeably herein. Unless the context clearly indicates otherwise, neither term implies any limitation on the type of computing system or computing device. In general, a computing system or computing device may be local or distributed, and may include any combination of special-purpose hardware and / or general-purpose hardware with software that implements the functionality described herein.

[0097] For purposes of presentation, the detailed description uses terms such as "determine" and "use" to describe computer operations in a computing system. These terms are high-level abstractions for operations performed by a computer and should not be confused with actions performed by a human. The actual computer operations corresponding to these terms vary depending on the implementation.

[0098] Example 9 - Cloud computing environment

[0099] Fig. 9 An example cloud computing environment 900 is depicted in which the described techniques may be implemented. Cloud computing environment 900 includes cloud computing services 910. Cloud computing services 910 may include various types of cloud computing resources, such as computer servers, data repositories, networking resources, etc. Cloud computing services 910 may be centrally located (e.g., provided by a data center of an enterprise or organization) or distributed (e.g., provided by various computing resources located in different locations, such as different data centers and / or located in different cities or countries).

[0100] The cloud computing service 910 is utilized by various types of computing devices (e.g., client computing devices), such as computing devices 920, 922, and 924. For example, the computing devices (e.g., 920, 922, and 924) may be computers (e.g., desktop or laptop computers), mobile devices (e.g., tablet computers or smart phones), or other types of computing devices. For example, the computing devices (e.g., 920, 922, and 924) may utilize the cloud computing service 910 to perform computing operations (e.g., data processing, data storage, etc.).

[0101] Example 10 - Implementation

[0102] Although the operations of some of the disclosed methods are described in a particular sequential order for ease of presentation, it should be understood that this description encompasses rearrangement unless the specific language set forth herein requires a particular ordering. For example, the operations described in sequence may be rearranged or performed simultaneously in some cases. In addition, for simplicity, the accompanying drawings may not show the various ways in which the disclosed methods may be used in conjunction with other methods.

[0103] Any disclosed method may be implemented as computer executable instructions or a computer program product stored on one or more computer readable storage media (such as tangible, non-transitory computer readable storage media) and executed on a computing device (e.g., any available computing device, including a smartphone or other mobile device that includes computing hardware). Tangible computer readable storage media is any available tangible medium that can be accessed within a computing environment (e.g., one or more optical media disks such as DVDs or CDs, volatile memory components (such as DRAM or SRAM), or non-volatile memory components (such as flash memory or a hard drive)). As an example and with reference to Figure 8 , computer-readable storage media include memories 820 and 825 and storage device 840. The term computer-readable storage medium does not include signals and carrier waves. In addition, the term computer-readable storage medium does not include communication connections (eg, 870).

[0104] Any computer executable instructions for implementing the disclosed technology and any data created and used during the disclosed embodiments of the implementation method can be stored on one or more computer readable storage media. Computer executable instructions can be, for example, a dedicated software application or a part of a software application accessed or downloaded via a web browser or other software application (such as a remote computing application). Such software can be executed, for example, on a single local computer (e.g., any suitable commercially available computer) or in a network environment using one or more network computers (e.g., via the Internet, a wide area network, a local area network, a client-server network (such as a cloud computing network) or other such networks).

[0105] For the sake of clarity, only some selected aspects of the software-based implementation are described. Other details known in the art are omitted. For example, it should be understood that the disclosed technology is not limited to any specific computer language or program. For example, the disclosed technology can be implemented by software written in C++, Java, Perl, JavaScript, Python, Ruby, ABAP, structured query language, Adobe Flash or any other suitable programming language, or in some examples, by a markup language such as html or XML or a combination of suitable programming languages ​​and markup languages. Similarly, the disclosed technology is not limited to any specific computer or hardware type. Some details of suitable computers and hardware are known and do not need to be elaborated in this disclosure.

[0106] In addition, any software-based embodiments (including, for example, computer-executable instructions for causing a computer to perform any disclosed method) can be uploaded, downloaded, or remotely accessed via suitable communication means. Such suitable communication means include, for example, the Internet, the World Wide Web, an intranet, a software application, cables (including fiber optic cables), magnetic communications, electromagnetic communications (including RF, microwave, and infrared communications), electronic communications, or other such communication means.

[0107] The disclosed methods, apparatus, and systems should not be construed as limiting in any way. Rather, the present disclosure is directed to all novel and non-obvious features and aspects of the various disclosed embodiments, individually and in various combinations and sub-combinations with each other. The disclosed methods, apparatus, and systems are not limited to any particular aspect or feature or combination thereof, nor do the disclosed embodiments require the presence of any one or more specific advantages or problems solved.

[0108] The technology from any example can be combined with the technology described in any one or more other examples. In view of the many possible embodiments to which the principles of the disclosed technology can be applied, it should be recognized that the illustrated embodiments are examples of the disclosed technology and should not be considered as limiting the scope of the disclosed technology. On the contrary, the scope of the disclosed technology includes what is covered by the scope and spirit of the appended claims.< / verify> < / verify>

Claims

1. A computing system, include: at least one memory; one or more hardware processor units coupled to the at least one memory; as well as One or more computer-readable storage media storing computer-executable instructions that, when executed, cause the computing system to perform operations comprising: Function definition of the receiving function; Receiving an inverse function definition of an inverse function from a client, wherein the inverse function is an inverse function of the function; receiving a query, the query comprising a call to the function; determining that the inverse function is applicable to the function; and A query execution plan is prepared that includes the inverse function in place of the function.

2. The computing system according to claim 1, wherein the operation further include: storing a mapping between the function and the inverse function; Wherein determining that the inverse function is applicable to the function comprises determining that the inverse function is applicable to the function according to the mapping.

3. The computing system according to claim 2, in, Determining that the inverse function is available for the function includes determining that the inverse function is associated with an identifier of the function.

4. The computing system according to claim 2, in, The mapping is stored in a mapping table having a first attribute configured to store a function identifier and a second attribute configured to store an identifier of an inverse function corresponding to a given function.

5. The computing system according to claim 1, in, Determining that the inverse function is available for the function includes using a naming convention defined for functions and inverse functions.

6. The computing system according to claim 5, in, The inverse function and the function share at least a portion of an identifier of the function, and wherein the identifier of the inverse function includes a token indicating that the inverse function is used as an inverse function.

7. The computing system according to claim 1, in, Preparing a query execution plan including the inverse function in place of the function includes selecting the inverse function in place of the function based on a rule of a query optimizer.

8. The computing system according to claim 1, in, Preparing a query execution plan including the inverse function in place of the function includes: determining at least a portion of a first query plan cost of a first putative query plan using the function; determining at least a portion of a second query plan cost for a second putative query plan using the inverse function; and Based on determining that the at least a portion of the second query plan cost is less than the at least a portion of the first query plan cost, the at least a portion of the second putative query plan is selected.

9. The computing system according to claim 1, in, The query includes calling a plurality of functions, the plurality of functions including the function, and the operation further includes: A determination is made for a plurality of functions of the plurality of functions whether a corresponding inverse function is available for a given function of the plurality of functions.

10. The computing system according to claim 9, in, The rules used by the query optimizer specify that when an inverse function is defined for a given function, the inverse function is always used for the function.

11. The computing system according to claim 9, in, For each of the plurality of functions, a determination is made as to whether the corresponding inverse function should be used for the given function based on comparing a query execution cost of using the given function with a query execution cost of using the corresponding inverse function in place of the given function.

12. The computing system of claim 1, in, The function and inverse function convert data between data formats.

13. The computing system of claim 1, in, The query includes values ​​used in a selection condition of the query, and the function converts the values ​​between data formats.

14. The computing system of claim 13, in, A first one of the data formats is a format used by objects of a virtual data model, and a second one of the data formats is used by database objects.

15. The computing system of claim 14, wherein the inverse function converts the value in the selection condition to a data format used by the database object, and the function converts the value of the database object to the format of the object of the virtual data model.

16. The computing system of claim 1, wherein the operation further comprises: include: executing the query execution plan to generate a first result; executing a second query execution plan including the function in place of the inverse function to generate a second result; as well as The first result and the second result are compared to determine whether the inverse function produces an equivalent result as using the function.

17. The computing system of claim 16, in, The executing the second query execution plan and the comparing are performed in response to commands provided in the query.

18. A method implemented in a computing system, the computing system comprising at least one memory and at least one hardware processor coupled to the at least one memory, the method include: Function definition of the receiving function; Receiving an inverse function definition of an inverse function from a client, wherein the inverse function is an inverse function of the function; receiving a query, the query comprising a call to the function; determining that the inverse function is applicable to the function; and A query execution plan is prepared that includes the inverse function in place of the function.

19. The method according to claim 18, in, Determining that the inverse function is applicable to the function includes: using a naming convention defined for functions and inverse functions to determine that the inverse function is available for the inverse function; or The inverse function is determined to be available for the inverse function using a mapping of the inverse function to the function.

20. One or more non-transitory computer-readable storage media, include: computer executable instructions that, when executed by a computing system comprising at least one hardware processor and at least one memory coupled to the at least one hardware processor, cause the computing system to receive a function definition for a function; computer executable instructions that, when executed by the computing system, cause the computing system to receive from a client an inverse function definition of an inverse function, wherein the inverse function is an inverse of the function; computer-executable instructions that, when executed by the computing system, cause the computing system to receive a query, the query comprising a call to the function; computer executable instructions that, when executed by the computing system, cause the computing system to determine that the inverse function is applicable to the function; and Computer executable instructions that, when executed by the computing system, cause the computing system to prepare a query execution plan that includes the inverse function in place of the function.

Images