Method and device for improving query performance of database window function

Abstract

This invention discloses a method and apparatus for improving the performance of window function queries in a database. The method includes: obtaining a query statement; determining an optimization type based on the query statement; and optimizing the database query performance according to the optimization type. The optimization types include: result space pre-allocation optimization, optimization using existing sorting in the storage engine, and vectorized computation optimization. The solution provided by this invention can effectively optimize the window function processing flow in high-performance distributed time-series databases.

Description

Technical Field

[0001] This invention relates to the field of database technology applications, and in particular to a method and apparatus for improving the performance of database window function queries. Background Technology

[0002] High-performance distributed time-series databases have proposed a new clause, `context by`, to address the usage requirements and scenarios of window functions, significantly simplifying the ease of use of queries related to window functions. However, the naive `context by` processing flow has some inefficiencies.

[0003] The processing flow of the context by clause in related technologies is as follows:

[0004] Step 1: Group the time series data according to certain rules;

[0005] Step 2: Filter all groups that meet the requirements;

[0006] Step 3: Take some or all of the data for each group;

[0007] Step 4: Calculate the order sensitivity function, such as cumulative summation, for each group. The final output will have the same number of rows as the input data.

[0008] There is currently no effective solution to the problem that existing technologies are not efficient enough in the context by processing flow in high-performance distributed time-series databases. Summary of the Invention

[0009] To address the aforementioned technical problems, embodiments of the present invention aim to provide a method and apparatus for improving the performance of database window function queries, thereby at least resolving the issue of insufficient efficiency in the context by processing flow of high-performance distributed time-series databases due to the limitations of existing technologies.

[0010] The technical solution of this invention is implemented as follows:

[0011] This invention provides a method for improving the performance of database window function queries, comprising: obtaining a query statement; determining an optimization type based on the query statement; and optimizing the database query performance based on the optimization type; wherein the optimization types include: result space pre-allocation optimization, optimization using existing sorting in the storage engine, and vectorized computation optimization.

[0012] Optionally, optimizing database query performance based on the optimization type includes: when the optimization type is result space pre-allocation optimization, determining whether the query statement contains a preset keyword; if the result is yes, calculating and returning the result based on the context by logic; if the result is no, grouping based on all possible combinations of values ​​of the field after the context by keyword, and sorting within each group according to the csort field; creating an array with a length equal to the length of the input data; and looping through at least one of the created arrays.

[0013] Further, optionally, the loop processing of at least one array includes: calculating the value of each select column based on the grouping result, and writing the value of each select column into the corresponding position of the array of the corresponding column.

[0014] Optionally, optimizing database query performance based on the optimization type includes: When the optimization type utilizes the existing sorting in the storage engine, determining whether the data in the storage engine is ordered; if the result is negative, calculating and returning the result using context by logic; where context by logic includes result space pre-allocation optimization; if the result is positive, determining whether the field used for sorting in the storage engine intersects with the context by grouping field; if the result indicates no intersection between the sorting field and the context by grouping field, calculating and returning the result using context by logic; if the result indicates intersection between the sorting field and the context by grouping field, grouping the data within the storage engine using the sorting information of the common field; processing the grouped arrays in a loop; and merging each group of results.

[0015] Further, optionally, the iterative processing of the grouped array includes: determining whether the fields used by the storage engine for sorting completely cover the context by grouping fields and whether there is no having clause; if the result is no, collecting the context by grouping fields not covered by the fields sorted by the storage engine, calculating the result of this group using context by logic, and continuing to the next group; if the result is yes, determining whether there is a csort clause; if the result is no csort clause, calculating the value of each select column; determining whether the array data has already been sorted by the csort field in the storage engine; if the result is yes, calculating the value of each select column; sorting the array data according to the csort field; calculating the value of each select column; determining whether there is a top or limit keyword; if the result is no top or limit keyword, merging each group result after processing; if the result is yes, processing the top or limit, retaining the limited preset row data.

