Sql task chain link optimization method, device, equipment, storage medium and program product

Abstract

A SQL task link optimization method, device, equipment, storage medium and program product are disclosed, task related data of a SQL task is acquired, a dependency link between SQL tasks is filtered based on the task related data, wherein the dependency link is used to represent an association relationship between the SQL tasks, a directed graph network of the SQL tasks is constructed based on the filtered dependency link, the directed graph network is divided into at least two connected subgraphs, a SQL task in each connected subgraph is subjected to a merging feasibility test, a task merging decision is generated based on a test result, and a link of the SQL task is restructured and optimized according to the task merging decision. By adopting the embodiment of the present application, an execution exception of a SQL task can be captured in real time, system failure caused by problem accumulation can be avoided, and the management requirement of a high concurrency and large data volume SQL task link can be adapted.

Description

Technical Field

[0001] This invention relates to the field of big data, and in particular to a method, apparatus, device, storage medium, and program product for optimizing SQL task chains. Background Technology

[0002] SQL (Structured Query Language) tasks refer to various data processing tasks, such as data calculation, storage, and analysis, performed by enterprise backends based on SQL statements. They are a crucial link in big data business operations, and the dependencies between tasks constitute the SQL task chain. With the advancement of global digital transformation, the iteration of enterprise information systems is accelerating, business volume and service complexity are increasing significantly, and data volume is exploding. The number of SQL tasks is surging, and dependencies are becoming increasingly complex. Efficient management and optimization of task chains have become key to enterprise IT infrastructure construction. Existing SQL task chain optimization primarily relies on slow query log analysis. This solution uses the database's built-in slow query log function to periodically screen for inefficient SQL statements, identify performance bottlenecks, and then optimize the statement structure or adjust system configuration to improve execution efficiency. However, existing solutions rely on a periodic review model of static logs for analysis, which cannot capture SQL task execution anomalies in real time. Furthermore, the time lag between anomaly discovery and manual analysis and handling can easily lead to the continuous accumulation of problems, making it impossible to avoid risks in a timely manner and ultimately causing system failures. This approach is ill-suited to the SQL task chain management needs under high concurrency and large data volumes. Summary of the Invention

[0003] The purpose of this invention is to provide a method, apparatus, device, storage medium, and program product for optimizing SQL task chains, which can capture execution anomalies of SQL tasks in real time, avoid the accumulation of problems that may lead to system failures, and adapt to the needs of SQL task chain management with high concurrency and large data volume.

[0004] To achieve the above objectives, embodiments of the present invention provide an SQL task chain optimization method, comprising: Retrieve task-related data for SQL tasks; The dependency links between SQL tasks are filtered based on the task-related data; wherein, the dependency links are used to characterize the association relationships between the SQL tasks. A directed graph network for SQL tasks is constructed based on the filtered dependency links, and the directed graph network is divided into at least two connected subgraphs. Perform a feasibility check on the SQL tasks in each connected subgraph, and generate a task merging decision based on the check results; The link of the SQL task is optimized based on the task merging decision.

[0005] As an improvement to the above scheme, the task-related data includes at least one of the following: attribute data of the SQL task, dependency link data between tasks, and runtime data of task instances.

[0006] As an improvement to the above solution, the filtering of dependency links between SQL tasks based on the task-related data includes at least one of the following data filtering methods: Deleting cross-cycle dependency links using an adaptive dynamic algorithm; Deleting timed-out dependency links based on timestamp comparison technology; Dependency links that do not meet the optimization criteria are filtered based on user-defined rules.

[0007] As an improvement to the above scheme, the construction of the directed graph network for the SQL task based on the filtered dependency links includes: Obtain the node characteristics of the SQL tasks in the filtered dependency chain; Based on the node characteristics and the relationship between SQL tasks, an incremental graph update algorithm is used to construct a directed graph network for SQL tasks.

[0008] As an improvement to the above scheme, the step of performing a feasibility check on the SQL tasks in each connected subgraph and generating a task merging decision based on the check results includes: A recursive algorithm is used to select at least two adjacent SQL tasks in the connected subgraph as tasks to be merged, and it is detected whether the tasks to be merged satisfy a preset circular dependency relationship. If the circular dependency relationship is not satisfied, the total time required to merge the tasks to be merged is evaluated based on historical execution data. If the total time consumed does not exceed a preset threshold, the maintainability score of the code after merging the tasks to be merged is quantified. If the maintainability score reaches a preset health threshold, a merging decision is generated for the tasks to be merged.

[0009] As an improvement to the above solution, the optimization of the SQL task chain based on the task merging decision includes: A new task node is created based on the task merging decision; wherein the new task node uses the configuration information of the task to be merged; The upstream dependencies of the tasks to be merged are mapped to the new task node, the tasks to be merged are marked as paused, and the redundant call links corresponding to the tasks to be merged are cut off.

[0010] To achieve the above objectives, embodiments of the present invention also provide an SQL task chain optimization device, comprising: The data acquisition module is used to obtain task-related data for SQL tasks; A data filtering module is used to filter the dependency links between SQL tasks based on the task-related data; wherein, the dependency links are used to characterize the association relationships between the SQL tasks. A network construction module is used to construct a directed graph network for SQL tasks based on filtered dependency links, and to divide the directed graph network into at least two connected subgraphs. The merge verification module is used to perform a merge feasibility verification on the SQL tasks in each connected subgraph and generate a task merge decision based on the verification results. An optimization execution module is used to optimize the chain of the SQL task based on the task merging decision.

