Unlock instant, AI-driven research and patent intelligence for your innovation.

A method and device for optimizing non-correlated subqueries

An optimization method and technology of an optimization device are applied in the computer field and can solve the problems of high time consumption and reduced execution efficiency of database query statements.

Active Publication Date: 2021-07-06
TENCENT TECH (SHENZHEN) CO LTD +1
View PDF8 Cites 0 Cited by
  • Summary
  • Abstract
  • Description
  • Claims
  • Application Information

AI Technical Summary

Problems solved by technology

[0006] However, when using the current non-correlated sub-query optimization method to optimize a database query statement (such as a SQL statement) with a non-correlated sub-query, when executing the optimized database query statement in the execution phase, it is necessary to access the table specified by the sub-query multiple times. (such as using semi-join optimization) or accessing the temporary table corresponding to the subquery specified table multiple times (such as using materialization optimization), the time is relatively large, which reduces the execution efficiency of database query statements with non-correlated subqueries, especially in the face of It is more obvious when querying a large amount of data

Method used

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
View more

Image

Smart Image Click on the blue labels to locate them in the text.
Viewing Examples
Smart Image
  • A method and device for optimizing non-correlated subqueries
  • A method and device for optimizing non-correlated subqueries
  • A method and device for optimizing non-correlated subqueries

Examples

Experimental program
Comparison scheme
Effect test

Embodiment 1

[0032] This embodiment will be described from the perspective of an apparatus for optimizing an uncorrelated subquery. Specifically, the apparatus for optimizing an uncorrelated subquery may be integrated in a server, and the server may be a database server or the like.

[0033] A method for optimizing a non-correlated subquery, comprising: determining whether there is a non-correlated subquery statement in a target clause in a database query statement, and if so, obtaining the estimated number of rows of the execution result set corresponding to the target clause, when the predicted When the number of estimated rows is less than the preset threshold, the non-correlated sub-query statement is executed to obtain an execution result set, and the target clause is rewritten according to the execution result set to eliminate the non-correlated sub-query statement.

[0034] Such as Figure 1a As shown, the specific flow of the optimization method of the non-correlated subquery can be...

Embodiment 2

[0132] According to the method described in Embodiment 1, an example will be given below for further detailed description.

[0133] In the embodiment of the present invention, the non-correlated sub-query optimization method provided by the present invention is introduced by taking the non-correlated sub-query optimization device integrated in the server and the database query statement as an SQL statement as an example.

[0134] Such as Figure 2a As shown, a non-correlated subquery optimization method, the specific process can be as follows:

[0135] 201. In the logical optimization stage of the SQL statement, the server determines whether there is a non-correlated subquery statement in the target clause of the SQL statement; if yes, execute step 202, and if not, end the optimization process of the non-correlated subquery.

[0136] Wherein, the target clause may be a clause in which a subquery may appear, such as a WHERE clause, a FROM clause, a JOIN / ON clause, a...

Embodiment 3

[0178] In order to better implement the above method, the embodiment of the present invention also provides an optimization device for non-correlated sub-queries, such as Figure 3a As shown, the device for optimizing the non-correlated subquery includes: a determination unit 301, an acquisition unit 302, an execution unit 303 and a rewriting unit 304, as follows:

[0179] (1) Determining unit 301;

[0180] The determination unit 301 is configured to determine whether there is a non-correlated subquery statement in the target clause of the database query statement.

[0181] Specifically, the determining unit 301 may determine whether there is an irrelevant sub-query statement in the target clause of the database query statement during the logic optimization stage of the database query statement.

[0182] Wherein, the database query statement can be implemented by multiple languages, for example, it can be implemented by SQL (Structured Query Language, Structured Que...

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to View More

PUM

No PUM Login to View More

Abstract

The embodiment of the present invention discloses a non-correlated sub-query optimization method and device; the embodiment of the present invention uses the method of determining whether the target clause in the database query statement has a non-correlated sub-query statement, and if it exists, obtains the corresponding execution of the target clause The estimated number of rows of the result set, when the estimated number of rows is less than the preset threshold, execute the non-correlated subquery statement to obtain the execution result set, and rewrite the target clause according to the execution result set to eliminate the non-correlated subquery statement Correlated subquery statements; this scheme can pre-execute non-correlated subquery statements before the statement execution stage, such as in the logic optimization stage, and rewrite the target clause where the non-correlated subquery statements are located according to the execution results to eliminate non-correlation subquery statements Sub-queries, so that there is no need to perform any query and access related to non-correlated query statements in the subsequent statement execution phase, which can improve the execution efficiency of database query statements with non-correlated sub-queries.

Description

technical field [0001] The invention relates to the field of computer technology, in particular to an optimization method and device for a non-correlated sub-query. Background technique [0002] Data processing systems, such as database systems and big data processing systems, usually support SQL (Structured Query Language, Structured Query Language) functions as the main goal, so as to provide users with convenient data operations. [0003] During SQL processing, the SQL engine is required to perform lexical analysis, syntax analysis, semantic analysis, logic optimization, and physical optimization on the SQL statement, and then deliver the obtained execution plan to the executor for execution. This process is divided into three stages: Analysis phase, optimization phase, execution phase. [0004] Wherein, the optimization of the subquery occurs in the logic optimization phase, which is an optimization sub-phase in the logic optimization phase (usually referred to as query...

Claims

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to View More

Application Information

Patent Timeline
no application Login to View More
Patent Type & Authority Patents(China)
IPC IPC(8): G06F16/2453G06F16/2455
CPCG06F16/24549G06F16/2456G06F16/24537G06F16/24535
Inventor 李海翔
Owner TENCENT TECH (SHENZHEN) CO LTD