[0016] Optionally, optimizing database query performance based on the optimization type includes: When the optimization type is vectorized computation optimization, determining whether the SELECT list does not contain aggregate functions, sequence correlation functions, or HAVING clauses; if the result is negative, calculating the result using the Context BY logic; where the Context BY logic includes result space pre-allocation optimization; if the result is positive, grouping the data according to all possible combinations of values ​​of the field following the Context BY keyword, obtaining the original data's sorted indices, and determining if the CSO keyword is present; if the CSO keyword is absent, determining if the TOP or LIMIT keyword is present; if the CSO keyword is present, looping through each group of data until the loop ends, determining if the loop's final processing result contains the TOP or LIMIT keyword; if the TOP or LIMIT keyword is absent, using the processed indices as filtering conditions to calculate the value of each SELECT column; if the TOP or LIMIT keyword is present, processing the TOP or LIMIT keyword, retaining the indices of specified rows in each group; where looping through each group of data includes: within each group, sorting the original data's sorted indices according to the sorting method specified by CSO.

[0017] This invention provides an apparatus for improving the performance of database window function queries, comprising: an acquisition module for acquiring a query statement; a determination module for determining an optimization type based on the query statement; and an optimization module for optimizing the database query performance based on the optimization type. The optimization types include: result space pre-allocation optimization, optimization using existing sorting in the storage engine, and vectorized computation optimization.

[0018] Optionally, the optimization module includes: a first judgment unit, used to determine whether the query statement contains a preset keyword when the optimization type is result space pre-allocation optimization; a first execution unit, used to calculate and return the result based on the context by logic when the judgment result is yes; a second execution unit, used to group all value combinations of the field after the context by keyword when the judgment result is no, and sort the group according to the csort field; create an array with a length equal to the length of the input data; and process at least one of the created arrays in a loop.

[0019] Optionally, the optimization module includes: a second judgment unit, used to determine whether the data in the storage engine is ordered when the optimization type is optimization using the existing sorting of the storage engine; a third execution unit, used to calculate and return the result through context by logic when the judgment result is negative; wherein, the context by logic includes result space pre-allocation optimization; a third judgment unit, used to determine whether the field used for sorting in the storage engine and the context by grouping field have intersection when the judgment result is positive; a fourth execution unit, used to calculate and return the result through context by logic when the judgment result is negative and the context by grouping field has no intersection; a fifth execution unit, used to group the data in the storage engine using the sorting information of the common field when the judgment result is positive and the context by grouping field has intersection; loop through the grouped arrays; and merge each group result after processing.

[0020] Optionally, the optimization module includes: a fourth judgment unit, used to determine whether the queried select list does not contain aggregate functions, sequence correlation functions, and has no having clause when the optimization type is vectorized calculation optimization; a sixth execution unit, used to calculate the result through context by logic when the judgment result is negative; wherein the context by logic includes result space pre-allocation optimization; a fifth judgment unit, used to group according to all value combinations of the field after the context by keyword when the judgment result is positive, obtain the index of the original data after sorting, and determine whether there is a csort keyword; a seventh execution unit, used to determine whether there is a top or limit keyword when the judgment result is negative; an eighth execution unit, used to process each group of data in a loop until the loop ends when the judgment result is positive, and determine whether the processing result at the end of the loop has a top or limit keyword; a ninth execution unit, used to calculate the value of each select column using the processed index as a filter condition when the judgment result is negative; and a tenth execution unit, used to process top or limit when the judgment result is positive, and retain the index of each specified row in each group.

[0021] This invention provides a method and apparatus for improving the performance of window function queries in a database. The method involves obtaining a query statement; determining an optimization type based on the query statement; and optimizing the database query performance based on the optimization type. The optimization types include: result space pre-allocation optimization, optimization using existing sorting in the storage engine, and vectorized computation optimization. This achieves the technical effect of optimizing the window function processing flow in a high-performance distributed time-series database. Attached Figure Description

[0022] The accompanying drawings, which are included to provide a further understanding of the invention and form part of this application, illustrate exemplary embodiments of the invention and, together with their description, serve to explain the invention and do not constitute an undue limitation thereof. In the drawings:

[0023] Figure 1 A flowchart illustrating a method for improving database window function query performance according to an embodiment of the present invention;

[0024] Figure 2 This is a flowchart illustrating the result space pre-allocation optimization process in a method for improving the performance of database window function queries provided in an embodiment of the present invention.

[0025] Figure 3This is a schematic diagram illustrating the optimization process of an existing sorting algorithm in a database window function query performance improvement method provided in an embodiment of the present invention.

[0026] Figure 4 This is a flowchart illustrating the vectorized computation optimization process in a method for improving the performance of database window function queries provided in an embodiment of the present invention.

[0027] Figure 5 This is a schematic diagram of a device for improving the performance of database window function queries, provided in an embodiment of the present invention. Detailed Implementation

