Query statement optimization method, device, medium and product

Abstract

The application provides a query statement optimization method, device, medium and product. The query statement optimization method comprises the following steps: obtaining an original query statement, wherein the original query statement is a query statement comprising a CTE definition statement and a main query statement; detecting whether the original query statement meets a preset condition; if yes, extracting constant values of each subquery in the CTE definition statement and organizing the constant values into a constant sequence; removing the CTE definition part of the original query statement, replacing the equijoin condition of the main query statement with a membership determination condition based on the constant sequence, and obtaining an optimized query statement; and executing the optimized query statement. The optimizer no longer needs to perform a loop operation of subquery promotion on each subquery in the planning generation stage, which greatly reduces the redundant iteration overhead of the optimizer, shortens the generation time of the execution plan, and helps to improve the execution efficiency.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, device, medium, and product for optimizing query statements. Background Technology

[0002] Common Table Expression (CTE) is a syntax structure in Structured Query Language (SQL) that defines a named temporary result set through the WITH clause. This temporary result set is only valid during the execution of the current query statement and can be referenced once or multiple times in the current query statement.

[0003] In practical database applications, developers often use Concurrent Term Examples (CTEs) in conjunction with the UNION ALL set operator to construct lists of constant values. For example, when it is necessary to match a fixed set of constant values ​​in a query condition, a common operation is to: in the CTE definition statement, connect multiple SELECT constants FROM system virtual table subqueries using UNION ALL to form a temporary result set containing multiple rows of constant values; then in the main query statement, associate this temporary result set with the target data table using an equi-join condition to achieve data retrieval.

[0004] When the database executes the above query statement, it will attempt to improve the subquery for each subquery in the CTE definition statement. When there are many simple subqueries in the CTE definition statement, the optimizer needs to repeatedly execute the subquery improvement logic, which will seriously affect the performance of the generated plan. Summary of the Invention

[0005] One object of the present invention is to provide a method, device, medium, and product for optimizing query statements that can solve the problem of low plan generation efficiency caused by the optimizer performing subquery boosting operations one by one on a large number of simple subqueries in a CTE definition statement.

[0006] Specifically, the present invention provides a method for optimizing query statements, comprising: Obtain the original query statement, which is a query statement including the CTE definition statement and the main query statement; The system checks whether the original query statement meets preset conditions. The preset conditions are that the CTE definition statement of the original query statement defines an output column with a set column name. The output column is formed by connecting the query results of multiple subqueries through set operators. The source data objects of all subqueries are system virtual tables. The projection list of all subqueries is a constant expression and the constant expression is specified as the set column name. The query condition of the main query statement is to perform equality matching between the target column in the target data table and the output column. If so, extract the constant values ​​of each subquery in the CTE definition statement and organize them into a constant sequence; Remove the CTE definition part of the original query statement and replace the equi-join condition of the main query statement with a membership determination condition based on the constant sequence to obtain the optimized query statement; Execute the optimized query statement.

[0007] Optionally, the step of extracting the constant values ​​of each subquery in the CTE definition statement and organizing them into a constant sequence includes: Determine the optimization threshold; Check if the number of subqueries in the CTE definition statement is greater than the optimization threshold. If so, execute the step of extracting the constant value of each subquery in the CTE definition statement and organizing it into a constant sequence. If not, execute the original query statement.

[0008] Optionally, the step of determining the optimization threshold includes: Obtain the optimized threshold base value; Get the current number of concurrent queries and processor utilization in the database; The optimization threshold is determined based on the base value of the optimization threshold, the number of concurrent queries, and the processor utilization rate.

[0009] Optionally, the step of determining the optimization threshold based on the optimization threshold base value, the number of concurrent queries, and the processor utilization includes: The optimized threshold is calculated according to a preset formula; The preset formula is: T1 = T0 × (1 + i × (0.5 - f)); T1 is the optimized threshold, T0 is the base value of the optimized threshold, i is the preset damping coefficient, f is the load factor, and f = a × (C / Cm) + b × U; a and b are preset weighting coefficients, which add up to 1; C is the number of concurrent queries; Cm is the maximum number of concurrent queries allowed by the database; U is the processor utilization rate.