[0011] To achieve the above objectives, embodiments of the present invention also provide an SQL task link optimization device, including a processor, a memory, and a computer program stored in the memory and configured to be executed by the processor. When the processor executes the computer program, it implements the SQL task link optimization method as described in any of the above embodiments.

[0012] To achieve the above objectives, embodiments of the present invention also provide a computer-readable storage medium, the computer-readable storage medium including a stored computer program, wherein, when the computer program is executed, it controls the device where the computer-readable storage medium is located to execute the SQL task chain optimization method as described in any of the above embodiments.

[0013] To achieve the above objectives, embodiments of the present invention also provide a computer program product, including computer instructions, which, when executed by a processor, implement the SQL task chain optimization method as described in any of the above embodiments.

[0014] Compared to existing technologies, the SQL task chain optimization method, apparatus, device, storage medium, and program product disclosed in this invention first acquires all SQL task-related data, then specifically filters inter-task dependency chains to eliminate invalid dependencies from the data source. Combined with the filtered valid dependency chains, a directed graph network is constructed and connected subgraphs are partitioned, allowing complex SQL task dependencies to form a structured, modular, and visual network, achieving a global review and analysis of task dependencies. Simultaneously, the feasibility of merging SQL tasks within each connected subgraph is checked, and a merging decision is generated, thereby completing the chain optimization. The path optimization fully automates link analysis, verification, and reconstruction through algorithms, abandoning the existing model of relying on periodic reviews of static logs. It eliminates the need for manual intervention in analysis and processing, enabling real-time detection and handling of SQL task execution anomalies, eliminating the time lag between anomaly detection and handling, preventing the accumulation of problems that could lead to system failures, and freeing users from dependence on professional human teams. Furthermore, it simplifies redundant links and improves task execution efficiency through subgraph partitioning and task merging, effectively adapting to the SQL task link management needs under high concurrency and large data volumes, and improving the efficiency, stability, and flexibility of SQL task link scheduling. Attached Figure Description

[0015] Figure 1 This is a flowchart of an SQL task chain optimization method provided in an embodiment of the present invention; Figure 2 This is a comparison chart of the SQL task chain before and after optimization provided in an embodiment of the present invention; Figure 3 This is a schematic diagram of the SQL task chain optimization process provided in an embodiment of the present invention; Figure 4 This is a structural block diagram of an SQL task link optimization device provided in an embodiment of the present invention; Figure 5 This is a structural block diagram of an SQL task link optimization device provided in an embodiment of the present invention. Detailed Implementation

[0016] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. 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 are within the scope of protection of the present invention.

[0017] Figure 1 This is a flowchart of an SQL task link optimization method provided by an embodiment of the present invention, which includes steps S1 to S5.

[0018] S1. Obtain task-related data for the SQL task; wherein, the task-related data includes at least one of the following: attribute data of the SQL task, dependency chain data between tasks, and runtime data of the task instance.

[0019] For example, the attribute data refers to information used to characterize the basic features of an SQL task, such as at least task ID, tenant identifier, execution cycle, task type, and task priority. The execution cycle refers to the preset running time pattern of the SQL task, and the task type refers to the category to which the SQL task belongs based on the business scenario, such as data cleaning, feature calculation, tag storage, and billing / accounting. This attribute data dynamically captures the basic attributes of the task when the SQL task is submitted or its configuration is updated using real-time data streaming technology, abandoning the traditional static collection mode. It also supplements and expands the task type and priority information by associating with a metadata table, enriching the attribute dimensions. Furthermore, it combines database views and pattern recognition technology to achieve second-level data updates in scenarios where task attributes change frequently. This solves the data lag problem caused by traditional timed batch query methods from the source of data collection, ensuring the timeliness and completeness of the attribute data.

[0020] For example, the dependency chain data refers to information used to characterize the execution relationships between SQL tasks, specifically the pre- and post-dependencies between SQL tasks. This means that the execution of one SQL task depends on the completion of another SQL task, or a chained execution relationship formed between multiple SQL tasks. This dependency chain data utilizes Kafka to monitor the entire SQL task execution log in real time, parsing the log data to extract the dependencies between tasks. Based on the parsing results, a dependency graph capable of being updated in seconds is constructed. Simultaneously, a transaction mechanism is introduced to ensure the atomicity of dependency updates, guaranteeing the integrity of adding, modifying, and deleting dependencies. Compared to traditional analysis methods based on historical log backtracking, this reduces dependency capture latency from minutes to seconds, effectively compressing dependency capture delays and avoiding dependency omissions and mismatches caused by dynamic task changes due to business adjustments, thus ensuring the accuracy and real-time performance of the dependency chain data.

[0021] For example, the task instance refers to a specific execution instance generated when an SQL task runs in a real production environment. A single SQL task can generate multiple different task instances based on business needs. For instance, a data cleaning SQL task can generate daily, weekly, and monthly data cleaning instances based on daily, weekly, and monthly business statistical needs, respectively, to perform cleaning operations on the business data for the corresponding period. The runtime data refers to various status and resource consumption information generated during the entire lifecycle of the SQL task instance. This includes at least the task instance's start time, end time, and CPU (Central Processing Unit) / memory consumption data. This runtime data is collected non-intrusively at the millisecond level using lightweight data tracking technology. This meets the system's performance requirements in high-concurrency, high-data-volume scenarios, avoids data collection from impacting resource consumption and performance on normal task execution, and provides a comprehensive raw data baseline for subsequent resource scheduling optimization, task anomaly detection, and runtime status evaluation.

