An optimization method for traffic merging and computation merging for SQL tasks
By merging calculation rules and traffic merging for homologous real-time SQL tasks, the problem of increasing load on the server side of the real-time SQL engine is solved, efficient utilization of computing resources and bandwidth is achieved, and the processing capability of real-time SQL tasks is significantly improved.
Patent Information
- Application Number
- CN202410394444.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-04-02
- Publication Date
- 2024-11-29
- Estimated Expiration
- 2044-04-02
AI Technical Summary
As the amount of data grows, the server-side load of the real-time SQL engine increases, and the execution performance of a single SQL task is close to a bottleneck. How to improve the multi-task merging capability to improve overall performance is a key issue.
By combining, parsing and converting expressions in SQL statements based on atomic computing rule types for multiple real-time SQL tasks that are homologous and have similar computing rules, compute using a new range expression calculation table, merge multiple real-time SQL Task, using only 1 computing resource, realizes the merger of traffic and computing.
It realizes efficient utilization of computing resources and bandwidth of multiple real-time SQL tasks, supports multiple improvements in the number of real-time SQL tasks, reduces traffic consumption, and improves the overall performance of the server.
Smart Images

Figure CN118377586B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of big data technology, and in particular to an optimization method for performing traffic merging and calculation merging for SQL tasks. Background Art
[0002] With the improvement of computer storage capacity and the development of complex algorithms, the amount of data has grown exponentially in recent years. These trends have led to rapid development of science and technology and disruptive changes in business models.
[0003] The real-time SQL engine is usually a distributed data processing engine. It uses distributed computing resources in the form of SQL interfaces to execute real-time computing tasks. The real-time SQL engine consists of two parts: the server and the client, and is mostly a CS architecture. The server includes sub-modules such as Manager (management), Scheduler (scheduling), Optimizer (optimization), and Executor (execution). Usually a server will run multiple real-time SQL tasks for multiple users. As the total number of SQL tasks increases, the server load is also getting higher and higher. The execution performance of a single SQL task is currently close to the performance bottleneck. How to improve the ability to merge multiple tasks is the key to further improving the overall performance of the server.
[0004] Therefore, in order to improve the overall data operation performance of the Server, an optimization method for traffic merging and calculation merging for SQL tasks is needed. Summary of the invention
[0005] The purpose of the present invention is to provide an optimization method for traffic merging and computing merging for SQL tasks. The present invention realizes effective merging of computing processes for multiple real-time SQL tasks of the same source and with similar computing rules. Through merging based on atomic computing rule types, it is finally achieved that for multiple real-time SQL tasks, multiple computing resources need to be used before merging, and only one computing resource needs to be used after merging. For servers configured with the same computing resources, considering only computing resources, it is possible to achieve a multiple increase in the number of real-time SQL tasks supported.
[0006] Effective traffic merging in stream computing scenarios is achieved. After the data sources of the same-source SQL tasks are merged, the consumption traffic of multiple real-time SQL tasks is reduced from multiple to one after merging. The number of real-time SQL tasks that can be supported by a unit bandwidth can be increased by several times year-on-year. That is, considering only the bandwidth, a server with the same bandwidth resources can support multiple times the original real-time SQL tasks.
[0007] The present invention is implemented in this way. The present invention provides an optimization method for traffic merging and calculation merging for SQL tasks, which is specifically performed in the following steps:
[0008] S1: The client submits multiple tasks at a time, and the server executes the multiple tasks submitted by the client;
[0009] S2: On the server side, multiple real-time SQL tasks of the same type are parsed to obtain the annotation structure of Source, Rule, and Sink, and then the Source part is judged;
[0010] Parse the SQL statements, input and output information and optimization options through Json syntax parsing or SQL syntax parsing Rule parameters; the information of multiple real-time SQL statements parsed is in SELECT WHERE format, and the expressions contained in the WHERE condition part of multiple real-time SQL task statements are all expressions that can be optimized. Convert logical expressions containing and and or into multiple logical expressions containing only and;
[0011] The range expression is extracted and analyzed, and a calculation table of the range expression is drawn; multiple real-time SQLs are merged, and the new range expression calculation table is used for actual calculation. According to the expression range of each real-time SQL, a corresponding mark is made on the range list of the summarized X to obtain the range calculation table
[0012] S3: Determine whether the topics of multiple real-time SQL sources are the same;
[0013] S4: If yes, multiple SQL tasks are merged. When merging multiple SQL tasks, the specific step is to modify the Rule of each real-time SQL and use the calculation table of the new range expression to perform actual calculations. The specific steps are as follows:
[0014] S 4.1 : Get the input object, the data object includes three columns of data, X, Y and Z;
[0015] S 4.2 : Match the values of the corresponding columns in the data object with the calculation table to obtain a list of ranges in the calculation table that are hit;
[0016] S 4.3 : Find the range expression corresponding to the hit range list, and correspond to the hit real-time SQL;
[0017] S 4.4 : The calculation result is obtained, and the data object hits the "first real-time SQL" and "second real-time SQL" in the range condition matching calculation;
[0018] S 4.5 : Merge the first real-time SQL task and the second real-time SQL task that are hit.
[0019] Furthermore, the present invention provides a computer-readable storage medium, wherein the storage medium stores a computer program, and when the computer program is executed by a main controller, the method described in any one of the above is implemented.
[0020] Compared with the prior art, the present invention has the following beneficial effects:
[0021] 1. Achieve efficient merging of specific computing scenarios, that is, to achieve effective merging of the computing processes of multiple real-time SQL tasks with the same source and similar computing rules. Through merging based on atomic computing rule types, it is finally possible to achieve that for multiple real-time SQL tasks, multiple computing resources are required before merging, but only one computing resource is required after merging. For servers configured with the same computing resources, the number of supported real-time SQL tasks can be greatly increased when only computing resources are considered.
[0022] 2. Effective traffic merging is achieved in stream computing scenarios. After the data sources of the same-source SQL tasks are merged, the traffic consumption of multiple real-time SQL tasks is reduced from multiple to one after merging. The number of real-time SQL tasks that can be supported by a unit bandwidth can be increased by several times year-on-year. That is, considering only the bandwidth, a server with the same bandwidth resources can support multiple times the number of real-time SQL tasks. BRIEF DESCRIPTION OF THE DRAWINGS
[0023] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required for use in the embodiments will be briefly introduced below. It is understood that the following drawings only show certain embodiments of the present invention and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without creative work.
[0024] Figure 1 is a flow chart of the method of the present invention;
[0025] Figure 2 This is a screenshot of the first Rule merge code of the present invention;
[0026] Figure 3 This is a screenshot of the second Rule merge code of the present invention;
[0027] Figure 4 This is a screenshot of the third Rule merge code of the present invention;
[0028] Figure 5 This is a screenshot of the fourth Rule merge code of the present invention;
[0029] Figure 6 It is a plurality of real-time SQL task graphs without traffic merging of the present invention;
[0030] Figure 7 It is a plurality of real-time SQL task graphs after traffic merging of the present invention;
[0031] Figure 8 It is a plurality of real-time SQL task graphs before the calculation merging of the present invention;
[0032] Fig. 9 It is a plurality of real-time SQL task graphs after calculation and merging of the present invention;
[0033] Fig.10 It is the present invention to obtain the full and expression diagram;
[0034] Fig.11 It is a calculation table of the range expression of the present invention. DETAILED DESCRIPTION
[0035] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work belong to the scope of protection of the present invention. Therefore, the following detailed description of the embodiments of the present invention provided in the drawings is not intended to limit the scope of the invention claimed for protection, but is only for selected embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work belong to the scope of protection of the present invention.
[0036] See also Figure 1-11 The present invention provides an optimization method for traffic merging and calculation merging for SQL tasks, which is specifically performed in the following steps:
[0037] Please follow the steps below:
[0038] S1: Submit multiple tasks at a time on the client side, and the server side executes the multiple tasks submitted by the client;
[0039] S2: On the server side, multiple real-time SQL tasks of the same type are parsed to obtain the annotation structure of Source, Rule, and Sink, and then the Source part is judged;
[0040] Parse the SQL statements, input and output information and optimization options through Json syntax parsing or SQL syntax parsing Rule parameters; the information of multiple real-time SQL statements parsed is in SELECT WHERE format, and the expressions contained in the WHERE condition part of multiple real-time SQL task statements are all expressions that can be optimized. Convert logical expressions containing and and or into multiple logical expressions containing only and;
[0041] The range expression is extracted and analyzed, and a calculation table of the range expression is drawn; multiple real-time SQLs are merged, and the new range expression calculation table is used for actual calculation. According to the expression range of each real-time SQL, a corresponding mark is made on the range list of the summarized X to obtain the range calculation table
[0042] S3: Determine whether the topics of multiple real-time SQL sources are the same;
[0043] S4: If yes, multiple SQL tasks are merged. When merging multiple SQL tasks, the specific step is to modify the Rule of each real-time SQL and use the calculation table of the new range expression to perform actual calculations. The specific steps are as follows:
[0044] S 4.1 : Get the input object, the data object includes three columns of data, X, Y and Z, (x=10, y=10, z=10);
[0045] In this embodiment, an expression of a range type is intercepted from each real-time SQL. The following are the interception results of 5 real-time SQLs; as shown in Table 1;
[0046] Table 1 The interception results of 5 real-time SQL
[0047]
[0048]
[0049] S 4.2 : Match the value of the corresponding column in the data object, here x, with the calculation table, and obtain the range list in the calculation table where the x value hits; here x=10, the matching range list is: x<80;
[0050] In this embodiment, the boundary points of these range expressions on X are combined to draw a range list of X containing all boundary points; as shown in Table 2;
[0051] Table 2 List of X ranges containing all boundary points
[0052]
[0053] S 4.3 : Find the range expression corresponding to the hit range list, and correspond to the hit real-time SQL; here x<80 corresponds to two range expressions, "x<80" and "x<1000", which come from "Real-time SQL1" and "Real-time SQL2" respectively;
[0054] In this embodiment, according to the expression range of each real-time SQL, a corresponding mark is made on the summarized range list of X to obtain a range calculation table, as shown in Table 3;
[0055] Table 3 Range calculation table
[0056]
[0057]
[0058] S 4.4 : The calculation result shows that the data object (x=10, y=10, z=10) hits "Real-time SQL1" and "Real-time SQL2" in the range condition matching calculation;
[0059] S 4.5 : Merge the first real-time SQL1 and the second real-time SQL2 tasks that are hit.
[0060] In this embodiment, the present invention provides a computer-readable storage medium, wherein the storage medium stores a computer program, and when the computer program is executed by a main controller, the method described in any one of the above is implemented.
[0061] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention has various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.
Claims
1. An optimization method for traffic merging and computation merging for SQL tasks. Features: Please follow the steps below: S 1 : Submit multiple tasks at a time on the Client side, and the Server side executes the multiple tasks submitted by the Client; S 2 : On the server side, for multiple real-time SQL tasks of the same type, parse the Source, Rule, and Sink annotation structures, and then judge the Source part; Parse the SQL statements, input and output information and optimization options through Json syntax parsing or SQL syntax parsing Rule parameters; the information of multiple real-time SQL statements parsed is in SELECT WHERE format, and the expressions contained in the WHERE condition part of multiple real-time SQL task statements are all expressions that can be optimized; Convert logical expressions containing and and or into multiple logical expressions containing only and; And extract and analyze the range expression, and draw a calculation table of the range expression; Merge multiple real-time SQL statements and use the calculation table of the new range expression to perform actual calculations; According to the expression range of each real-time SQL, a corresponding mark is made on the summarized range list of X to obtain a range calculation table; S 3 : Determine whether the topics of multiple real-time SQL sources are the same; S 4 : If yes, merge multiple SQL tasks; specifically, modify the Rule of each real-time SQL and use the calculation table of the new range expression to perform actual calculations; specifically, follow the steps below: S 4.1 : Get the input object; S 4.2 : Match the values of the corresponding columns in the data object with the calculation table to obtain a list of ranges in the calculation table that are hit; S 4.3 : Find the range expression corresponding to the hit range list, and correspond to the hit real-time SQL; S 4.4 : Obtain the calculation result, the SQL task that the data object hits in the range condition matching calculation; S 4.5 : Merge each hit SQL task.
2. According to claim 1, an optimization method for traffic merging and computing merging for SQL tasks, It is characterized in that The data object consists of three columns of data, X, Y, and Z.
3. A computer-readable storage medium storing a computer program, It is characterized in that When the computer program is executed by the main controller, the method according to any one of claims 1 to 2 is implemented.
Citation Information
Patent Citations
Data query method and device, computer system and medium
CN111597214A
SQL optimization query method and device
CN112286961A