[0010] Optionally, after the statement is executed, the execution information of the statement is recorded, including the number of subqueries in the original query statement, whether it has been optimized, and the execution time. Based on the recorded optimization information, a preset number of first verification statements are obtained and a first average execution time is calculated. The first verification statements are historical statements whose ratio of the number of subqueries to the optimization threshold base value is within a set range and which have not been optimized. Based on the recorded optimization information, a preset number of second verification statements are obtained and a second average execution time is calculated. The second verification statement is a historical statement whose ratio of the number of subqueries to the optimization threshold base value is within a set range and has been optimized. The first average execution time and the second average execution time are compared, and the optimization threshold base value is adjusted according to the comparison result.

[0011] Optionally, the step of adjusting the optimized threshold base value based on the comparison result includes: Calculate the updated optimized threshold base value based on the base value adjustment formula; The base value adjustment formula is: Tn=T0×(1-k×gain; Tn is the updated optimized threshold base value, T0 is the current optimized threshold base value, k is the preset adjustment coefficient, and gain is the relative gain rate, gain=(t1-t2) / t1; t1 is the first average execution time, and t2 is the second average execution time.

[0012] Optionally, the preset adjustment coefficient is selected within a preset range based on the absolute value of the relative gain rate, and the value of the preset adjustment coefficient is positively correlated with the absolute value of the relative gain rate.

[0013] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a computer executable program stored in the memory and running on the processor, wherein the processor, when executing the computer executable program, implements an optimization method for a query statement according to any of the preceding claims.

[0014] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer-executable program is stored, which, when executed by a processor, implements the method for optimizing a query statement according to any of the preceding claims.

[0015] According to another aspect of the present invention, a computer program product is also provided, comprising a computer executable program that, when executed by a processor, implements the method for optimizing a query statement according to any of the preceding claims.

[0016] The query optimization method of this invention obtains the original query statement containing the CTE definition statement and the main query statement. If the original query statement meets preset conditions, it extracts the constant values ​​of each subquery in the CTE definition statement and organizes them into a constant sequence. Then, it removes the CTE definition part and replaces the equi-join conditions with membership judgment conditions based on the constant sequence, resulting in an optimized query statement, which is then executed. This approach allows multiple simple subqueries that would otherwise require the optimizer to iterate and attempt subquery improvement one by one to be identified and merged into a constant sequence during the parsing phase. This eliminates the need for the optimizer to perform loop operations on subquery improvement for each subquery during the plan generation phase, significantly reducing redundant iteration overhead and shortening the execution plan generation time, thus improving execution efficiency. Furthermore, by replacing the equi-join conditions in the main query statement with membership judgment conditions based on constant sequences, the original join operation is converted into a filtering operation. This allows the target data table to directly filter data based on the membership judgment conditions during the scanning phase, without performing additional join operations, further reducing the data processing volume during query execution. This improves the processing performance of the query statement from both the plan generation and execution levels.

[0017] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0018] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings: Figure 1 This is a schematic flowchart of a query statement optimization method according to an embodiment of the present invention; Figure 2 This is a partial schematic flowchart of a query statement optimization method according to another embodiment of the present invention; Figure 3 This is a schematic flowchart illustrating the determination of an optimization threshold in a query statement optimization method according to another embodiment of the present invention; Figure 4 This is a partial schematic flowchart of a query statement optimization method according to yet another embodiment of the present invention; Figure 5 This is a schematic diagram of a computer device according to an embodiment of the present invention; Figure 6 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; Figure 7This is a schematic diagram of a computer program product according to an embodiment of the present invention. Detailed Implementation

[0019] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0020] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0021] The flowcharts provided in this invention are not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.

[0022] like Figure 1 As shown, in one embodiment, the query optimization method generally includes: Step S101: Obtain the original query statement. The original query statement is a query statement that includes the CTE definition statement and the main query statement.

[0023] Specifically, a CTE (Common Table Expression) is a temporary named result set defined by the WITH clause and valid during the execution of the current query. A CTE definition statement begins with the WITH keyword, followed by the name of the CTE and an AS clause containing the query statement that defines the CTE. The main query statement is the query statement that follows the CTE definition statement and references the CTE. The CTE definition statement and the main query statement together constitute a complete SQL query statement.