[0022] S2. Filter the dependency links between SQL tasks based on the task-related data; wherein, the dependency links are used to characterize the association between the SQL tasks.

[0023] For example, the collected SQL task-related data may contain various invalid dependency links. These links have no actual business execution value, which not only increases the computational load of subsequent graph network construction and task merging verification, causing a waste of system resources, but also interferes with the analysis and optimization decisions of SQL task links. They may even lead to task scheduling conflicts and low execution efficiency due to invalid dependencies. Therefore, it is necessary to filter dependency links based on task-related data, identify and eliminate various invalid links, and retain only valid dependency links that meet the business logic and optimization conditions. This will purify the dataset, improve the accuracy and overall efficiency of subsequent link optimization processing, and lay a high-quality data foundation for building a directed graph network for SQL tasks.

[0024] Furthermore, the present invention includes at least one of the following data filtering methods: 1) Remove cross-cycle dependency links using an adaptive dynamic algorithm; For example, the execution cycle attribute data of each SQL task is extracted from the task-related data, including different cycle types such as daily tasks, weekly tasks, monthly tasks, and quarterly tasks. Then, an adaptive dynamic algorithm is used to analyze the cycle matching degree of two or more SQL tasks with dependencies to determine whether the cycle association between tasks conforms to the business execution logic. If an unreasonable cross-cycle dependency link is identified, such as a daily statistics SQL task depending on a monthly summary SQL task, or a weekly settlement SQL task depending on a quarterly accounting SQL task, this type of cross-cycle dependency will cause downstream tasks to wait for a long time due to the excessively long execution cycle of upstream tasks, resulting in idle resources and waste. The system will automatically remove this type of cross-cycle dependency link from the task dependency network and only retain cross-cycle dependency links with the same cycle or that conform to the business logic.

[0025] 2) Delete timed-out dependency links based on timestamp comparison technology; For example, timestamp data for each SQL task and its corresponding dependency chain is extracted from task-related data, including the task's execution cycle timestamp, the dependency chain's generation timestamp, and the task's preset effective execution window. After the SQL task chain is generated, timestamp comparison technology is used to compare the dependency timestamps of downstream tasks with the execution timestamps of upstream tasks. Simultaneously, the preset time window is used to determine whether the dependency chain is within its valid time limit. If a timeout dependency chain is detected, such as the bill calculation SQL task for period T depending on the raw data collection SQL task for period T-2, the goal is to calculate... To calculate billing data such as communication consumption and package usage for users in period T, only valid raw data for period T provided by the raw data collection task of period T is required. The raw data for period T-2 is consumption records from an earlier period, and the billing for the corresponding period has already been completed. It cannot be used for billing calculation in period T. If this overdue data is relied upon, it will lead to errors in the billing data of period T and a disconnect from actual business. The upstream task data corresponding to such timed dependency links has no practical value. The system will automatically prune such timed dependency links and automatically filter invalid timed links through a set time window.

[0026] 3) Filter dependency links that do not meet the optimization conditions based on user-defined rules.

[0027] For example, a custom rule configuration interface is first provided for different tenants and different business scenarios. Users can set optimization filtering rules for dependency links in the system according to their own business needs, tenant preferences, task type matching requirements, etc. Then, relevant feature data such as tenant identifier, task type, task priority, and business affiliation are extracted from the task-related data of the SQL task. When submitting an SQL task link optimization request, the system will dynamically load the user's preset custom rule parameters and match and verify the task-related data with the custom rules one by one. If a non-optimized dependency link that meets the filtering rules is detected, such as setting "low-priority SQL tasks must not depend on high-priority business SQL tasks" for a specific tenant, or setting "non-billing SQL tasks must not depend on billing and accounting SQL tasks" for a billing scenario, the system will automatically filter out such dependency links that do not meet the optimization conditions, thus meeting the differentiated link optimization needs of multi-tenant and multi-business scenarios.

[0028] In this embodiment of the invention, three targeted data filtering methods—adaptive dynamic algorithms, timestamp comparison technology, and user-defined rules—are used to perform multi-dimensional screening of dependency links between SQL tasks. This can identify and eliminate various invalid dependency links such as those spanning cycles or timeouts, while also filtering out customized invalid links that do not meet business optimization requirements. This effectively purifies the task dependency link dataset, not only eliminating link data with no actual execution value and reducing the amount of invalid computation in subsequent graph network construction and task optimization, thus improving the accuracy and overall efficiency of subsequent processing, but also retaining valid dependency link information that meets optimization conditions, laying a data foundation for the subsequent construction of a structured directed graph network for SQL tasks.

[0029] S3. Construct a directed graph network for the SQL task based on the filtered dependency links, and divide the directed graph network into at least two connected subgraphs.

[0030] For example, the directed graph network refers to a structured network model that maps the logical relationships between SQL tasks, with SQL tasks as independent nodes and filtered pre- and post-dependencies between tasks as directed edges. The direction of the edges represents the execution order of the tasks, providing a clear and comprehensive view of the dependency topology of the entire SQL task system. The connected subgraph refers to a subset of the task network extracted from the overall directed graph network, where internal nodes have direct or indirect dependencies and are relatively independent of the external subgraph. Each connected subgraph is a highly cohesive modular task processing unit, enabling independent analysis and optimization.

