CA3132004A1 - Sql query optimization method and device - Google Patents
Sql query optimization method and device Download PDFInfo
- Publication number
- CA3132004A1 CA3132004A1 CA3132004A CA3132004A CA3132004A1 CA 3132004 A1 CA3132004 A1 CA 3132004A1 CA 3132004 A CA3132004 A CA 3132004A CA 3132004 A CA3132004 A CA 3132004A CA 3132004 A1 CA3132004 A1 CA 3132004A1
- Authority
- CA
- Canada
- Prior art keywords
- subquery
- query
- statements
- target data
- statement
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 47
- 238000005457 optimization Methods 0.000 title claims abstract description 26
- 238000004364 calculation method Methods 0.000 claims abstract description 22
- 238000012545 processing Methods 0.000 claims description 26
- 238000004590 computer program Methods 0.000 claims description 6
- 230000007246 mechanism Effects 0.000 abstract description 10
- 230000000977 initiatory effect Effects 0.000 abstract description 5
- 238000005516 engineering process Methods 0.000 abstract description 2
- 238000010586 diagram Methods 0.000 description 3
- 238000004422 calculation algorithm Methods 0.000 description 1
- 238000001914 filtration Methods 0.000 description 1
- 239000011159 matrix material Substances 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000012805 post-processing Methods 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24539—Query rewriting; Transformation using cached or materialised query results
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/248—Presentation of query results
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
parsing SQL
query statements classified as UNION type query statements, and counting the number of storage segments associated with target data, wherein a UNION type query includes multiple subquery statements; when the number of associated storage segments exceeds a threshold, dividing query periods of the subquery statements into multiple granularity periods, then adding combined target data dimensions for the subquery statements into the subquery statements as additional limit rules; and sending the subquery statements with limit rules to a computation engine, parallelly accessing target data and dimensions in each granularity period, then returning the query results to users after calculation. The forementioned device is applied to achieve the described method.
Description
Technical Field [0001] The present invention is related to the field of big data technologies, in particular to a SQL query optimization method and device.
Background
Summary
type query includes multiple subquery statements;
Date Recue/Date Received 2021-09-27
accessing the query period corresponding to each described subquery statement, and dividing each query period into multiple granularity periods according to a pre-set mean granularity.
where if the dimensions of the target data corresponding to each subquery statement are identical, selecting one of the described dimension of the target data corresponding to the subquery statement and adding into each subquery statement as an additional limit rule;
and
Preferably, the described UNION type query statement includes at least a primary period query statement and a secondary period query statement, and the described limit rules includes query metrices, a dimension rank, and numbers of target data queries besides the additional rule.
Preferably, the computation engine parallelly accesses the target data and dimension values in each granularity period by:
Furthermore, the query result is return to users after calculation on the processing terminal by:
Preferably, the described subquery statements with limit rules are sent to the computation engine, parallelly accessing target data and dimension values in each granularity period, then returning the query results to the users after the calculation on the processing terminal, is followed by the step of:
Date Recue/Date Received 2021-09-27
Date Recue/Date Received 2021-09-27
a statement parsing unit, configured to parse SQL query statements classified as UNION type query statements, and counting the number of storage segments associated with target data, wherein a UNION type query includes multiple subquery statements;
a detecting and processing unit, configured to divide query periods of the subquery statements into multiple granularity periods when the number of associated storage segments exceeds a threshold, combine the target data dimensions for the subquery statements, and add the combined target data into the subquery statements as an additional limit rule;
and
a searching unit, configured to send the subquery statements with limit rules to a computation engine, parallelly access target data and dimension values in each granularity period, then return the query results to the users after the calculation on the processing terminal.
From the third perspective, a computer readable storage medium is provided in the present invention, wherein the computer programs are stored on. When the described computer programs are executed by the processor, any of the procedures in the forementioned SQL query optimization method are performed.
Compared with the currently available techniques, the computer readable storage medium can provide the same benefits as forementioned SQL query optimization method and is not explained in detail herein.
Brief descriptions of the drawings
To clarify, the described drawings are providing further explanations for the present invention as a part of the present invention. The demonstration embodiments and descriptions are used to explain the present invention and shall not limit the present invention. In the drawings:
Fig. 3 is a schematic diagram of the SQL query optimization method in the present invention.
Date Recue/Date Received 2021-09-27 Detailed descriptions
Based on the embodiments in the present invention, the other applications by those skilled in the art without any creative works are falling within the scope of the present invention.
Referring to Fig. 1, a SQL query optimization method is provided in the present embodiment, comprising:
type query includes multiple subquery statements; when the number of associated storage segments exceeds a threshold, dividing query periods of the subquery statements into multiple granularity periods, and adding the combined target data dimensions for the subquery statements into the subquery statements as additional limit rules; and sending the subquery statements with limit rules to a computation engine, parallelly accessing target data and dimension values in each granularity period, then returning the query results to the users after the calculation on the processing terminal.
Obviously, the present invention can not only solve the problem of directly circuit-breaker mechanism of the computation engine during a full-volume query, but also standardize and yield the uniform dimension of the target data for each subquery statement, to guarantee the query result output.
In practice, the UNION type query statements are represented as UNION ALL, including primary period query statement and a secondary period query statement. The described limit rules include query metrices, dimension rank, and numbers of target data queries besides the additional rule. The computation engine is Druid engine; the source of data is noted as datasource; the storage segments are noted as segment, and the processing terminal is the spark terminal. In particular, the numbers of target data queries in the limit rules are used to restrict the target data reading volume by the computation engine, to prevent initiating the circuit-breaker mechanisms.
In the forementioned embodiment, when the number of associated storage segments exceeds the threshold, the query periods of the subquery statements are divided into multiple granularity periods by:
accessing the query period corresponding to each described subquery statement, and dividing each query period into multiple granularity periods according to a pre-set mean granularity.
Illustratively, the SQL query statement includes inner statement and outer statement.
Taking the year-to-year period query as an example, the inner statement includes the primary subquery statement and the secondary subquery statement, wherein the primary subquery statement and the secondary subquery statement imply the current period query statement and the historical period query statement, individually. The inner statement is used to acquire the target data corresponding to the primary subquery statement and the secondary subquery statement from the Druid, and the outer statement is used to access the dimensions of the target data and perform associated year-to-year calculation from the spark terminal based on the inner statement.
query statements, to extract the original data information able to be sliced, such as the target data Date Recue/Date Received 2021-09-27 associated storage segment query command, target data dimension query command, target data matrix query command, target data period query command, etc. Then, the number of the storage segments associated with the target data is determined to be exceeding the threshold or not.
Where if the storage segments associated with the target data exceeds the threshold, the entire subquery can be sent to the Druid for a full-volume query based on the current techniques.
Where if the storage segments associated with the target data does not exceed the threshold, the query period is evenly divided into multiple granularity periods for parallel queries, to prevent initiating the circuit-breaker mechanisms. For example, the minimum granularity is a day when the datasource is taken, and the current SQL query statement queries the data within one month.
The one-month query period is divided into thirty granularity periods. In the following, the SQL
query statement corresponding to each granularity period are processed, in order to reserve only necessary portion of the target dimension data and filter out the irrelevant derived metrices data.
The post-processing SQL query data corresponding to these granularity periods are converted to json statements receivable by the druid. A request for acquiring the target data is sent to the druid.
After the druid queries the target data for each granularity period, the dimension sets and corresponding dimension values of each granularity period are returned by the druid. The data sets returned from all granularity periods are combined. Then, a data set with less or equal data volume to the target data query is obtained via selection sort algorithm.
where if the dimensions of the target data corresponding to each subquery statement are identical, selecting one of the described dimension of the target data corresponding to the subquery statement and adding into each subquery statement as an additional limit rule;
and where if the dimensions of the target data corresponding to each subquery statement are not identical, combining the target data dimensions for the subquery statements and adding the combined target data into the subquery statements as an additional limit rule.
For example, in terms of the case wherein the pre-set calculation rules are set for growth rate Date Recue/Date Received 2021-09-27 calculation, the growth rate result is obtained via dividing the dimension values of the primary by the dimension values of the primary secondary periods.
query statements, based on the most active datasource and the associated dimension fields, the druid statements are generated dynamically when low query volume. Then the described dimension and dimension values are pre-extracted and stored in the form of materialized views. In the upcoming operations, the materialized views can be extracted immediately once the same datasource or dimension value queries are requested. As a result, it is not required to send the requests to the druid for accessing associated dimension value sets, to achieve the fast calling.
statement includes the history period query results, the present SQL query statement can be split based on the query periods of the query statements. The history period query results can be directly extracted from the databased and the rest of present periods can be obtained from the druid query. For example, Date Recue/Date Received 2021-09-27 the dimension data for day 1 to day 20 has been stored. If the current query requests for dimension from day 1 to day 30, only the granularity periods of day 21 to day 30 are sent to the druid to greatly reduce the pressure over the druid.
3. combining the target data dimensions for the subquery statements and adding the combined target data into the subquery statements as an additional limit rule;
after processing each subquery statement, converting the subqueries into the json statements that are readable by the druid. With the additional limit rules, the current subquery statements request more dimensions than the limit rules. In other words, the druid does not need to scan all the data within the period, wherein no circuit-breaker mechanisms initiation or query time-out, to greatly reduce the pressure over the druid.
a statement parsing unit, configured to parse SQL query statements classified as UNION type query statements, and counting the number of storage segments associated with target data, wherein a UNION type query includes multiple subquery statements;
a detecting and processing unit, configured to divide query periods of the subquery statements into multiple granularity periods when the number of associated storage segments exceeds a threshold, combine the target data dimensions for the subquery statements, and add the combined target data into the subquery statements as an additional limit rule;
and
a searching unit, configured to send the subquery statements with limit rules to a computation engine, parallelly access target data and dimension values in each granularity period, then return the query results to the users after the calculation on the processing terminal.
Date Recue/Date Received 2021-09-27
Date Recue/Date Received 2021-09-27
Claims (10)
parsing SQL query statements classified as UNION type query statements, and counting the number of storage segments associated with target data, wherein a UNION type query includes multiple subquery statements;
when the number of associated storage segments exceeds a threshold, dividing query periods of the subquery statements into multiple granularity periods, and adding the combined target data dimensions for the subquery statements into the subquery statements as additional limit rules;
and sending the subquery statements with limit rules to a computation engine, parallelly accessing target data and dimension values in each granularity period, then returning the query results to the users after the calculation on the processing terminal.
accessing the query period corresponding to each described subquery statement, and dividing each query period into multiple granularity periods according to a pre-set mean granularity.
identifying the dimensions of the target data corresponding to each subquery statement;
where if the dimensions of the target data corresponding to each subquery statement are identical, selecting one of the described dimension of the target data corresponding to the subquery statement and adding into each subquery statement as an additional limit rule;
and Date Recue/Date Received 2021-09-27 where if the dimensions of the target data corresponding to each subquery statement are not identical, combining the target data dimensions for the subquery statements and adding the combined target data into the subquery statements as an additional limit rule.
according to each subquery statement and corresponding limit rules, on the computation engine, searching for target data satisfying the described target data query amount rules from the storage segments corresponding to data sources, and extracting an associated dimension value.
based on the dimension value of each subquery, calculating according to the pre-set rules on the processing terminal and returning query results to users.
counting hot data source and hot data dimensions from the history SQL
statements, and pre-extract the dimension values of the hot data dimensions, for fast calling of the upcoming SQL
query statement.
Date Recue/Date Received 2021-09-27
caching each subquery statement and the target data and corresponding dimension values for each subquery statement in the database, for fast calling of upcoming identical subquery statements from the database.
a statement parsing unit, configured to parse SQL query statements classified as UNION type query statements, and counting the number of storage segments associated with target data, wherein a UNION type query includes multiple subquery statements;
a detecting and processing unit, configured to divide query periods of the subquery statements into multiple granularity periods when the number of associated storage segments exceeds a threshold, combine the target data dimensions for the subquery statements, and add the combined target data into the subquery statements as an additional limit rule;
and a searching unit, configured to send the subquery statements with limit rules to a computation engine, parallelly access target data and dimension values in each granularity period, then return the query results to the users after the calculation on the processing terminal.
Date Recue/Date Received 2021-09-27
Applications Claiming Priority (2)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202011048287.7A CN112286961B (en) | 2020-09-29 | 2020-09-29 | SQL optimization query method and device |
CN202011048287.7 | 2020-09-29 |
Publications (1)
Publication Number | Publication Date |
---|---|
CA3132004A1 true CA3132004A1 (en) | 2022-03-29 |
Family
ID=74422478
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
CA3132004A Pending CA3132004A1 (en) | 2020-09-29 | 2021-09-27 | Sql query optimization method and device |
Country Status (2)
Country | Link |
---|---|
CN (1) | CN112286961B (en) |
CA (1) | CA3132004A1 (en) |
Cited By (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN117453732A (en) * | 2023-12-25 | 2024-01-26 | 智业软件股份有限公司 | CDSS doctor's advice data query optimization method and system |
Families Citing this family (2)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN112765286A (en) * | 2021-02-01 | 2021-05-07 | 广州海量数据库技术有限公司 | Query method and device based on relational database |
CN113448983B (en) * | 2021-07-15 | 2024-01-30 | 中国银行股份有限公司 | Knowledge point processing method, device, server, medium and product |
Family Cites Families (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN103714058B (en) * | 2012-09-28 | 2017-05-17 | Sap欧洲公司 | Method for database query optimization and system using same |
CN110096489A (en) * | 2019-04-30 | 2019-08-06 | 阿里巴巴集团控股有限公司 | A kind of data query method, system, device and electronic equipment |
CN110515973B (en) * | 2019-08-30 | 2022-02-18 | 上海达梦数据库有限公司 | Data query optimization method, device, equipment and storage medium |
-
2020
- 2020-09-29 CN CN202011048287.7A patent/CN112286961B/en active Active
-
2021
- 2021-09-27 CA CA3132004A patent/CA3132004A1/en active Pending
Cited By (2)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN117453732A (en) * | 2023-12-25 | 2024-01-26 | 智业软件股份有限公司 | CDSS doctor's advice data query optimization method and system |
CN117453732B (en) * | 2023-12-25 | 2024-03-01 | 智业软件股份有限公司 | CDSS doctor's advice data query optimization method and system |
Also Published As
Publication number | Publication date |
---|---|
CN112286961B (en) | 2022-11-18 |
CN112286961A (en) | 2021-01-29 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
CA3132004A1 (en) | Sql query optimization method and device | |
US20210319024A1 (en) | Methods and systems for joining indexes for query optimization in a multi-tenant database | |
US7797286B2 (en) | System and method for externally providing database optimizer statistics | |
US8732163B2 (en) | Query optimization with memory I/O awareness | |
US10380114B2 (en) | System and method for generating rowid range-based splits in a massively parallel or distributed database environment | |
US20100131540A1 (en) | System, method, and computer-readable medium for optimizing processing of distinct and aggregation queries on skewed data in a database system | |
US8423569B2 (en) | Decomposed query conditions | |
US7921085B2 (en) | Method and system for quantifying a data page repetition pattern for a database index in a database management system | |
WO2018157680A1 (en) | Method and device for generating execution plan, and database server | |
US10394818B2 (en) | System and method for dynamic database split generation in a massively parallel or distributed database environment | |
US10078684B2 (en) | System and method for query processing with table-level predicate pushdown in a massively parallel or distributed database environment | |
US11003649B2 (en) | Index establishment method and device | |
US6122644A (en) | System for halloween protection in a database system | |
US10459912B2 (en) | Optimizing processing of group-by queries featuring maximum or minimum equality conditions in a parallel processing system | |
US20110252020A1 (en) | Enhanced utlization of query optimization | |
CN109656958A (en) | Data query method and system | |
Durner et al. | Crystal: a unified cache storage system for analytical databases | |
Behrend et al. | Period index: A learned 2d hash index for range and duration queries | |
WO2016086973A1 (en) | Unstructured search query generation from a set of structured data terms | |
D’silva et al. | Secondary indexing techniques for key-value stores: Two rings to rule them all | |
US11899665B2 (en) | Data aggregation and pre-positioning for multi-store queries | |
US20200364222A1 (en) | Temporary materialized views | |
CN116450607A (en) | Data processing method, device and storage medium | |
US20170132295A1 (en) | Top-k projection | |
US8290935B1 (en) | Method and system for optimizing database system queries |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
EEER | Examination request |
Effective date: 20220506 |
|
EEER | Examination request |
Effective date: 20220506 |
|
EEER | Examination request |
Effective date: 20220506 |
|
EEER | Examination request |
Effective date: 20220506 |
|
EEER | Examination request |
Effective date: 20220506 |
|
EEER | Examination request |
Effective date: 20220506 |
|
EEER | Examination request |
Effective date: 20220506 |
|
EEER | Examination request |
Effective date: 20220506 |
|
EEER | Examination request |
Effective date: 20220506 |
|
EEER | Examination request |
Effective date: 20220506 |
|
EEER | Examination request |
Effective date: 20220506 |
|
EEER | Examination request |
Effective date: 20220506 |
|
EEER | Examination request |
Effective date: 20220506 |