[0024] For example, the following SQL statement is a query statement that includes a CTE definition statement and a main query statement: With cte as ( Select a as column from dual union all ... Select n as column from dual union all ) Select from t1, cte where t1.a=cte.column; The `with cte as()` part is the CTE definition statement, and `SELECT ...` is the SELECT statement. The part FROM t1, cte WHERE t1.a = cte.column is the main query statement.

[0025] Step S102: Check if the original query statement meets the preset conditions. If yes, proceed to step S103; otherwise, proceed to step S106. The preset conditions are: the CTE definition statement of the original query statement defines an output column with a set column name; the output column is formed by connecting the query results of multiple subqueries through set operators; the source data objects of all subqueries are system virtual tables; the projection list of all subqueries is a constant expression and the constant expression is specified as the set column name; and the query condition of the main query statement is to perform equality matching between the target column in the target data table and the output column.

[0026] System virtual tables are special tables in a database that are not associated with any physical storage media. They are only used to provide syntactic table references in query statements, such as the table "dual" in the previous example statement. System virtual tables typically contain only one row of data and are mainly used to query constant values ​​or expression results in SELECT statements.

[0027] The projection list refers to the list of expressions between the SELECT and FROM keywords in a subquery, used to define which columns the subquery returns. A constant expression is an expression whose value can be determined before the query is executed and does not depend on data in any physical table. In other words, it's data specified by the application. For example, "a" and "n" in the example statement above.

[0028] The `AS` keyword specifies the alias for the constant expressions in the projection list. For example, `AS column` in the previous example statement. All constant expressions in the projection lists of subqueries are assigned the same `AS column`, ensuring that the output columns formed after set operations have consistent names, making it easy for the main query to reference them. For example, referring to the main query in the previous example statement, the query condition is "t1.a=cte.column", which performs equality matching between the target column `t1.a` in the target data table `t1` and the output column `cte.column`.

[0029] Step S103: Extract the constant values ​​of each subquery in the CTE definition statement and organize them into a constant sequence.

[0030] Specifically, under preset conditions, the projection list of each subquery in the CTE definition statement is traversed, and the value of the constant expression is extracted from the projection list of each subquery. During extraction, the subqueries are extracted sequentially according to the connection order in the set operators, and all extracted constant values ​​are organized into a constant sequence according to this order. A constant sequence is a data structure containing a certain number of elements with a definite arrangement order, such as an array.

[0031] Step S104: Remove the CTE definition part of the original query statement and replace the equi-join condition of the main query statement with a membership determination condition based on a constant sequence to obtain the optimized query statement.

[0032] Specifically, the entire CTE definition section (i.e., everything from the WITH keyword to the beginning of the main query) is deleted from the original query statement. Then, the equi-join condition referencing the CTE output column in the main query statement is located and replaced with a membership condition. This membership condition determines whether each candidate value of the target column in the target data table belongs to the set of values ​​defined by the constant sequence. Specifically, it is in the form of IN plus the constant sequence. After the above replacement, the optimized query statement is obtained.

[0033] Referring to the example statement above, the replaced statement is: Select from t1 where t1.a in (a,…,n).

[0034] Step S105: Execute the optimized query statement. This means executing the query according to the optimized query statement.

[0035] Step S106: Execute the original query statement. That is, execute the original query statement.

[0036] In this embodiment, the original query statement, containing the CTE definition statement and the main query statement, is obtained. If the original query statement meets preset conditions, the constant values ​​of each subquery in the CTE definition statement are extracted and organized into a constant sequence. Then, the CTE definition part is removed, and the equi-join condition is replaced with a membership condition based on the constant sequence, resulting in an optimized query statement that is then executed. This approach allows multiple simple subqueries that would otherwise require the optimizer to iterate and attempt subquery improvement one by one to be identified and merged into a constant sequence during the parsing phase. This eliminates the need for the optimizer to perform a loop of subquery improvement on each subquery during the plan generation phase, significantly reducing redundant iteration overhead and shortening the execution plan generation time, thus improving execution efficiency. Furthermore, by replacing the equi-join condition in the main query statement with a membership condition based on a constant sequence, the original join operation is converted into a filtering operation. This allows the target data table to be directly filtered based on the membership condition during the scanning phase, without the need for additional join operations, further reducing the data processing volume during query execution. This improves the processing performance of the query statement from both the plan generation and execution levels.