[0031] For example, the partitioning of connected subgraphs is implemented based on the Louvain community detection technique. This algorithm, based on the tightness of the dependencies between task nodes, automatically identifies and aggregates SQL task nodes with high internal dependencies and strong business logic connections to form a highly cohesive connected subgraph by iteratively calculating the modularity of nodes in the task network. For instance, task A is a user behavior data cleaning task, task B is a consumer feature extraction task, and task C is a basic label calculation task. Task B requires the output of task A as input, and task C depends on the execution result of task B. The three form a strong execution dependency and belong to the business link of user label generation, with extremely high density of dependency edges between nodes. Task D is a label data backup task, and task E is a label push task, which only depends on the result of task C and has a weak direct connection with A, B, and C. The Louvain community detection technique, through iterative calculation of modularity, will identify the high aggregation of nodes A, B, and C and automatically aggregate them into a highly cohesive connected subgraph, while D and E are aggregated into another independent highly cohesive connected subgraph based on their dependencies and business logic. The connected subgraphs after partitioning can be analyzed and optimized in parallel. Compared with the traditional manual partitioning mode, it does not require a lot of manpower to sort out the task associations and divide the processing units, reducing the investment of manpower and system resources, while improving the efficiency and accuracy of subgraph partitioning, and adapting to the link optimization needs under massive SQL tasks and complex dependencies.

[0032] Further, in step S3, the construction of the directed graph network of the SQL task based on the filtered dependency links includes: obtaining the node features of the SQL task in the filtered dependency links; and constructing the directed graph network of the SQL task using an incremental graph update algorithm based on the node features and the association between the SQL tasks.

[0033] For example, full-dimensional node feature extraction is performed on all SQL tasks involved in the filtered effective dependency links. The extracted features include attribute features such as task ID, task name, and execution cycle. At the same time, each SQL task node is assigned multi-layer feature labels, giving each node clear and rich feature dimensions, improving the expressiveness and analysis accuracy of the subsequent graph network, and laying the feature foundation for building the directed graph network. Then, the dynamic directed dependency graph construction unit integrates the extracted features of each SQL task node with the task associations represented by the filtered effective dependency links. The directed graph network is constructed and maintained in real time based on the incremental graph update algorithm. After the data filtering is completed, the basic directed graph network is built first, mapping each SQL task to a network node, and converting the effective pre- and post-dependencies between tasks into directed edges, completely restoring the execution dependency logic between SQL tasks. Moreover, if there are changes in the SQL task status during the entire task lifecycle, such as task execution failure, task priority adjustment, or addition or deletion of effective dependency links, it is not necessary to rebuild the entire directed graph network. Only the nodes and edges affected by the status change are locally updated and adjusted through the incremental graph update algorithm. For example, when a SQL task fails to execute, the algorithm will automatically remove the downstream dependent edge corresponding to the task and trigger the subgraph reconstruction of the corresponding region. This not only ensures the real-time consistency between the directed graph network and the actual SQL task dependency relationship, but also reduces the resource consumption of graph network maintenance and improves the efficiency of graph network construction and updating.

[0034] In this embodiment of the invention, node features are extracted to endow the graph network construction with rich task attribute dimensions, enabling the directed graph network to realistically and comprehensively map the complex dependencies between SQL tasks, providing a precise network foundation for subsequent link analysis and optimization. Furthermore, the incremental graph update algorithm abandons the traditional full graph reconstruction mode, performing only local updates on affected nodes and edges during graph network construction. This reduces resource consumption for graph network construction and subsequent maintenance, improves the efficiency of graph network construction, and can adapt to the dynamic changes in SQL task status in real time, ensuring the consistency between the directed graph network and the actual SQL task dependency links. This makes subsequent optimization operations such as subgraph partitioning and task merging based on this graph network more aligned with actual business scenarios, further guaranteeing the scientific rigor and efficiency of the overall SQL task link optimization.

[0035] S4. Perform a feasibility test on the SQL tasks in each connected subgraph, and generate a task merging decision based on the test results.

[0036] For example, each connected subgraph after partitioning represents a highly cohesive set of SQL tasks. These subgraphs may contain many adjacent SQL tasks with direct dependencies. Distributing these tasks across multiple subgraphs creates complex dependency chains, increasing the complexity of task scheduling and management. Furthermore, waiting and resource contention between tasks can reduce overall execution efficiency. Distributed task nodes also consume more system computing and storage resources, and manual maintenance of multiple nodes is costly. By verifying the feasibility of merging SQL tasks within the connected subgraphs and implementing a reasonable merging process, redundant dependency chains within the subgraphs can be simplified, the number of task nodes reduced, and the complexity of task scheduling and maintenance lowered. Additionally, task execution logic can be integrated, reducing ineffective waiting time between tasks, improving overall execution efficiency, and releasing redundant resources occupied by distributed nodes.

[0037] Further, step S4 specifically includes: using a recursive algorithm to select at least two adjacent SQL tasks in the connected subgraph as tasks to be merged; detecting whether the tasks to be merged satisfy a preset circular dependency relationship; if the circular dependency relationship is not satisfied, evaluating the total time spent merging the tasks to be merged based on historical execution data; if the total time spent does not exceed a preset threshold, quantifying the maintainability score of the code after merging the tasks to be merged; if the maintainability score reaches a preset health threshold, generating a merging decision for the tasks to be merged.

