Query optimization execution method and device

By using the method of sharing CTE result data in memory in SparkSQL's query execution plan, the shuffle overhead problem of CTE reference in the query statement is solved, which significantly improves query efficiency.

CN119782343AActive Publication Date: 2025-04-08ALIPAY (HANGZHOU) INFORMATION TECH CO LTD
View PDF 4 Cites 2 Cited by

Patent Information

Application Number
CN202411754656.2
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-12-02
Publication Date
2025-04-08
Estimated Expiration
2044-12-02

AI Technical Summary

Technical Problem

In the process of executing data queries using query tools such as SparkSQL, it is difficult for the prior art to effectively improve query efficiency, especially when there is a common expression CTE in the query statement, additional shuffle overhead is generated, affecting query performance.

Method used

In the physical execution plan of the query, a custom execution operator is generated for the subquery reference that references the common expression CTE, the data results of the CTE are stored in the memory cache, and memory sharing is performed between execution stages with the same CTE dependency, reducing disk I/O operations.

Benefits of technology

This method can significantly improve the execution performance of queries and improve the efficiency of data queries by reducing shuffle operations and disk I/O.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119782343A_ABST
    Figure CN119782343A_ABST
Patent Text Reader

Abstract

The embodiment of the invention provides a query optimization execution method which comprises the following steps: receiving a query statement which comprises definitions of a plurality of common expressions CTE and a plurality of sub-query references referencing the CTE; a physical execution plan of the query statement is generated, the physical execution plan comprises a first execution operator generated by referring to a first sub-query referencing a first CTE, and the first execution operator is used for storing a first data result corresponding to the first CTE into a first cache in a memory. In the process of executing the physical execution plan, a plurality of first execution stages are determined according to the first execution operator, and the plurality of first execution stages depend on a first CTE; and sharing a first data result through the first cache among the plurality of first execution stages.
Need to check novelty before this filing date? Find Prior Art

Citation Information

Patent Citations

  • SQL (Structured Query Language) execution optimization method, device and equipment based on Trino common table expression

    CN118132582A

  • Methods And Apparatus Of Shared Expression Evaluation Across RDBMS And Storage Layer

    US20180144029A1

  • Query plan generation for queries having common expressions

    US9501536B1

  • Query plan generation for common table expressions

    US9589030B1

Cited By

  • Method for optimizing cte expression based on gbase8c distributed database

    CN120448419A

  • A CTE expression optimization method based on gbase8c distributed database

    CN120448419B