[0037] like Figure 2 As shown, in one embodiment, the step of extracting the constant values ​​of each subquery in the CTE definition statement and organizing them into a constant sequence includes: Step S201: Determine the optimization threshold.

[0038] Step S202: Check if the number of subqueries in the CTE definition statement is greater than the optimization threshold. If yes, proceed to step S203; otherwise, proceed to step S204.

[0039] The optimization threshold is a positive integer value used to control the lower limit of the number of subqueries that trigger optimization. When the number of subqueries in the CTE definition statement is greater than this threshold, it is determined that the optimization benefit may outweigh the optimization cost, and optimization is triggered; when the number of subqueries is not greater than this threshold, it is determined that the optimization benefit is insufficient to offset the optimization cost, and optimization is abandoned.

[0040] Step S203: Extract the constant values ​​of each subquery in the CTE definition statement and organize them into a constant sequence. Subsequent operations refer to steps S104 and S105 in the previous embodiment.

[0041] Step S204: Execute the original query statement.

[0042] By determining the optimization threshold before optimization and checking whether the number of subqueries exceeds the optimization threshold, optimization operations are only performed when the number of subqueries exceeds the optimization threshold; otherwise, the original query statement is executed directly. This avoids unnecessary optimization operations in simple scenarios, which would generate additional parsing and memory overhead, and ensures that optimization decisions match the complexity of the query.

[0043] Reference Figure 3 As shown, in one embodiment, step S201, determining the optimization threshold includes: Step S301: Obtain the optimized threshold base value.

[0044] The optimization threshold base value is a preset baseline value that can be pre-set by the database administrator based on experience. Alternatively, the database administrator can pre-set an initial value based on experience and update it during use according to application conditions.

[0045] Step S302: Obtain the current number of concurrent queries and processor utilization in the database.

[0046] Concurrent query count refers to the number of query statements being executed concurrently in the database at any given time. Processor utilization refers to the percentage of the central processing unit (CPU) of the physical device hosting the database that is currently in use, with a value ranging from 0 to 1.

[0047] Step S303: Determine the optimization threshold based on the optimization threshold base value, the number of concurrent queries, and the processor utilization rate.

[0048] Specifically, in one embodiment, this step can calculate the optimization threshold according to a preset formula; The default formula is: T1 = T0 × (1 + i × (0.5 - f)); T1 is the optimization threshold; T0 is the base value of the optimization threshold; i is the preset damping coefficient, which is greater than 0 and less than 1, for example, it can be 0.3, 0.4 or 0.5; f is the load factor, f=a×(C / Cm)+b×U; a and b are preset weighting coefficients, which add up to 1; C is the number of concurrent queries; Cm is the maximum number of concurrent queries allowed by the database; U is the processor utilization rate.

[0049] By determining the optimization threshold based on the optimization threshold base value, the number of concurrent queries, and processor utilization, the optimization threshold can be adaptively adjusted according to the system load state. When the system is idle, the threshold can be appropriately increased to reduce unnecessary optimization attempts, and when the system is busy, the threshold can be appropriately decreased to more actively trigger optimization, thereby achieving better performance under different load conditions.

[0050] The significance of adopting a preset formula lies in that when the system load is in a normal state (that is, the load factor f=0.5), the optimization threshold is equal to the base value (T1=T0), and no adjustment is performed. When the system load is low (that is, f<0.5), there is sufficient performance to execute the original query statement, so the optimization threshold is set to be higher than the base value (T1>T0) to reduce the optimization triggering probability and avoid unnecessary optimization. When the system load is high (that is, f>0.5), the optimization threshold is lower than the base value (T1<T0) to increase the optimization triggering probability, so as to reduce the execution burden.

[0051] In addition, by setting a preset damping coefficient, the adjustment range can be reduced, and smooth and conservative adjustment can be performed, which helps avoid excessive uncertainty caused by excessive adjustment range.

[0052] It should be noted that in some other embodiments, no preset damping coefficient may be set.

[0053] As Figure 4 shown, in one embodiment, the query statement optimization method generally comprises: Step S401: after the execution of the statement is completed, record the execution information of the statement, wherein the execution information comprises the number of sub-queries of the original query statement, whether the statement has been optimized (that is, whether the operations of steps S103 to S104 in the foregoing embodiments have been performed) and the execution time.