[0038] For example, a recursive algorithm is used to select at least two adjacent SQL tasks with direct dependencies within the subgraph as tasks to be merged, starting from the bottom-level leaf nodes of the connected subgraph and proceeding upwards. A multi-dimensional feasibility test for merging is carried out layer by layer. Through depth-first search (DFS) and graph coloring techniques, the selected tasks to be merged are scanned in all dimensions to detect whether there are any pre-set circular dependencies, such as a closed loop where task A depends on task B and task B depends on task A. If a circular dependency is detected, the system will immediately roll back the current merge proposal and generate an alternative merge solution. This avoids problems such as execution deadlock and scheduling anomalies caused by closed loops in the task chain after merging, and avoids delays and omissions caused by manual review.

[0039] If no circular dependency is detected in the tasks to be merged, the duration prediction algorithm built based on the historical execution data of the SQL tasks, combined with the historical runtime and resource consumption data of the tasks to be merged, dynamically evaluates the total execution time of the merged tasks. At the same time, it is compared with the system's preset time threshold (such as 1 hour). If the predicted total time exceeds the threshold, it means that the task execution efficiency will be reduced after merging, and the system will automatically roll back and skip this merging evaluation.

[0040] If the predicted total time of the tasks to be merged does not exceed a preset threshold, a full-dimensional quality assessment is performed on the merged task code. For example, maintainability scores are quantitatively calculated from dimensions such as code size, logical complexity, and syntax standardization, and compared with the system's preset health threshold. If the score does not reach the health threshold, the system will forcibly trigger code refactoring suggestions and pause the merging process. In this embodiment of the invention, this assessment process can be implemented using a random forest model. First, a large amount of SQL code from business scenarios such as billing and user tagging in the telecommunications industry is used as samples. The model is trained by combining manually annotated maintainability scores, incorporating features strongly related to maintainability, such as the total number of lines of code, the number of nested subqueries, the number of conditional statements, the number of syntax errors, and the standardization of comments, into the model learning. When evaluating the merged code, the multi-dimensional quantitative features mentioned above are first extracted and numerically processed using an SQL syntax parser. The feature vectors are then input into a trained random forest model. The model outputs a maintainability score of 0-100 through ensemble inference of multiple decision trees. If the model's output score does not reach the preset health threshold, it indicates that the merged SQL task code does not meet the system's preset operational standards and suffers from issues such as bloated code, overly complex logic, non-standard syntax, and poor maintainability. Subsequent operations are prone to execution failures, high operational costs, and difficulties in iterative optimization, failing to meet the requirements for efficient management and stable execution of SQL task chains in high-concurrency, high-data-volume scenarios. The system will pause the merge process and may further trigger code refactoring suggestions, simultaneously outputting the specific reasons for the low score, such as excessive logical complexity or insufficient syntax standardization. This guides operations personnel to refactor and optimize the merged code. After refactoring, the code is re-entered into the model for scoring until it reaches the health threshold before the merge process can continue.

[0041] If the maintainability score of the tasks to be merged reaches the preset health threshold, and the results of multi-dimensional checks on prior circular dependencies, total time consumption, and code maintainability are combined, the system determines that the group of tasks to be merged has reasonable merging conditions. The system will then formally generate a merging decision for the group of tasks to be merged, specifying information such as the configuration of the merged task nodes and the integration method of the execution logic. At the same time, based on historical data and machine learning, the system will estimate the completion time of the merged tasks and optimize it to a reasonable range of the latest completion time of all tasks before the merge plus X minutes (e.g., 30 minutes), ensuring that the merged tasks are seamlessly integrated with the original business processes and guaranteeing business continuity.

[0042] In this embodiment of the invention, a recursive algorithm is used to conduct a multi-dimensional feasibility test for merging. First, it checks for circular dependencies between the tasks to be merged, fundamentally avoiding execution failures caused by task loops after merging. Then, it evaluates the total time consumption after merging based on historical execution data, ensuring that the task merging does not exceed the time threshold and affect overall execution efficiency. Subsequently, it further quantitatively analyzes the maintainability score of the merged code to ensure that the merged task code meets healthy operation and maintenance standards. The entire testing process avoids system operation problems caused by unreasonable merging, ensures the scientific and effective nature of the merging decision, effectively simplifies redundant task links within the subgraph, and improves the overall execution efficiency and management convenience of SQL tasks.

[0043] S5. Optimize the link of the SQL task based on the task merging decision.

[0044] For example, after obtaining the task merging decision, based on the combination of tasks to be merged that has passed multi-dimensional testing in each connected subgraph, the SQL task links in each connected subgraph are reconstructed and optimized in sequence according to the bottom-up recursive optimization principle, so as to simplify and improve the efficiency of the entire SQL task dependency network, while ensuring the continuity and stability of task link execution.

[0045] Further, step S5 specifically includes: creating a new task node according to the task merging decision; wherein the new task node uses the configuration information of the task to be merged; mapping the upstream dependencies of the task to be merged to the new task node; marking the task to be merged as paused; and cutting off the redundant call links corresponding to the task to be merged.

