Eureka AIR delivers breakthrough ideas for toughest innovation challenges, trusted by R&D personnel around the world.

Optimization method and device of non-correlator inquiry

An optimization method and sub-query technology, applied in the computer field, can solve problems such as high time consumption and lower execution efficiency of database query statements, and achieve the effect of saving execution time and improving execution efficiency

Active Publication Date: 2017-11-03
TENCENT TECH (SHENZHEN) CO LTD
View PDF8 Cites 15 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
  • Optimization method and device of non-correlator inquiry
  • Optimization method and device of non-correlator inquiry
  • Optimization method and device of non-correlator inquiry

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 invention discloses an optimization method and device of non-correlator inquiry. According to the embodiment, whether a non-correlator inquiry sentence exists in a target sub sentence in a database inquiry sentence or not is determined; if the non-correlator inquiry sentence exists, the estimated row number of an execution result set corresponding to the target sub sentence is obtained; when the estimated row number is smaller than a preset threshold value, the non-correlator inquiry sentence is executed to obtain the execution result set; and the target sub sentence is modified according to the execution result set so as to eliminate the non-correlator inquiry sentence. By using the scheme, the non-correlator inquiry sentence can be executed in advance before the sentence execution stage such as in the logic optimization stage; the located target sub sentence of the non-correlator inquiry sentence is modified according to the execution result so as to eliminate the non-correlator inquiry, so that the relevant inquiry and access on any non-correlator inquiry sentence are not needed in the subsequent sentence execution stage; and the execution efficiency of the database inquiry sentence with the non-correlator inquiry can be improved.

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
IPC IPC(8): G06F17/30
CPCG06F16/24549G06F16/2456G06F16/24537G06F16/24535
Inventor 李海翔
Owner TENCENT TECH (SHENZHEN) CO LTD
Who we serve
  • R&D Engineer
  • R&D Manager
  • IP Professional
Why Eureka
  • Industry Leading Data Capabilities
  • Powerful AI technology
  • Patent DNA Extraction
Social media
Eureka Blog
Learn More
PatSnap group products