[0054] Step S402: acquire a preset number of first check statements according to the recorded optimization information and calculate a first average execution time. The first check statements are historical statements in which the ratio of the number of sub-queries to the optimization threshold base value is within a set interval and have not been optimized.

[0055] Specifically, the first check statements are original query statements meeting preset conditions, and the ratio of the number of sub-queries in CTE definition statements of the first check statements to the current optimization threshold base value is within a set interval, for example, greater than or equal to 0.8 and less than or equal to 1.0. The statements have not been optimized during execution (that is, the optimization operations of steps S103 to S104 have not been triggered). The preset number can be set by a database administrator, such as 100, 200 or 500.

[0056] Step S403: acquire a preset number of second check statements according to the recorded optimization information and calculate a second average execution time. The second check statements are historical statements in which the ratio of the number of sub-queries to the optimization threshold base value is within a set interval and have been optimized.

[0057] Correspondingly, the conditions of the second check statements are substantially the same as those of the first check statements, with the difference that the second check statements have been optimized during execution, that is, the optimization operations of steps S103 to S104 have been triggered.

[0058] The number of the first and second verification statements obtained is the same.

[0059] Step S404: Compare the first average execution time and the second average execution time, and adjust the optimization threshold base value according to the comparison result.

[0060] Specifically, if the first average execution time is greater than the second average execution time, it indicates that the optimization is beneficial, and the base value of the optimization threshold can be appropriately reduced to increase the probability of triggering subsequent optimizations. If the first average execution time is less than the second average execution time, it indicates that the optimization is not beneficial, and the base value of the optimization threshold can be appropriately increased to decrease the probability of triggering subsequent optimizations.

[0061] By recording execution information after a statement is executed, including the number of subqueries, whether it has been optimized, and the execution time, and then obtaining optimized and unoptimized historical statements whose ratio of the number of subqueries to the optimization threshold base value is within the same set range based on the recorded execution information, the average execution time of the two groups of statements is calculated and compared. The optimization threshold base value is adjusted based on the comparison results. This allows for feedback correction of the optimization threshold base value using real execution data, enabling the optimization threshold base value to continuously converge towards a better direction and achieve better performance improvement.

[0062] In one implementation, step S404 includes: calculating the updated optimized threshold base value according to the base value adjustment formula. The base value adjustment formula is: Tn = T0 × (1 - k × gain).

[0063] Tn is the updated optimized threshold base value; T0 is the current optimized threshold base value; k is the preset adjustment coefficient, which is greater than 0 and less than 1.

[0064] gain is the relative gain ratio, gain=(t1-t2) / t1; t1 is the first average execution time, t2 is the second average execution time.

[0065] When gain is greater than 0, it indicates that the average execution time of the optimized statement is shorter than that of the unoptimized statement. Optimization has yielded significant benefits for statements with a number of subqueries near the current optimization threshold. In this case, Tn is less than T0, the optimization threshold decreases, and more queries can trigger optimization. When gain is less than 0, it indicates that the average execution time of the optimized statement is longer than that of the unoptimized statement. Optimization may have had negative benefits for statements with a number of subqueries near the current optimization threshold. In this case, Tn is greater than T0, the optimization threshold increases, and optimization triggers are reduced. When gain equals 0, it indicates that optimization has no significant benefit, and the optimization threshold remains essentially unchanged.

[0066] By calculating the updated optimized threshold base value according to the base value adjustment formula, the actual observed performance differences can be quantified as the basis for threshold adjustment, realizing the quantification and controllable closed-loop adjustment of the threshold base value.

[0067] Furthermore, the preset adjustment coefficient is selected within a preset range of 0 to 1 based on the absolute value of the relative gain, and the value of the preset adjustment coefficient is positively correlated with the absolute value of the relative gain.

[0068] For example, when the absolute value of the relative gain is less than 0.05, the preset adjustment coefficient is 0, and no adjustment is made. When the absolute value of the relative gain is greater than or equal to 0.05 but less than 0.15, the preset adjustment coefficient is 0.15. When the absolute value of the relative gain is greater than or equal to 0.15 but less than 0.3, the preset adjustment coefficient is 0.3. When the absolute value of the relative gain is greater than or equal to 0.3, the preset adjustment coefficient is 0.5.

[0069] Alternatively, the value of the preset adjustment coefficient is linearly proportional to the absolute value of the relative gain rate.

[0070] By selecting the preset adjustment coefficient within a preset range based on the absolute value of the relative gain, and by ensuring that the value of the preset adjustment coefficient is positively correlated with the absolute value of the relative gain, a larger adjustment step size can be used to accelerate the convergence speed when the optimization effect difference is large, and a smaller adjustment step size can be used to avoid threshold oscillation when the optimization effect difference is small. This achieves adaptive matching between the adjustment amplitude and the performance difference, and improves the stability and convergence efficiency of the threshold base value adjustment.

[0071] It should be noted that in some other embodiments, adjusting the optimization threshold base value based on the comparison results can also be done by directly adjusting the optimization threshold base value based on the difference between the first average execution time and the second average execution time. That is, the coefficient is directly selected from the range of coefficient values ​​based on the difference between the first average execution time and the second average execution time and the relative size of the first average execution time and the second average execution time, and the new optimization threshold base value is obtained by directly multiplying the coefficient by the optimization threshold base value.

[0072] This embodiment also provides a computer device and a computer-readable storage medium. Figure 5 This is a schematic diagram of a computer device 10 according to an embodiment of the present invention. Figure 6 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention.

[0073] The computer device 10 may include a memory 110, a processor 120, and a computer-executable program 11 stored on the memory 110 and running on the processor 120. When the processor 120 executes the computer-executable program 11, it implements the database query statement optimization method of any of the above embodiments.

[0074] The computer-readable storage medium 20 stores a computer-executable program 11 thereon, which, when executed by a processor, implements the method for optimizing the query statement of any of the above embodiments.

[0075] This embodiment also provides a computer program product. Figure 7 This is a schematic diagram of a computer program product 30 according to an embodiment of the present invention. The computer program product 30 includes a computer executable program 11, which, when executed by a processor 120, implements the optimization method for any of the query statements described above.

[0076] Specifically, the computer executable program 11 used to perform the operations of the present invention may be assembly instructions, instruction set architecture (ISA) instructions, computer instructions, computer-related instructions, microcode, firmware instructions, status setting data, or source code or object code written in any combination of one or more programming languages.

[0077] For the purposes of this embodiment, the computer-readable storage medium 20 can be any means capable of containing, storing, communicating, propagating, or transmitting a program for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable media include: an electrical connection having one or more wires (electronic device), a portable computer disk drive (magnetic device), random access memory (RAM), read-only memory (ROM), erasable and editable read-only memory (EPROM or flash memory), fiber optic devices, and portable optical disc read-only memory (CDROM). Furthermore, the computer-readable storage medium 20 can even be paper or other suitable media on which the program can be printed, since the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting, or otherwise processing as necessary, and then stored in a computer memory.

[0078] It should be understood that various parts of the present invention can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0079] Computer device 10 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 10 can be a cloud acquisition node. Computer device 10 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 10 can be implemented in a distributed cloud acquisition environment where tasks are performed by remote processing devices linked via a communication network. In a distributed cloud acquisition environment, program modules can reside on local or remote acquisition system storage media, including storage devices.

[0080] Computer device 10 may include a processor 120 adapted to execute stored instructions and a memory 110 that provides temporary storage space for the operation of said instructions during operation. Processor 120 may be a single-core processor, a multi-core processor, an acquisition cluster, or any other configuration. Memory 110 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0081] The processor 120 can be connected via a system interconnect (e.g., PCI, PCI-Express, etc.) to an I / O interface (input / output interface) suitable for connecting the computer device 10 to one or more I / O devices (input / output devices). I / O devices may include, for example, a keyboard and indicating devices, where indicating devices may include a touchpad or touchscreen, etc. I / O devices may be built into the computer device 10 or may be external devices connected to the acquisition device.

[0082] The processor 120 may also be linked via a system interconnect to a display interface suitable for connecting the computer device 10 to a display device. The display device may include a display screen that is a built-in component of the computer device 10. The display device may also include an external computer monitor, television, or projector connected to the computer device 10. Furthermore, a network interface controller (NIC) may be adapted to connect the computer device 10 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an Internet Minicomputer System Interface) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. Remote devices may connect to the computer device via the network.