[0046] For example, the system automatically creates a new SQL task node based on the task merging decision. The new node fully inherits all the configuration information of the task to be merged, including the task execution cycle, tenant identifier, task priority, trigger time, etc. If the task to be merged A is a daily billing task triggered at 8:00 AM and the task B is a billing sub-task of the same tenant triggered at 9:00 AM, the new node will directly inherit the optimal trigger time at 8:00 AM and all billing-related configurations without manual reconfiguration, ensuring that the new node has the complete execution attributes of the original task and achieving seamless succession of task functions. Next, a topology sorting algorithm is used to map all upstream dependencies of the tasks to be merged to the new task nodes. This process sorts out and inherits all the associated logic of the original upstream tasks, ensuring that the dependency links between the new nodes and the upstream tasks are unobstructed. For example, if the tasks to be merged are A and B, where task A's upstream dependencies are tasks C and D, and task B's upstream dependencies are tasks D and E, the topology sorting algorithm will first perform a topology sort on the upstream dependent nodes C, D, and E of tasks A and B, clarifying the execution order logic and relationships of each upstream node. Then, these upstream dependencies are completely mapped to the newly created task node N, allowing the new node N to inherit all dependencies on C, D, and E, while preserving the original execution order of the upstream nodes. That is, the new node N will be triggered to execute after C and D have finished executing, and after E and D have finished executing, which is completely consistent with the dependency logic of the original tasks A and B on the upstream nodes. This ensures that the dependency links between the new node N and the upstream tasks C, D, and E are unobstructed throughout the entire process, without any issues such as missing dependencies or disordered execution order, thus guaranteeing the continuity of data flow and execution process in the entire SQL task chain. Finally, the system marks all the SQL tasks originally to be merged as paused, so that they no longer participate in task scheduling and execution. At the same time, it automatically cuts off all redundant call links corresponding to these tasks to be merged, including their old dependency associations with upstream tasks and their call relationships with downstream tasks. This completely eliminates the occupation of system resources by the original task nodes, releases system resources such as computing, storage, and scheduling, and avoids the interference of redundant task links on the overall SQL task network.

[0047] In this embodiment of the invention, a new task node is created based on the task merging decision, while retaining the configuration information of the task to be merged. This ensures that the new task node possesses the complete execution attributes of the original task, enabling seamless handover of task functionality without manual reconfiguration. Simultaneously, the upstream dependencies of the task to be merged are mapped to the new task node, ensuring the continuity and integrity of the task chain and preventing task execution failures caused by broken dependencies. Furthermore, by marking the task to be merged as paused and cutting off its corresponding redundant call links, the system computing and storage resources occupied by the task to be merged can be released in a timely manner, eliminating the interference of redundant tasks on the overall SQL task chain. This simplifies the task chain structure, reduces the complexity of chain management, improves system resource utilization, effectively shortens the overall runtime of the SQL task, and makes the optimized task chain more suitable for high-concurrency, large-data-volume processing scenarios.

[0048] Further, see Figure 2 , Figure 2 This is a comparison diagram of the SQL task chain before and after optimization provided in this embodiment of the invention. Taking the subgraph merging process as an example, the task network before optimization consists of 6 SQL task nodes (101, 102, 103, 104, 105, 106), presenting an overall 3-layer dependency structure: Layer 1: Task 103, the upstream trigger node; Layer 2: Tasks 101 and 102 are intermediate nodes that depend on or are triggered independently of Layer 1; Layer 3: Tasks 104, 105, and 106 are downstream nodes that depend on the nodes in Layer 2.

[0049] There are a total of 6 intertwined dependency links: 103→101, 103→106, 101→104, 101→106, 102→106, and 102→105. There are multiple path cross dependencies between nodes, which makes the task scheduling logic complicated, the visibility low, and the operation and maintenance management difficult.

[0050] After the entire process optimization process of this invention, the original 6 task nodes are merged into 3 new task nodes, namely: New node 103: Retain the original task 103 as the upstream trigger node; New node 101104: It is formed by merging the original tasks 101 and 104, and inherits the execution logic and dependencies of the two. New node 102105106: It is formed by merging the original tasks 102, 105 and 106, integrating the execution logic and dependencies of the three; The dependency links are simplified to three: 103→101104, 103→102105106, and 101104→102105106. The three-layer dependency structure is still maintained, with no cross-redundant links, and the topology is simple and intuitive.

[0051] The comparison shows that reducing the number of task nodes from 6 to 3 reduces the management costs of task scheduling and operation and maintenance; reducing the number of intertwined dependency links from 6 to 3 clear links eliminates redundant associations, reduces the risk of execution waiting and resource contention; and while retaining the original 3-layer execution hierarchy, it improves the visibility of the task network, making it easier to quickly understand business logic and troubleshoot problems.

[0052] Further, see Figure 3 , Figure 3 This is a schematic diagram of the SQL task chain optimization process provided in this embodiment of the invention. In the initial state, the SQL task dependency network contains 6 independent task nodes with multiple intertwined dependency chains between them, resulting in a complex overall structure. This provides the original task topology for subsequent merging and optimization. During the first merge, following the merging verification principle from bottom to top and from left to right, the bottom-level adjacent task nodes 102 and 106 are selected as tasks to be merged. After multi-dimensional verification such as circular dependency detection, time consumption evaluation, and code maintainability scoring, it is confirmed that the two meet the merging conditions and are integrated into a new task node 102106. The new node fully inherits the configuration information, execution logic, and dependency relationships of the original tasks 102 and 106. The original tasks 102 and 106 are marked as paused, their redundant call chains are cut off, and the task network structure is initially simplified. During the second merge, the principle of bottom-up and left-to-right merging verification continued. Adjacent new node 102106 and task node 105 were selected as tasks to be merged. After multi-dimensional verification, they were integrated into a new task node 102105106. The new node fully inherits all the functions and attributes of the original node 102106 and task 105. The original node 102106 and task 105 were marked as paused, redundant call links were further severed, and the task network structure continued to simplify. During the third merge, adjacent upper-level task nodes 101 and 104 were selected as tasks to be merged. After multi-dimensional verification, they were integrated into a new task node 101104. The new node fully inherits the configuration information and dependencies of the original tasks 101 and 104. The original tasks 101 and 104 were marked as paused, and redundant call links were completely severed. After three progressive merging steps, the initial six task nodes were streamlined into three new task nodes (103, 101104, and 102105106). The original six intertwined dependency links were simplified into three clear dependency links, forming a concise and logically clear optimized SQL task dependency network. This effectively reduced the complexity of task scheduling and operation and maintenance, and improved the system resource utilization and the visibility of the links.

