CN109669952A - A kind of SQL execution efficiency Static Analysis Method - Google Patents
A kind of SQL execution efficiency Static Analysis Method Download PDFInfo
- Publication number
- CN109669952A CN109669952A CN201811447657.7A CN201811447657A CN109669952A CN 109669952 A CN109669952 A CN 109669952A CN 201811447657 A CN201811447657 A CN 201811447657A CN 109669952 A CN109669952 A CN 109669952A
- Authority
- CN
- China
- Prior art keywords
- sql
- sentence
- execution efficiency
- static analysis
- analysis method
- 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
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/42—Syntactic analysis
-
- 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
Landscapes
- Engineering & Computer Science (AREA)
- General Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Software Systems (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Debugging And Monitoring (AREA)
Abstract
The invention discloses a kind of SQL execution efficiency Static Analysis Methods, comprising the following steps: step S01: SQL syntax file being input in JavaCC and JJTree, SQL resolver is generated；Step S02: file to be measured being input in SQL resolver and is handled, and generates SQL abstract syntax tree；Step S03: establishing SQL syntax rule base, includes several customized syntax rule in the rule base；Step S04: matching treatment is carried out to SQL abstract syntax tree by SQL syntax rule base using rule match engine, marks out problematic sentence；Step S05: the sentence and its details of mark are exported.The present invention can be according to customized detection and analysis rule, SQL statement in engineering is scanned and static analysis, the help even audit of Replacing engineering Shi Jinhang SQL statement improve the efficiency of exploitation and O&M, it reduces and analyzes the time used, improve the accuracy rate of analysis.
Description
Technical field
The present invention relates to a kind of code analysis methods, in particular to a kind of SQL execution efficiency Static Analysis Method.
Background technique
The SQL statement for being largely used to database manipulation is often used in the exploitation of operation system.These SQL statements
Execution efficiency can largely influence the performance and stability of system.In order to which the SQL statement guaranteed in software product reaches
To the design object of expectation, software development and O&M team can take a variety of methods, these methods can substantially be divided into 2 classes:
One is dynamic monitoring and controlling methods, after software product is online, by the execution of the SQL statement generated when monitoring software actual motion
Time, CPU usage, IO efficiency etc., when measured value is more than that some preset threshold value just generates alarm；Another kind is static analysis
Method is audited by code of the experienced software engineer to SQL in software development process, finds potential property in SQL
Energy problem or correctness problem, and correct in time.
In above-mentioned traditional scheme, dynamic monitoring and controlling method cannot be accomplished to take precautions against in advance, and static analysis human cost is higher, need
It puts into a large amount of code inspection time and depends on the experience and technical level of engineer.
The patent application of publication number CN107480296A proposes a kind of database performance analysis method and dress based on SQL
It sets, wherein method includes: to extract SQL instruction from the log of database, analyzes the SQL instruction extracted, obtains
Database root instructs the operation information inquired according to SQL, is counted according to operation information, and multiple performance ginsengs are respectively obtained
Number.
Analysis method in the documents is substantially to statistically analyze, and service efficiency and accuracy be not high.
Summary of the invention
The not high problem of the efficiency and accuracy analyzed for the prior art, the invention discloses a kind of SQL execution efficiencys
Static Analysis Method matches SQL abstract syntax tree by the rule of setting, accelerates analysis speed, reduce analysis
Time improves accuracy of analysis.
It is technical solution of the present invention below.
A kind of SQL execution efficiency Static Analysis Method, comprising the following steps:
Step S01: SQL syntax file is input in JavaCC and JJTree, generates SQL resolver；
Step S02: file to be measured being input in SQL resolver and is handled, and generates SQL abstract syntax tree；
Step S03: establishing SQL syntax rule base, includes several customized syntax rule in the rule base；
Step S04: matching treatment, mark are carried out to SQL abstract syntax tree by SQL syntax rule base using rule match engine
Problematic sentence out；
Step S05: the sentence and its details of mark are exported.
The present invention can be scanned the SQL statement in engineering and static state is divided according to customized detection and analysis rule
Analysis helps the audit of even Replacing engineering Shi Jinhang SQL statement, improves the efficiency of exploitation and O&M.
Further, in the step S02, treatment process includes filtering out SQL statement in file to be measured and by SQL language
Sentence presses object categorization, carries out abstract processing, the English that the object name is started with capitalization to the SQL statement after the completion of classification
Literary word indicates that the object includes Select and Where.
Further, the abstract processing includes the component part that SQL syntax is abstracted using DSL, with multilayer subobject
Form component part is further split.
Further, in the step S03, the customized syntax rule of rule base is write by DSL language, including several
The syntactically correct but low execution efficiency statement form of kind.
Further, in the step S04, rule match engine is generated by search engine and rule base.
Further, in the step S04, matching treatment process are as follows: by the content in SQL abstract syntax tree by appearance frequency
Rate arranges descending, and by the content of sentence and regular storehouse matching since the head of sequence, qualified sentence is saved to set A,
Ineligible preservation is to set B.
Further, be additionally provided with secondary verification: randomly selecting several sentences from the set B, to the sentence of extraction into
Row verification, sentence then marks if you have questions.
Further, the executive mode of the secondary verification is for desk checking or by verifying after neural metwork training.
Further, in the step S05, details include sentence in the position in file to be measured, asking in sentence
The frequency of occurrences of topic part and sentence in file to be measured.
Further, the step S02 to step S04 records total used time when being executed.The time can intuitively reflect
Duration used is analyzed, other modes carry out more intuitive comparison, help to select distinct methods for varying environment.
The present invention can be scanned the SQL statement in engineering and static state is divided according to customized detection and analysis rule
Analysis helps the audit of even Replacing engineering Shi Jinhang SQL statement, improves the efficiency of exploitation and O&M, reduces and analyzes the time used,
Improve the accuracy rate of analysis.
Specific embodiment
The technical program is further elaborated below in conjunction with specific embodiment.
A kind of embodiment: SQL execution efficiency Static Analysis Method, comprising the following steps:
Step S01: SQL syntax file is input in JavaCC and JJTree, generates SQL resolver；
Step S02: file to be measured being input in SQL resolver and is handled, and generates SQL abstract syntax tree；
Step S03: establishing SQL syntax rule base, includes several customized syntax rule in the rule base；
Step S04: matching treatment, mark are carried out to SQL abstract syntax tree by SQL syntax rule base using rule match engine
Problematic sentence out；
Step S05: the sentence and its details of mark are exported.
The present embodiment can be scanned the SQL statement in engineering and static according to customized detection and analysis rule
Analysis helps the audit of even Replacing engineering Shi Jinhang SQL statement, improves the efficiency of exploitation and O&M.
In the present embodiment, in the step S02, treatment process includes filtering out SQL statement in file to be measured and by SQL
Sentence presses object categorization, carries out abstract processing to the SQL statement after the completion of classification, the object name is started with capitalization
English word indicates that the object includes Select and Where.
In the present embodiment, the abstract processing includes the component part that SQL syntax is abstracted using DSL, right with multilayer
The form of elephant further splits component part.
Such as in the present embodiment, expression formula object class Expression is an abstract class, there is 2 subclasses:
ValueExpression(value expression) BooleanExpression(Boolean expression), and ValueExpression has
Multiple subclass FunctionCall(function calls) ColumnExpression(field value) etc..Can thus it pass through
Expression chooses all " value expressions " and " Boolean expression ", can only be chosen by ValueExpression
All FunctionCall and ColumnExpression, without choosing Like, the BooleanExpression such as Equal.
In the present embodiment, in the step S03, the customized syntax rule of rule base is write by DSL language, if including
Dry kind of syntactically correct but low execution efficiency statement form.Example when implementing for one, such as: select * from user
Age > 20 where wherein should clearly specify the column needed in Select sentence, and do not have to " * " and obtain all column, for
Such issues that, the present embodiment writes detected rule using Groovy language: AST.select.values.find { it
instanceof AstarExpression}；In conjunction with syntax tree it is recognised that AST is an abstract syntax tree object,
AST.select is then a syntactic units in SQL, and AST.select.values is then a category of Select syntactic units
Property, indicate the list of fields chosen in select sentence, this is the array of a ValueExpression, then to word
Duan Liebiao scans for matching.Wherein the effect of xxx.find method is to find to meet expression formula in brace below and be judged as
The content of True.Herein, it instanceof AstarExpression expression type is
AstarExpression, above-mentioned such rule can match and find the SQL problem of above-mentioned influence execution efficiency.
In the present embodiment, in the step S04, rule match engine is generated by search engine and rule base.
In the present embodiment, in the step S04, matching treatment process are as follows: by the content in SQL abstract syntax tree by appearance
Frequency arranges descending, and by the content of sentence and regular storehouse matching since the head of sequence, qualified sentence is saved to set
A, ineligible preservation to set B.
In the present embodiment, it is additionally provided with secondary verification: several sentences is randomly selected from the set B, to the sentence of extraction
It is verified, sentence then marks if you have questions.
In the present embodiment, the executive mode of the secondary verification is for desk checking or by verifying after neural metwork training.
In the present embodiment, in the step S05, details include sentence in file to be measured position, in sentence
The frequency of occurrences of problematic portion and sentence in file to be measured.
In the present embodiment, the step S02 to step S04 records total used time when being executed.The time can intuitively reflect
Duration used is analyzed out, and other modes carry out more intuitive comparison, help to select distinct methods for varying environment.Repeatedly compared
Compared with the present embodiment analyzes the time used in the complete manual examination and verification of time comparison used, saves the time greater than 80 percent.
The present embodiment can be scanned the SQL statement in engineering and static according to customized detection and analysis rule
Analysis helps the audit of even Replacing engineering Shi Jinhang SQL statement, improves the efficiency of exploitation and O&M, reduces analysis institute's used time
Between, improve the accuracy rate of analysis.
It should be noted that the specific embodiment is only used for that technical solution is further described, it is not used in and limits the skill
The range of art scheme, any modifications, equivalent substitutions and improvements etc. based on this technical solution are regarded as in protection of the invention
In range.
Claims (10)
1. a kind of SQL execution efficiency Static Analysis Method, which comprises the following steps:
Step S01: SQL syntax file is input in JavaCC and JJTree, generates SQL resolver；
Step S02: file to be measured being input in SQL resolver and is handled, and generates SQL abstract syntax tree；
Step S03: establishing SQL syntax rule base, includes several customized syntax rule in the rule base；
Step S04: matching treatment, mark are carried out to SQL abstract syntax tree by SQL syntax rule base using rule match engine
Problematic sentence out；
Step S05: the sentence and its details of mark are exported.
2. a kind of SQL execution efficiency Static Analysis Method according to claim 1, which is characterized in that the step S02
In, treatment process includes filtering out SQL statement in file to be measured and by SQL statement by object categorization, after the completion of classification
SQL statement carries out abstract processing, and the object name is indicated with the English word that capitalization starts, and the object includes
Select and Where.
3. a kind of SQL execution efficiency Static Analysis Method according to claim 2, which is characterized in that at the abstract
Reason includes the component part that SQL syntax is abstracted using DSL, is further split component part with the form of multilayer subobject.
4. a kind of SQL execution efficiency Static Analysis Method according to claim 1 or 3, which is characterized in that the step
In S03, the customized syntax rule of rule base is write by DSL language, low including several syntactically correct but execution efficiency
Statement form.
5. a kind of SQL execution efficiency Static Analysis Method according to claim 4, which is characterized in that the step S04
In, rule match engine is generated by search engine and rule base.
6. a kind of SQL execution efficiency Static Analysis Method according to claim 5, which is characterized in that the step S04
In, matching treatment process are as follows: by the content in SQL abstract syntax tree by the frequency of occurrences arrange descending, since the head of sequence by
The content of sentence and regular storehouse matching, qualified sentence are saved to set A, ineligible preservation to set B.
7. a kind of SQL execution efficiency Static Analysis Method according to claim 6, which is characterized in that be additionally provided with secondary school
It tests: randomly selecting several sentences from the set B, the sentence of extraction is verified, sentence then marks if you have questions.
8. a kind of SQL execution efficiency Static Analysis Method according to claim 7, which is characterized in that the secondary verification
Executive mode be desk checking or by being verified after neural metwork training.
9. a kind of SQL execution efficiency Static Analysis Method according to claim 8, which is characterized in that the step S05
In, details include sentence in the position in file to be measured, the problems in sentence part and sentence going out in file to be measured
Existing frequency.
10. a kind of SQL execution efficiency Static Analysis Method according to claim 1, which is characterized in that the step S02
Total used time is recorded when being executed to step S04.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811447657.7A CN109669952A (en) | 2018-11-29 | 2018-11-29 | A kind of SQL execution efficiency Static Analysis Method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811447657.7A CN109669952A (en) | 2018-11-29 | 2018-11-29 | A kind of SQL execution efficiency Static Analysis Method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN109669952A true CN109669952A (en) | 2019-04-23 |
Family
ID=66143467
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811447657.7A Pending CN109669952A (en) | 2018-11-29 | 2018-11-29 | A kind of SQL execution efficiency Static Analysis Method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109669952A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112346730A (en) * | 2020-11-04 | 2021-02-09 | 星环信息科技(上海)股份有限公司 | Intermediate representation generation method, computer equipment and storage medium |
| CN113434430A (en) * | 2021-07-09 | 2021-09-24 | 中国银行股份有限公司 | SQL query statement performance detection method and device |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| KR100691261B1 (en) * | 2005-12-01 | 2007-03-12 | 한국전자통신연구원 | System and method for supporting xquery update language |
| CN101807232A (en) * | 2009-02-18 | 2010-08-18 | 牛婷芝 | Method for detecting Java source code insecure input loophole |
| CN103927473A (en) * | 2013-01-16 | 2014-07-16 | 广东电网公司信息中心 | Method, device and system for detecting source code safety of mobile intelligent terminal |
| CN108255837A (en) * | 2016-12-28 | 2018-07-06 | ***通信集团浙江有限公司 | A kind of SQL resolvers and method |
| CN108388430A (en) * | 2018-01-26 | 2018-08-10 | 珠海金山网络游戏科技有限公司 | A kind of mobile terminal data check system and method |
-
2018
- 2018-11-29 CN CN201811447657.7A patent/CN109669952A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| KR100691261B1 (en) * | 2005-12-01 | 2007-03-12 | 한국전자통신연구원 | System and method for supporting xquery update language |
| CN101807232A (en) * | 2009-02-18 | 2010-08-18 | 牛婷芝 | Method for detecting Java source code insecure input loophole |
| CN103927473A (en) * | 2013-01-16 | 2014-07-16 | 广东电网公司信息中心 | Method, device and system for detecting source code safety of mobile intelligent terminal |
| CN108255837A (en) * | 2016-12-28 | 2018-07-06 | ***通信集团浙江有限公司 | A kind of SQL resolvers and method |
| CN108388430A (en) * | 2018-01-26 | 2018-08-10 | 珠海金山网络游戏科技有限公司 | A kind of mobile terminal data check system and method |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112346730A (en) * | 2020-11-04 | 2021-02-09 | 星环信息科技(上海)股份有限公司 | Intermediate representation generation method, computer equipment and storage medium |
| CN113434430A (en) * | 2021-07-09 | 2021-09-24 | 中国银行股份有限公司 | SQL query statement performance detection method and device |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| Schäfer et al. | An empirical evaluation of using large language models for automated unit test generation | |
| CN109697162B (en) | Software defect automatic detection method based on open source code library | |
| Caliskan-Islam et al. | De-anonymizing programmers via code stylometry | |
| Bareiß et al. | Code generation tools (almost) for free? a study of few-shot, pre-trained language models on code | |
| US20240078168A1 (en) | Test Case Generation Method and Apparatus and Device | |
| CN109471889B (en) | Report accelerating method, system, computer equipment and storage medium | |
| CN111191276A (en) | Data desensitization method and device, storage medium and computer equipment | |
| Le et al. | Interactive program synthesis | |
| CN114186019A (en) | Enterprise project auditing method and device combining RPA and AI | |
| CN109840255A (en) | Reply document creation method, device, equipment and storage medium | |
| CN106326112B (en) | A kind of method and apparatus that procedure operation is corrected automatically | |
| CN109669952A (en) | A kind of SQL execution efficiency Static Analysis Method | |
| US10782942B1 (en) | Rapid onboarding of data from diverse data sources into standardized objects with parser and unit test generation | |
| Tsuchiya et al. | Interactive recovery of requirements traceability links using user feedback and configuration management logs | |
| Rai et al. | Method level text summarization for java code using nano-patterns | |
| EP3608786B1 (en) | Systems and methods of requirements chaining and applications thereof | |
| Hayase et al. | Building domain specific dictionaries of verb-object relation from source code | |
| Wang et al. | Resilience of Large Language Models for Noisy Instructions | |
| CN111881055B (en) | Simulink model design method for military software evaluation specification | |
| CN114547560A (en) | Processing method and device and electronic equipment | |
| HIGO | Dataset of Functionally Equivalent Java Methods and Its Application to Evaluating Clone Detection Tools | |
| CN114153447A (en) | Method for automatically generating AI training code | |
| Eder et al. | Selecting manual regression test cases automatically using trace link recovery and change coverage | |
| França et al. | A Systematic Review on Evaluation of Aspect Oriented Programming using Software Metrics. | |
| Silva et al. | LCCSS: A similarity metric for identifying similar test code |
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 | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20190423 |