[0083] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A method for optimizing a query statement, comprising: Obtain the original query statement, which is a query statement including the CTE definition statement and the main query statement; The system checks whether the original query statement meets preset conditions. The preset conditions are that the CTE definition statement of the original query statement defines an output column with a set column name. The output column is formed by connecting the query results of multiple subqueries through set operators. The source data objects of all subqueries are system virtual tables. The projection list of all subqueries is a constant expression and the constant expression is specified as the set column name. The query condition of the main query statement is to perform equality matching between the target column in the target data table and the output column. If so, extract the constant values ​​of each subquery in the CTE definition statement and organize them into a constant sequence; Remove the CTE definition part of the original query statement and replace the equi-join condition of the main query statement with a membership determination condition based on the constant sequence to obtain the optimized query statement; Execute the optimized query statement.

2. The query statement optimization method according to claim 1, wherein... Before the step of extracting the constant values ​​of each subquery in the CTE definition statement and organizing them into a constant sequence, the following steps are included: Determine the optimization threshold; Check if the number of subqueries in the CTE definition statement is greater than the optimization threshold. If so, execute the step of extracting the constant value of each subquery in the CTE definition statement and organizing it into a constant sequence. If not, execute the original query statement.

3. The query statement optimization method according to claim 2, wherein... The step of determining the optimization threshold includes: Obtain the optimized threshold base value; Get the current number of concurrent queries and processor utilization in the database; The optimization threshold is determined based on the base value of the optimization threshold, the number of concurrent queries, and the processor utilization rate.

4. The query statement optimization method according to claim 3, wherein... The step of determining the optimization threshold based on the optimization threshold base value, the number of concurrent queries, and the processor utilization rate includes: The optimized threshold is calculated according to a preset formula; The preset formula is: T1 = T0 × (1 + i × (0.5 - f)); T1 is the optimized threshold, T0 is the base value of the optimized threshold, i is the preset damping coefficient, f is the load factor, and f = a × (C / Cm) + b × U; a and b are preset weighting coefficients, which add up to 1; C is the number of concurrent queries; Cm is the maximum number of concurrent queries allowed by the database; U is the processor utilization rate.

5. The query statement optimization method according to claim 3, wherein... After the statement is executed, the execution information of the statement is recorded. The execution information includes the number of subqueries in the original query statement, whether it has been optimized, and the execution time. Based on the recorded optimization information, a preset number of first verification statements are obtained and a first average execution time is calculated. The first verification statements are historical statements whose ratio of the number of subqueries to the optimization threshold base value is within a set range and which have not been optimized. Based on the recorded optimization information, a preset number of second verification statements are obtained and a second average execution time is calculated. The second verification statement is a historical statement whose ratio of the number of subqueries to the optimization threshold base value is within a set range and has been optimized. The first average execution time and the second average execution time are compared, and the optimization threshold base value is adjusted according to the comparison result.

6. The query statement optimization method according to claim 5, wherein... The step of adjusting the optimized threshold base value based on the comparison result includes: Calculate the updated optimized threshold base value based on the base value adjustment formula; The base value adjustment formula is: Tn=T0×(1-k×gain; Tn is the updated optimized threshold base value, T0 is the current optimized threshold base value, k is the preset adjustment coefficient, and gain is the relative gain rate, gain=(t1-t2) / t1; t1 is the first average execution time, and t2 is the second average execution time.

7. The query statement optimization method according to claim 6, wherein, The preset adjustment coefficient is selected within a preset range based on the absolute value of the relative gain rate, and the value of the preset adjustment coefficient is positively correlated with the absolute value of the relative gain rate.

8. A computer device comprising a memory, a processor, and a computer-executable program stored in the memory and running on the processor, wherein the processor, when executing the computer-executable program, implements the method for optimizing a query statement according to any one of claims 1 to 7.

9. A computer-readable storage medium having a computer-executable program stored thereon, the computer-executable program, when executed by a processor, implementing the method for optimizing a query statement according to any one of claims 1 to 7.

10. A computer program product comprising a computer executable program, wherein the computer executable program, when executed by a processor, implements the method for optimizing a query statement according to any one of claims 1 to 7.

Images