[0028] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0029] It should be noted that the terms "first," "second," etc., in the specification, claims, and drawings of this invention are used to distinguish different objects, rather than to limit a specific order.

[0030] It should also be noted that the various embodiments of the present invention described below can be executed individually or in combination with each other, and the embodiments of the present invention do not impose specific limitations in this regard.

[0031] Technical terms related to this application:

[0032] Aggregate functions: perform calculations on a set of data and return a single value;

[0033] Vector functions: perform calculations on a set of data and return a vector of the same length as the input data;

[0034] Order-sensitive functions: Functions whose output depends on the order of the input data. The output may be a single value or a vector; changing the order of the input data will affect the result.

[0035] Moving window functions (window functions): These define left and right boundaries for the input data, continuously move these boundaries, perform calculations based on the data within each boundary, and generate the value corresponding to the current window. The result is typically a vector of the same length as the input data. For example, in cumulative summation, the left boundary of the window remains unchanged, while the right boundary slides from the beginning to the end of the data. Each time, the sum of the data within the left and right boundaries is calculated, and this sum is used as the result at the right boundary position. Moving window functions are order-sensitive functions.

[0036] Window functions: Window functions perform aggregation-like operations on the data in a query. Unlike traditional aggregation operations (such as GROUP BY), which produce only one row for each set of inputs, window functions generate a result for each row of the input.

[0037] This invention provides a method for improving the performance of database window function queries. Figure 1 This is a flowchart illustrating a method for improving database window function query performance according to an embodiment of the present invention; as shown below. Figure 1 As shown in the embodiments of this application, the method for improving the performance of database window function queries includes:

[0038] Step S102: Obtain the query statement;

[0039] Step S104: Determine the optimization type based on the query statement;

[0040] Step S106: Optimize database query performance according to the optimization type;

[0041] The optimization types include: result space pre-allocation optimization, optimization using existing sorting in the storage engine, and vectorized computation optimization.

[0042] The database window function query performance improvement method provided in this application embodiment addresses the inefficiencies in the context by process by adding the following three optimization mechanisms to DolphinDB:

[0043] Optimization Mechanism 1: Result Space Pre-allocation Optimization: Solves the problem of additional overhead caused by append operations;

[0044] Optimization Mechanism 2: Utilizing the storage engine's existing sorting information to optimize grouping performance;

[0045] Optimization Mechanism 3: Vectorized Computation Optimization: For queries where the select list does not contain aggregate functions, sequence-related functions, or having clauses, this optimizes the overhead of retrieving data group by group and repeatedly calling the calculation interface.

[0046] Based on the above, Figure 2 This is a flowchart illustrating the result space pre-allocation optimization process in a method for improving database window function query performance provided by an embodiment of the present invention; as follows: Figure 2 As shown, optimization mechanism 1, result space pre-allocation optimization, specifically includes:

[0047] Optionally, step S106, which optimizes database query performance based on the optimization type, includes: when the optimization type is result space pre-allocation optimization, determining whether the query statement contains a preset keyword; if the determination result is yes, calculating and returning the result based on the context by logic; if the determination result is no, grouping according to all value combinations of the field after the context by keyword, and sorting within the group according to the csort field; creating an array with a length equal to the length of the input data; and looping through at least one of the created arrays.

[0048] Further, optionally, the loop processing of at least one array includes: calculating the value of each select column based on the grouping result, and writing the value of each select column into the corresponding position of the array of the corresponding column.

[0049] Specifically, such as Figure 2 As shown, to address the overhead of appending the results of each group in the context by algorithm, we can pre-allocate memory space to store all group results. When calculating each group, the data is directly written to the corresponding location in the result data. The optimization logic is as follows:

[0050] Step 1: Determine if the query contains "having" or "top" (i.e., the preset keywords in this application embodiment). If it does, calculate and return the result using the original context by logic; otherwise, continue execution.

[0051] Step 2: Group the data according to all possible combinations of values ​​in the field following the context by keyword, and sort the data within each group by the csort field.

[0052] Step 3: Create an array with a length equal to the length of the input data;

[0053] Step 4: Process each set of data in a loop: Specifically, processing each set of data in a loop involves:

[0054] Step 4.1: Calculate the value of each selected column using the data within each group;

[0055] Step 4.2: The calculation results of each select column in this group are directly written to the corresponding position in the result array of that column;

[0056] Step 5: Return the results.

[0057] In the embodiments of this application, the result space pre-allocation optimization avoids the extra overhead of frequent memory allocation and manipulation of the internal data structure of the array caused by pre-allocating memory space of the same length as the result and directly writing the calculation result to the corresponding position. This greatly improves the performance of context by queries with a large number of groups.

[0058] Figure 3 This invention provides a schematic diagram illustrating the optimization process of an existing sorting algorithm in a database window function query performance improvement method, as shown in the embodiment of the invention. Figure 3 As shown, optimization mechanism 2, which utilizes the existing sorting mechanism of the storage engine, specifically involves:

[0059] Optionally, step S106, which optimizes database query performance based on the optimization type, includes: if the optimization type is to utilize the existing sorting in the storage engine, determining whether the data in the storage engine is ordered; if the result is negative, calculating and returning the result using context by logic; wherein, the context by logic includes result space pre-allocation optimization; if the result is positive, determining whether the field used for sorting in the storage engine intersects with the context by grouping field; if the result is negative, calculating and returning the result using context by logic; if the result is positive, grouping the data within the storage engine using the sorting information of the common field; processing the grouped arrays in a loop; and merging each group of results.

[0060] Further, optionally, the iterative processing of the grouped array includes: determining whether the fields used by the storage engine for sorting completely cover the context by grouping fields and whether there is no having clause; if the result is no, collecting the context by grouping fields not covered by the fields sorted by the storage engine, calculating the result of this group using context by logic, and continuing to the next group; if the result is yes, determining whether there is a csort clause; if the result is no csort clause, calculating the value of each select column; determining whether the array data has already been sorted by the csort field in the storage engine; if the result is yes, calculating the value of each select column; sorting the array data according to the csort field; calculating the value of each select column; determining whether there is a top or limit keyword; if the result is no top or limit keyword, merging each group result after processing; if the result is yes, processing the top or limit, retaining the limited preset row data.

[0061] Specifically, such as Figure 3 As shown, if the data is already sorted in the storage engine according to certain context by grouping fields, this information can be used to avoid some redundant calculations. The optimization logic is as follows:

[0062] Step 1: Determine if the data in the storage engine is ordered. If it is unordered, use the original logic (including result space pre-allocation optimization) to calculate and return the result; otherwise, continue execution.

[0063] Step 2: Determine if there is any overlap between the field used for sorting by the storage engine and the grouping field of context by. If not, calculate and return the result using the original logic of context by (including result space pre-allocation optimization); otherwise, continue execution.

[0064] Step 3: Group the data using the sorting information of common fields within the storage engine;

[0065] Step 4: Process the groups assigned by storage engines in a loop: Specifically, the processing of the groups assigned by storage engines in a loop is as follows:

[0066] Step 4.1: Determine if the storage engine's sorting field completely covers the context by grouping field and if there is no having clause; if not, collect the context by grouping fields that are not covered by the storage engine's sorting field, calculate the result of this group using the original context by logic (including result space pre-allocation optimization), and continue processing the next group; otherwise, continue execution.

[0067] Step 4.2: Check if a `csort` clause exists. If not, proceed to step 4.5; otherwise, continue execution.

[0068] Step 4.3: Determine if the data has been sorted by the csort field in the storage engine. If so, proceed to step 4.5; otherwise, continue execution.

[0069] Step 4.4: Sort the data in this group according to the csort field;

[0070] Step 4.5: Calculate the value of each select column;

[0071] Step 4.6: Check if the keywords "top" or "limit" exist. If not, proceed to step 5; otherwise, continue execution.

[0072] Step 4.7: Process top or limit to retain the first few or last few rows (i.e., the preset row data to be retained in this embodiment).

[0073] Step 5: Merge the processed results from each group;

[0074] Step 6: Return the results.

[0075] In this embodiment, by making the most of the existing sorting information in the storage engine for grouping optimization, the computational resources required for grouping are reduced and performance is improved.

[0076] Figure 4 This is a flowchart illustrating the vectorized computation optimization process in a method for improving database window function query performance provided in an embodiment of the present invention; as follows: Figure 4 As shown, optimization mechanism 3, vectorized computation optimization, specifically includes:

[0077] Optionally, step S106, which optimizes database query performance based on the optimization type, includes: When the optimization type is vectorized computation optimization, determining whether the queried select list does not contain aggregate functions, sequence correlation functions, or a having clause; if the result is negative, calculating the result using context by logic; wherein, context by logic includes result space pre-allocation optimization; if the result is positive, grouping the data according to all possible combinations of values ​​of the field after the context by keyword, obtaining the original data sorted index, and determining whether the csort keyword exists; if the csort keyword does not exist, determining whether the top or limit keyword exists; if the csort keyword exists, processing each group of data in a loop until the loop ends, and determining whether the processing result at the end of the loop contains the top or limit keyword; if the top or limit keyword does not exist, using the processed index as a filter condition, calculating the value of each select column; if the top or limit keyword exists, processing the top or limit, retaining the index of each specified row in each group; wherein, processing each group of data in a loop includes: within each group, sorting the original data sorted index according to the sorting method specified by csort.

[0078] Specifically, such as Figure 4 As shown, for queries where the select list does not contain aggregate functions, sequence correlation functions, or a having clause, the csort, top, and limit results for each group of data can be represented by the index of each row in the original data. Then, the processed indices can be used as filtering conditions to retrieve the data uniformly and calculate the select column, reducing the overhead of loops and repeated API calls. The optimization logic is as follows:

[0079] Step 1: Determine if the SELECT list does not contain aggregate functions or sequence-related functions, and does not have a HAVING clause. If not, calculate the result using the original logic of Context BY (including result space pre-allocation optimization); otherwise, continue execution.

[0080] Step 2: Group the data according to all possible combinations of values ​​for the field following the "context by" keyword, and obtain the index of the original data after sorting.

[0081] Step 3: Check if the keyword "csort" exists. If not, proceed to step 5; otherwise, continue execution.

[0082] Step 4: Process each set of data in a loop: Specifically, processing each set of data in a loop involves:

[0083] Step 4.1: Within each group, sort the indices obtained in Step 2 according to the sorting method specified by csort;

[0084] Step 5: Check if the keywords "top" or "limit" exist. If not, proceed to step 7; otherwise, continue execution.

[0085] Step 6: Process top or limit, and retain the indices of the first few rows or the last few rows of each group (i.e., the indices of the specified rows of each group in this embodiment of the application).

[0086] Step 7: Using the processed subscripts as filter conditions, calculate the value of each select column;

[0087] Step 8: Return the results.

[0088] In this embodiment, vectorized computation optimization processes grouping, csorting, top, and limit on the dimension of the input data index, and then uses the processed index as a filter condition to uniformly retrieve and calculate the data, reducing unnecessary loops and additional overhead caused by repeated API calls, thus improving performance.

[0089] Furthermore, in the relevant application scenarios, context by in this application embodiment can be:

[0090] Scenario 1: Grouping and calculating multiple window functions using context by:

[0091] select*,cumsum(qty),cummax(price),cummin(price)from tbl context bysym,dt.minute();

[0092] Scenario 2: Calculating complex custom window functions:

[0093] select*,cumwavg(price,qty)from tbl context by sym,dt.minute();

[0094] Scenario 3: Calculating nested window functions:

[0095] select*,cummax(cumavg(cumstdp(price)))from tbl context by sym,dt.minute();

[0096] Scenario 4: Filter groupings and data within groups according to rules, and calculate window functions:

[0097] select top 2*,cumsum(qty)from tbl context by sym,dt.minute()csort dtdesc having sum(qty)>6500and count(sym)>2;

[0098] The functions cumsum, cummax, cummin, cumavg, cumwavg, and cumstdp used in queries are all window functions. For example, cumsum adds the values ​​of each row of input data, along with the values ​​of a preceding column, to the cumsum result of the current row.

[0099] Standard SQL window functions can only use a very limited set of built-in functions, while context by can use any aggregate function, vector function, and any other function with equal input and output lengths, including user-defined functions.

[0100] Keywords that can be used with context by include:

[0101] csort: Specifies which fields to sort the data in each group by, and whether it is in ascending or descending order;

[0102] HAVING: Filters groups and data that meet certain criteria.

[0103] For conditions involving aggregate functions, the results of each aggregate function calculation will be performed and then filtered.

[0104] For conditions that do not involve aggregate functions, all rows that meet the conditions will be filtered.

[0105] top: Retrieves the first few data entries within each group;

[0106] `limit`: Retrieves the first few records or the last few records within each group. A positive number after `limit` indicates retrieving the first few records; a negative number indicates retrieving the last few records.

[0107] The keywords above are all optional, and users can use different keyword combinations according to their needs.

[0108] The details of the context by feature are as follows:

[0109] For the data source, first group it according to all possible combinations of values ​​for the field following the context by keyword;

[0110] Within each group, sort according to the sorting method specified by csort;

[0111] Calculate the value for each selected column using the data within each group. These columns may contain aggregate functions, order-sensitive functions, etc. The calculated results have the same number of rows as the input.

[0112] Based on the results of the previous step, calculate the having condition and filter the rows that meet the condition;

[0113] Process top or limit to retain only the first or last few lines of the specified range.

[0114] By default, `context by` outputs results in the order they are grouped. To preserve the original data order, add `[HINT_KEEPORDER]` after the `SELECT` keyword.

[0115] select[HINT_KEEPORDER]top 2*,cumsum(qty)from t1 context by sym,dt.minute()csort dt desc having sum(qty)>6500and count(sym)>2.

[0116] In the embodiments of this application, the `context by` statement can perform complex calculations on time-series data with a single query in relevant scenarios. Compared to existing solutions, it eliminates the need for nested queries, significantly reducing parameter redundancy in the statement and making it less prone to errors. Furthermore, it greatly increases code maintainability compared to existing solutions. If you want to change the grouping method, filtering conditions, or sorting method within a group, you only need to modify the parameters after the corresponding keywords, making it convenient to use. Finally, it significantly improves code readability compared to existing solutions.

[0117] In this application's embodiments, `context by` abstracts complex time series processing into a single SQL query, minimizing semantic interference from linguistic details and making the computational logic of the statement readily clear to the user. When calculating multiple window functions, `context by` is more concise than standard SQL. Nesting multiple window functions in standard SQL is inconvenient, while `context by` simplifies this process. Standard SQL limits window functions to a very limited set of built-in functions, while `context by` can use any aggregate function, vector function, and any other function with equal input and output lengths, including user-defined functions. In standard SQL, filtering groups and data within groups requires pre-calculating the filtering criteria in a subquery, while `context by` simply summarizes the conditions into a single query, making it much more convenient.

[0118] It should be noted that in the database window function query performance improvement method provided in this application embodiment, the parameters that can be filled in for `select` include, but are not limited to: original data columns, aggregate functions, order-sensitive functions, moving window functions, and expressions. The keywords such as `context by`, `csort`, `having`, `top`, and `limit` used in the database window function query performance improvement method provided in this application embodiment are arbitrary in selection; replacing them with other names does not affect the semantics of the statement. The keywords such as `csort`, `having`, `top`, and `limit` in the database window function query performance improvement method provided in this application embodiment are all optional, and users can use different keyword combinations according to their needs.

[0119] The computational process proposed in the database window function query performance improvement method provided in this application is only a specific implementation for time series data calculation. As long as a certain process is followed, similar effects to the database window function query performance improvement method provided in this application can be achieved.

[0120] This invention provides a method for improving the performance of window function queries in a database. The method involves obtaining a query statement; determining the optimization type based on the query statement; and optimizing the database query performance based on the optimization type. The optimization types include: result space pre-allocation optimization, optimization using existing sorting in the storage engine, and vectorized computation optimization. This method can improve the technical effect of window function processing in high-performance distributed time-series databases.

[0121] This invention provides an apparatus for improving the performance of database window function queries. Figure 5 This is a schematic diagram of a device for improving the performance of database window function queries, provided in an embodiment of the present invention; as shown. Figure 5 As shown in the embodiment of this application, the apparatus for improving the performance of database window function queries includes:

[0122] The acquisition module 52 is used to acquire the query statement; the determination module 54 is used to determine the optimization type based on the query statement; the optimization module 56 is used to optimize the database query performance based on the optimization type; among which, the optimization types include: result space pre-allocation optimization, optimization using the existing sorting of the storage engine, and vectorized calculation optimization.

[0123] Optionally, the optimization module 56 includes: a first judgment unit, used to determine whether the query statement contains a preset keyword when the optimization type is result space pre-allocation optimization; a first execution unit, used to calculate and return the result based on the context by logic when the judgment result is yes; a second execution unit, used to group all the value combinations of the field after the context by keyword when the judgment result is no, and sort the group according to the csort field; create an array with a length equal to the length of the input data; and process at least one of the created arrays in a loop.

[0124] Optionally, the optimization module 56 includes: a second judgment unit, used to determine whether the data in the storage engine is ordered when the optimization type is optimization using the existing sorting of the storage engine; a third execution unit, used to calculate and return the result through context by logic when the judgment result is negative; wherein, the context by logic includes result space pre-allocation optimization; a third judgment unit, used to determine whether the field used for sorting in the storage engine and the context by grouping field have an intersection when the judgment result is positive; a fourth execution unit, used to calculate and return the result through context by logic when the judgment result is negative and the context by grouping field has no intersection; a fifth execution unit, used to group the data in the storage engine using the sorting information of the common field when the judgment result is positive and the context by grouping field has an intersection; loop through the grouped arrays; and merge each group of results after processing.

[0125] Optionally, the optimization module 56 includes: a fourth judgment unit, used to determine whether the queried select list does not contain aggregate functions, sequence correlation functions, and has no having clause when the optimization type is vectorized calculation optimization; a sixth execution unit, used to calculate the result through context by logic when the judgment result is negative; wherein the context by logic includes result space pre-allocation optimization; a fifth judgment unit, used to group according to all value combinations of the field after the context by keyword when the judgment result is positive, obtain the index of the original data after sorting, and determine whether there is a csort keyword; a seventh execution unit, used to determine whether there is a top or limit keyword when the judgment result is negative; an eighth execution unit, used to process each group of data in a loop until the loop ends when the judgment result is positive, and determine whether the processing result at the end of the loop has a top or limit keyword; a ninth execution unit, used to calculate the value of each select column using the processed index as a filter condition when the judgment result is negative; and a tenth execution unit, used to process top or limit when the judgment result is positive, and retain the index of each specified row in each group.

[0126] This invention provides an apparatus for improving the performance of window function queries in a database. The apparatus involves obtaining a query statement; determining an optimization type based on the query statement; and optimizing the database query performance based on the optimization type. The optimization types include: result space pre-allocation optimization, optimization using existing sorting in the storage engine, and vectorized computation optimization. This achieves the technical effect of optimizing the window function processing flow in a high-performance distributed time-series database.

[0127] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0128] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0129] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0130] The above are merely preferred embodiments of the present invention and are not intended to limit the scope of protection of the present invention.

Claims

1. A method for improving the performance of database window function queries, characterized in that, include: Retrieve the query statement; The optimization type is determined based on the query statement; Optimize database query performance based on the optimization type described above; The optimization types include: result space pre-allocation optimization, optimization using existing sorting in the storage engine, and vectorized computation optimization; The optimization of database query performance based on the optimization type includes: when the optimization type is vectorized calculation optimization, determining whether the queried select list does not contain aggregate functions, sequence correlation functions, and has no having clause; if the determination result is no, calculating the result using context by logic; wherein, the context by logic includes result space pre-allocation optimization; if the determination result is yes, grouping according to all combinations of values ​​of the field after the context by keyword, obtaining the index of the original data after sorting, and determining whether there is a csort keyword; if the determination result is no csort keyword, determining whether there is a top or limit keyword; if the determination result is a csort keyword, processing each group of data in a loop until the loop ends, determining whether the processing result at the end of the loop has a top or limit keyword; if the determination result is no top or limit keyword, using the processed index as a filter condition, calculating the value of each select column; if the determination result is a top or limit keyword, processing the top or limit, retaining the index of the specified row in each group; wherein, processing each group of data in a loop includes: within each group, sorting the index of the original data after sorting according to the sorting method specified by csort.

2. The method for improving database window function query performance according to claim 1, characterized in that, The optimization of database query performance based on the optimization type includes: When the optimization type is the result space pre-allocation optimization, determine whether the query statement contains a preset keyword; If the judgment result is yes, calculate and return the result based on the context by logic; If the result is negative, group the data according to all possible combinations of values ​​in the field following the context by keyword, and sort the data within each group by the csort field; create an array with a length equal to the length of the input data; and process at least one of the created arrays in a loop.

3. The method for improving database window function query performance according to claim 2, characterized in that, At least one of the arrays created by the loop process includes: Calculate the value of each select column based on the grouping results, and write the value of each select column into the corresponding position of the array of the corresponding column.

4. The method for improving database window function query performance according to claim 1, characterized in that, The optimization of database query performance based on the optimization type includes: When the optimization type is the optimization that utilizes the existing sorting in the storage engine, determine whether the data in the storage engine is ordered; If the result is negative, the result is calculated and returned using the context by logic; wherein, the context by logic includes result space pre-allocation optimization; If the result is yes, determine whether the field used for sorting by the storage engine and the context by grouping field have any overlap; If the sorting field and the context by grouping field have no overlap, the result is calculated and returned using the context by logic. If the sorting field and the context by grouping field have an intersection, the data is grouped using the sorting information of the common field within the storage engine; the grouped arrays are processed in a loop; and the results of each group are merged.

5. The method for improving database window function query performance according to claim 4, characterized in that, The array after grouping is processed in a loop, including: Determine if the fields used for sorting by the storage engine completely cover the context by grouping fields, and if there is no having clause; If the result is negative, collect the context by grouping fields that are not covered by the fields sorted by the storage engine, calculate the result of this group using context by logic, and continue processing the next group. If the result is yes, check if there is a csort clause; If the result indicates that the csort clause does not exist, calculate the value of each select column; Determine whether the data in the array has been sorted by the csort field in the storage engine; If the data in the array is already sorted by the csort field in the storage engine, calculate the value of each select column; Sort the data in the array according to the csort field; Calculate the value for each selected column; Determine if the keywords "top" or "limit" are present; If the result indicates that the keywords "top" or "limit" do not exist, merge the results of each group after processing. If the result indicates the presence of the keywords "top" or "limit", then process the "top" or "limit" keywords and retain the predefined row data.

6. An apparatus for improving the performance of database window function queries, characterized in that, include: The retrieval module is used to retrieve query statements; The determination module is used to determine the optimization type based on the query statement; An optimization module is used to optimize database query performance based on the optimization type. The optimization types include: result space pre-allocation optimization, optimization using existing sorting in the storage engine, and vectorized computation optimization; The optimization module includes: a fourth judgment unit, used to determine whether the queried select list does not contain aggregate functions, sequence correlation functions, and has no having clause when the optimization type is vectorized computation optimization; a sixth execution unit, used to calculate the result by context by logic when the judgment result is negative; wherein the context by logic includes result space pre-allocation optimization; and a fifth judgment unit, used to calculate the result according to context when the judgment result is positive. The system groups all possible values ​​of the field following the `by` keyword, obtains the sorted index of the original data, and determines whether the `csort` keyword is present. The seventh execution unit, if the `csort` keyword is not present, determines whether the `top` or `limit` keyword is present. The eighth execution unit, if the `csort` keyword is present, iterates through each group of data until the loop ends, and determines whether the processed result at the end of the loop contains the `top` or `limit` keyword. The ninth execution unit, if the `top` or `limit` keyword is not present, calculates the value of each `select` column using the processed index as a filter condition. The tenth execution unit, if the `top` or `limit` keyword is present, processes the `top` or `limit` keyword, retaining the index of each specified row in each group. The iterative processing of each group of data includes: within each group, sorting the sorted index of the original data according to the sorting method specified by `csort`.

7. The apparatus for improving database window function query performance according to claim 6, characterized in that, The optimization module includes: The first judgment unit is used to determine whether the query statement contains a preset keyword when the optimization type is the result space pre-allocation optimization. The first execution unit is used to calculate and return the result based on the context by logic if the judgment result is yes; The second execution unit is used to group data according to all possible combinations of values ​​of the field after the context by keyword if the judgment result is negative, and sort the data within each group according to the csort field; create an array with a length equal to the length of the input data; and process at least one of the created arrays in a loop.

8. The apparatus for improving database window function query performance according to claim 6, characterized in that, The optimization module includes: The second judgment unit is used to determine whether the data in the storage engine is ordered when the optimization type is the optimization that utilizes the existing sorting of the storage engine. The third execution unit is used to calculate and return the result through context by logic if the result is negative; The context by logic includes result space pre-allocation optimization; The third judgment unit is used to determine, if the judgment result is yes, whether there is any overlap between the field used by the storage engine for sorting and the context by grouping field; The fourth execution unit is used to perform logical calculations and return the result using context by when the sorting field and the context by grouping field have no intersection. The fifth execution unit is used to group data using the sorting information of the common field within the storage engine when the judgment result shows that there is an intersection between the sorting field and the context by grouping field; to process the grouped array in a loop; and to merge the results of each group after processing.

Images