[0053] The above optimization examples can be further referenced in Tables 1 and 2. Table 1 shows the data example before task link optimization, and Table 2 shows the data example after task link optimization. As shown in Table 1, the task link before optimization had the following problems: ① Uneven distribution of task levels, divided into 3 levels. Level 1 contains 2 tasks, Level 2 contains 3 tasks, and Level 3 contains only 1 task. The number of nodes between levels varies greatly, and the scheduling logic is complex; ② Significant differences in runtime, with computational tasks (101, 103) taking longer and lightweight triggering tasks (102, 105) taking shorter, for a total runtime of 99 minutes; ③ A large number of upstream dependencies, with task 101 having 9 upstream dependencies, Level 2 tasks (103, 104, 105) each depending on 4 upstream nodes, and Level 3 task 106 depending on 5 upstream nodes, for a total of 27 upstream dependencies. The dependencies are redundant and cross-referenced, increasing the risk of scheduling wait and resource contention. After optimization using the method of this invention, the task chain is simplified to 3 new task nodes. As shown in Table 2, the various indicators of the optimized task chain are significantly improved. The task hierarchy is integrated and optimized, still maintaining a clear 3-layer structure. The number of nodes is reduced from 6 to 3, and the hierarchical distribution is more balanced, which facilitates unified scheduling and operation and maintenance management. The runtime is effectively shortened. After merging, the tasks inherit the execution logic of the original tasks, avoiding waiting overhead between tasks. The total runtime is 83 minutes (originally 99 minutes), which is about 16% less than before optimization. The number of upstream dependencies is controlled. The total number of upstream dependencies is 23 (originally 27), which is about 15% less than before optimization. Redundant dependencies are effectively eliminated, reducing the complexity of data flow and the risk of failure.

[0054] Table 1. Data Example Before Task Link Optimization

[0055] Table 2. Data examples after task link optimization

[0056] Compared to existing technologies, the SQL task link optimization method disclosed in this invention first acquires all SQL task-related data, then performs targeted filtering on inter-task dependency links to eliminate invalid dependencies from the data source. It then constructs a directed graph network and divides it into connected subgraphs based on the filtered valid dependency links, allowing complex SQL task dependencies to form a structured, modular, and visual network, enabling a global review and analysis of task dependencies. Simultaneously, it performs feasibility checks on SQL tasks within each connected subgraph and generates merging decisions, thereby completing link optimization. The entire process is automated through algorithms for link analysis, verification, and reconstruction, abandoning the existing model of relying on periodic reviews of static logs. It eliminates the need for manual intervention in analysis and processing, achieving real-time detection and handling of SQL task execution anomalies, eliminating the time lag between anomaly discovery and handling, preventing system failures caused by accumulated problems, and freeing users from reliance on professional manual teams. Furthermore, it simplifies redundant links and improves task execution efficiency through subgraph partitioning and task merging, effectively adapting to the SQL task link management needs under high concurrency and large data volumes, and improving the efficiency, stability, and flexibility of SQL task link scheduling.

[0057] See Figure 4 , Figure 4 This is a structural block diagram of an SQL task link optimization device 100 provided in an embodiment of the present invention. The SQL task link optimization device 100 includes: Data acquisition module 11 is used to acquire task-related data for SQL tasks; The data filtering module 12 is used to filter the dependency links between SQL tasks based on the task-related data; wherein, the dependency links are used to characterize the association relationship between the SQL tasks. Network construction module 13 is used to construct a directed graph network for SQL tasks based on filtered dependency links, and to divide the directed graph network into at least two connected subgraphs; The merge verification module 14 is used to perform a merge feasibility verification on the SQL tasks in each connected subgraph and generate a task merge decision based on the verification results. The optimization execution module 15 is used to optimize the link of the SQL task based on the task merging decision.

[0058] It is worth noting that the working process of each module in the SQL task link optimization device 100 described in the embodiments of the present invention can refer to the working process of the SQL task link optimization method described in the above embodiments, and will not be repeated here.

[0059] See Figure 5 , Figure 5This is a structural block diagram of an SQL task link optimization device 200 provided in an embodiment of the present invention. The SQL task link optimization device 200 includes a processor 21, a memory 22, and a computer program stored in the memory 22 and executable on the processor 21. When the processor 21 executes the computer program, it implements the steps in the various SQL task link optimization method embodiments described above.

[0060] For example, the computer program can be divided into one or more modules / units, which are stored in the memory 22 and executed by the processor 21 to complete the present invention. The one or more modules / units can be a series of computer program instruction segments capable of performing specific functions, which describe the execution process of the computer program in the SQL task link optimization device 200.

[0061] The SQL task link optimization device 200 may include, but is not limited to, a processor 21 and a memory 22. Those skilled in the art will understand that the schematic diagram is merely an example of the SQL task link optimization device 200 and does not constitute a limitation on the SQL task link optimization device 200. It may include more or fewer components than illustrated, or combine certain components, or different components. For example, the SQL task link optimization device 200 may also include input / output devices, network access devices, buses, etc.

[0062] The processor 21 can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor can be a microprocessor or any conventional processor. The processor 21 is the control center of the SQL task link optimization device 200, connecting all parts of the SQL task link optimization device 200 via various interfaces and lines.

[0063] The memory 22 can be used to store the computer programs and / or modules. The processor 21 implements various functions of the SQL task link optimization device 200 by running or executing the computer programs and / or modules stored in the memory 22 and calling the data stored in the memory 22. The memory 22 may mainly include a program storage area and a data storage area. The program storage area may store the operating system, at least one application program required for a function (such as sound playback function, image playback function, etc.), etc.; the data storage area may store data created according to the use of the mobile phone (such as audio data, phonebook, etc.). In addition, the memory 22 may include high-speed random access memory, and may also include non-volatile memory, such as hard disk, memory, plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, at least one disk storage device, flash memory device, or other volatile solid-state storage device.

[0064] The modules / units integrated into the SQL task link optimization device 200, if implemented as software functional units and sold or used as independent products, can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the above embodiments can also be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by the processor 21, it can implement the steps of the various method embodiments described above. The computer program includes computer program code, which can be in the form of source code, object code, executable files, or certain intermediate forms. The computer-readable medium can include: any entity or device capable of carrying the computer program code, recording media, USB flash drives, portable hard drives, magnetic disks, optical disks, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc.

[0065] Furthermore, the present invention also provides a computer program product, including a computer program / instruction that, when executed by a processor, implements the SQL task chain optimization method as described in any of the above embodiments.

[0066] The above description represents the preferred embodiments of the present invention. It should be noted that those skilled in the art can make various improvements and modifications without departing from the principles of the present invention, and these improvements and modifications are also considered to be within the scope of protection of the present invention.

Claims

1. A method for optimizing SQL task chains, characterized in that, include: Retrieve task-related data for SQL tasks; The dependency links between SQL tasks are filtered based on the task-related data; wherein, the dependency links are used to characterize the association relationships between the SQL tasks. A directed graph network for SQL tasks is constructed based on the filtered dependency links, and the directed graph network is divided into at least two connected subgraphs. Perform a feasibility check on the SQL tasks in each connected subgraph, and generate a task merging decision based on the check results; The link of the SQL task is optimized based on the task merging decision.

2. The SQL task chain optimization method as described in claim 1, characterized in that, The task-related data includes at least one of the following: attribute data of the SQL task, dependency chain data between tasks, and runtime data of the task instance.

3. The SQL task chain optimization method as described in claim 1, characterized in that, The filtering of dependency links between SQL tasks based on the task-related data includes at least one of the following data filtering methods: Deleting cross-cycle dependency links using an adaptive dynamic algorithm; Deleting timed-out dependency links based on timestamp comparison technology; Dependency links that do not meet the optimization criteria are filtered based on user-defined rules.

4. The SQL task chain optimization method as described in claim 1 or 3, characterized in that, The directed graph network for constructing the SQL task based on the filtered dependency links includes: Obtain the node characteristics of the SQL tasks in the filtered dependency chain; Based on the node characteristics and the relationship between SQL tasks, an incremental graph update algorithm is used to construct a directed graph network for SQL tasks.

5. The SQL task chain optimization method as described in claim 1, characterized in that, The step of performing a feasibility test on the SQL tasks in each connected subgraph and generating a task merging decision based on the test results includes: A recursive algorithm is used to select at least two adjacent SQL tasks in the connected subgraph as tasks to be merged, and it is detected whether the tasks to be merged satisfy a preset circular dependency relationship. If the circular dependency relationship is not satisfied, the total time required to merge the tasks to be merged is evaluated based on historical execution data. If the total time consumed does not exceed a preset threshold, the maintainability score of the code after merging the tasks to be merged is quantified. If the maintainability score reaches a preset health threshold, a merging decision is generated for the tasks to be merged.

6. The SQL task chain optimization method as described in claim 5, characterized in that, The optimization of the SQL task chain based on the task merging decision includes: A new task node is created based on the task merging decision; wherein the new task node uses the configuration information of the task to be merged; The upstream dependencies of the tasks to be merged are mapped to the new task node, the tasks to be merged are marked as paused, and the redundant call links corresponding to the tasks to be merged are cut off.

7. An SQL task chain optimization device, characterized in that, include: The data acquisition module is used to obtain task-related data for SQL tasks; A data filtering module is used to filter the dependency links between SQL tasks based on the task-related data; wherein, the dependency links are used to characterize the association relationships between the SQL tasks. A network construction module is used to construct a directed graph network for SQL tasks based on filtered dependency links, and to divide the directed graph network into at least two connected subgraphs. The merge verification module is used to perform a merge feasibility verification on the SQL tasks in each connected subgraph and generate a task merge decision based on the verification results. An optimization execution module is used to optimize the chain of the SQL task based on the task merging decision.

8. An SQL task chain optimization device, characterized in that, It includes a processor, a memory, and a computer program stored in the memory and configured to be executed by the processor, wherein the processor, when executing the computer program, implements the SQL task chain optimization method as described in any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium includes a stored computer program, wherein, when the computer program is executed, it controls the device where the computer-readable storage medium is located to perform the SQL task chain optimization method as described in any one of claims 1 to 6.

10. A computer program product, characterized in that, It includes computer instructions that, when executed by a processor, implement the SQL task chain optimization method as described in any one of claims 1 to 6.

Images