CN115576563A - SQL script dynamic optimization method - Google Patents
SQL script dynamic optimization method Download PDFInfo
- Publication number
- CN115576563A CN115576563A CN202211210292.2A CN202211210292A CN115576563A CN 115576563 A CN115576563 A CN 115576563A CN 202211210292 A CN202211210292 A CN 202211210292A CN 115576563 A CN115576563 A CN 115576563A
- Authority
- CN
- China
- Prior art keywords
- script
- sql
- sql script
- target database
- analyzing
- 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
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/41—Compilation
- G06F8/44—Encoding
- G06F8/443—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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- 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/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/41—Compilation
- G06F8/42—Syntactic analysis
- G06F8/427—Parsing
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/41—Compilation
- G06F8/43—Checking; Contextual analysis
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- General Engineering & Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to a script optimization technology, and discloses a dynamic optimization method of an SQL script, which simplifies the operation of developers on a database, thereby reducing the learning cost of the SQL script optimization. Firstly, intercepting and analyzing a syntax structure of an SQL script by triggering an optimization mechanism, and checking script specifications; then, based on the parsed grammar structure and the index condition returned by the retrieval target database, the SQL script is dynamically optimized; searching the target database again by combining different script sets, virtual table sets and search condition sets; and finally, sequentially presenting information such as SQL scripts, table structure information, execution time, result sets, whether the result sets are consistent or not and the like according to the execution efficiency. By the method for dynamically optimizing the SQL script, the project development progress and the program execution efficiency can be improved, so that the script is optimized by developers more simply and efficiently, and time and energy are saved.
Description
Technical Field
The invention relates to a script optimization technology, in particular to a SQL script dynamic optimization method.
Background
With the gradual development of computer technology, software developers are faced with more diversified demands and larger data volume, and to date, in order to solve the problem of low efficiency of the traditional JDBC (Java language database connectivity), various packaging frameworks are developed, and the databases can be manipulated by manipulating objects. The business logic of an enterprise is very complex, and the encapsulation query provided by the framework cannot meet the complex queries, so that developers still need to hand-write related query scripts, and the more complex the business is, the more the data tables are, the more the structured query scripts are, and the lower the execution efficiency is. Complicated query scripts are often time-consuming to operate on the database, and the optimization design of the query scripts is necessary to improve the execution efficiency.
Disclosure of Invention
The technical problem to be solved by the invention is as follows: the SQL script dynamic optimization method is provided, and the operation of developers on the database is simplified, so that the learning cost of SQL script optimization is reduced.
The technical scheme adopted by the invention for solving the technical problems is as follows:
a SQL script dynamic optimization method comprises the following steps:
s1, intercepting an SQL script, analyzing a grammar structure of the script, and checking a script specification;
s2, retrieving the target database, and returning the index hit condition and the structural information of the target data table;
s3, analyzing the hit condition of the returned index, judging whether the script relates to index retrieval, if not, executing the step S4, and if so, executing the step S5;
s4, generating comparison group table structure information according to the table structure information of the target data table, establishing an optimized index column, and generating a corresponding virtual table in a database to form a virtual table set;
s5, judging whether the indexes are all hit, and if the indexes are all hit, executing the step S6;
s6, analyzing the script, matching all structured query keywords in the script according to the target database, sequentially combining and replacing optimizable keywords according to the optimization rule of the target database, and dynamically generating different SQL script sets;
s7, analyzing WHERE keywords in the script, and dynamically setting different retrieval condition sets according to a target data table of a target database;
s8, searching the target database again by combining the virtual table set, the SQL script set and the searching condition set;
and S9, sequentially presenting retrieval execution conditions of different query inputs according to execution efficiency.
Further, in step S1, the checking the script specification specifically includes: and checking whether the script accords with the target database specification, if so, entering a step S2, and otherwise, ending the optimization process.
Further, step S5 further includes: and if the index is not hit, analyzing the reason of the miss, and prompting a modification suggestion.
Further, in step S9, the content of the search execution case includes: SQL script, table structure information, execution time, result set and whether the result set is consistent or not.
The invention has the beneficial effects that:
based on the SQL script dynamic optimization scheme provided by the invention, when a developer develops a function, the developer can use the function to test the execution efficiency and check the dynamic optimization result set, and the method is very convenient and practical for the developer and can optimize the execution efficiency of a program to a great extent.
Drawings
FIG. 1 is a flowchart of a method for dynamically optimizing an SQL script according to an embodiment of the present invention.
Detailed Description
The invention aims to provide a dynamic optimization method of an SQL script, which simplifies the operation of developers on a database, thereby reducing the learning cost of the SQL script optimization. Firstly, intercepting and analyzing a syntax structure of an SQL script by triggering an optimization mechanism, and checking script specifications; then, based on the parsed grammar structure and the index condition returned by the retrieval target database, the SQL script is dynamically optimized; searching the target database again by combining different script sets, virtual table sets and search condition sets; and finally, sequentially presenting information such as SQL scripts, table structure information, execution time, result sets, whether the result sets are consistent or not and the like according to the execution efficiency. By the method for dynamically optimizing the SQL script, the project development progress and the program execution efficiency can be improved, so that the script is optimized by developers more simply and efficiently, and time and energy are saved.
The embodiment is as follows:
as shown in fig. 1, the method for dynamically optimizing an SQL script in this embodiment includes the following implementation processes:
1. starting a program, connecting a target database, and filling a large amount of data in a data table to be searched;
2. starting SQL optimization, triggering an interface, intercepting an SQL script and analyzing a syntactic structure of the script;
3. checking whether the script accords with the target database specification, and prompting to modify if the script does not accord with the database specification;
4. on the premise that the script accords with the specification, the script is continuously executed, the database is searched, and the index hit condition and the structural information of the target data table are returned;
5. analyzing the index hit condition, if the script does not relate to index retrieval, generating comparison group table structure information according to the table structure information of the target data table, establishing an optimized index column, and generating a corresponding virtual table in a database to obtain a virtual table set; if the index is not hit, analyzing the reason of the miss, and prompting a modification suggestion;
6. analyzing the script, matching all structured query keywords in the script according to the target database, sequentially combining and replacing optimizable keywords according to the optimization rule of the target database, and dynamically generating different SQL script sets;
7. analyzing WHERE keywords in the script, and dynamically setting different retrieval condition sets according to a target data table of a target database; in addition, for a script requiring a condition set, in addition to dynamically generating a search condition set, a developer may customize the search condition set.
8. Searching the target database again by combining the virtual table set, the script set and the condition set;
9. and finally, sequentially presenting information such as SQL scripts, table structure information, execution time, result sets, whether the result sets are consistent or not and the like according to the execution efficiency.
Finally, it should be noted that the above-mentioned embodiments are only preferred embodiments and are not intended to limit the present invention. It should be noted that, for those skilled in the art, it should be understood that various changes, substitutions, modifications, etc. can be made without departing from the spirit and scope of the invention as defined by the appended claims.
Claims (4)
1. A method for dynamically optimizing SQL scripts is characterized by comprising the following steps:
s1, intercepting an SQL script, analyzing a grammar structure of the script, and checking a script specification;
s2, retrieving the target database, and returning the index hit condition and the structural information of the target data table;
s3, analyzing the hit condition of the returned index, judging whether the script relates to index retrieval, if not, executing the step S4, and if so, executing the step S5;
s4, generating comparison group table structure information according to the table structure information of the target data table, establishing an optimized index column, and generating a corresponding virtual table in a database to form a virtual table set;
s5, judging whether the indexes are all hit, and if the indexes are all hit, executing the step S6;
s6, analyzing the script, matching all structured query keywords in the script according to the target database, sequentially combining and replacing optimizable keywords according to the optimization rule of the target database, and dynamically generating different SQL script sets;
s7, analyzing WHERE keywords in the script, and dynamically setting different retrieval condition sets according to a target data table of a target database;
s8, combining the virtual table set, the SQL script set and the retrieval condition set, and retrieving the target database again;
and S9, sequentially presenting retrieval execution conditions of different query inputs according to execution efficiency.
2. The method of claim 1, wherein the SQL script dynamic optimization method,
in step S1, the checking the script specification specifically includes: and checking whether the script accords with the target database specification, if so, entering a step S2, and otherwise, ending the optimization process.
3. The method of claim 1, wherein the SQL script dynamic optimization method,
in step S5, the method further includes: and if the index is not hit, analyzing the reason of the miss, and prompting a modification suggestion.
4. The method of claim 1, wherein the SQL script dynamic optimization method,
in step S9, the content of the search execution condition includes: SQL script, table structure information, execution time, result set and whether the result set is consistent or not.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211210292.2A CN115576563A (en) | 2022-09-30 | 2022-09-30 | SQL script dynamic optimization method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211210292.2A CN115576563A (en) | 2022-09-30 | 2022-09-30 | SQL script dynamic optimization method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN115576563A true CN115576563A (en) | 2023-01-06 |
Family
ID=84583133
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211210292.2A Pending CN115576563A (en) | 2022-09-30 | 2022-09-30 | SQL script dynamic optimization method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115576563A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116431799A (en) * | 2023-06-14 | 2023-07-14 | 湖南科德信息咨询集团有限公司 | Content accurate mining system based on technical innovation research and development |
-
2022
- 2022-09-30 CN CN202211210292.2A patent/CN115576563A/en active Pending
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116431799A (en) * | 2023-06-14 | 2023-07-14 | 湖南科德信息咨询集团有限公司 | Content accurate mining system based on technical innovation research and development |
| CN116431799B (en) * | 2023-06-14 | 2023-08-18 | 湖南科德信息咨询集团有限公司 | Content accurate mining system based on technical innovation research and development |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US9767147B2 (en) | Method of converting query plans to native code | |
| CN111309757B (en) | SQL interpreter and optimization method of HBase | |
| US8959106B2 (en) | Class loading using java data cartridges | |
| US11354284B2 (en) | System and method for migration of a legacy datastore | |
| US7853553B2 (en) | Engine for converting data from a source format to a destination format using user defined mappings | |
| US9430505B2 (en) | Automated data warehouse migration | |
| US9058360B2 (en) | Extensible language framework using data cartridges | |
| US8793240B2 (en) | Generation of machine code for a database statement by specialization of interpreter code | |
| US20080109424A1 (en) | Method of Querying Relational Database Management Systems | |
| CN111813798B (en) | Mapping method, device, equipment and storage medium based on R2RML standard | |
| CN110019314B (en) | Dynamic data packaging method based on data item analysis, client and server | |
| Yaghmazadeh et al. | Type-and content-driven synthesis of SQL queries from natural language | |
| CN109710220A (en) | Relevant database querying method, device, equipment and storage medium | |
| US8090735B2 (en) | Statement generation using statement patterns | |
| CN112231321B (en) | Oracle secondary index and index real-time synchronization method | |
| CN111695002B (en) | Database-independent query method based on XML sentences | |
| CN115576563A (en) | SQL script dynamic optimization method | |
| CN111782195A (en) | Query method based on adding annotation on request parameter and splicing into SQL | |
| CN117421302A (en) | Data processing method and related equipment | |
| CN115640295A (en) | SQL dynamic optimization method based on back-end framework | |
| CN106484706B (en) | Method and apparatus for executing procedural SQL statements for distributed systems | |
| Wcisło et al. | A java-integrated object oriented query language | |
| Jia-di et al. | Modeling Language Design and Mapping Rules for REST Interface | |
| Czejdo et al. | Request: A testbed relational database management system for instructional and research purposes | |
| US20240028594A1 (en) | Query refactoring framework |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |