Optimization of database query
View Patent ↗Described herein is a technology for facilitating optimization of database queries is described herein. In some implementations, a first query is received. At least one scenario for optimization is automatically recognized by detecting specific syntax in the query. A second query is then automatically generated by re-writing the first query using a pre-defined operation corresponding to the recognized scenario.
1. A method of database query optimization, comprising:
(a) receiving a first query via a user interface presented at an output device;
(b) automatically recognizing, by a computer coupled to the output device, at least one scenario for optimization by detecting specific syntax in the first query;
(c) automatically generating, by the computer, a second query by re-writing the first query using a pre-defined operation corresponding to the recognized scenario, wherein the pre-defined operation includes re-ordering “Inner Join” statements in the first query by placing tables at a same business level in adjacent positions; and
(d) presenting, via the user interface, the second query for user evaluation.
2. The method of claim 1 wherein the first and second queries comprise Structured Query Language (SQL) queries.
3. The method of claim 1 wherein the first query comprises a structured object generated by a query analyzer.
4. The method of claim 1 wherein the scenario comprises a “Join” scenario.
5. The method of claim 4 wherein the pre-defined operation comprises changing a “Join” type of the first query.
6. The method of claim 5 wherein changing the “Join” type comprises converting the first query from an “Inner Join” form to an equivalent “Outer Join” form.
7. The method of claim 5 wherein changing the “Join” type comprises converting the first query from an “Outer Join” form to an equivalent “Inner Join” form.
8. The method of claim 4 wherein the pre-defined operation comprises re-arranging “Join” statements in the first query.
9. The method of claim 8 wherein re-arranging the “Join” statements comprises ordering “Inner Join” statements before “Left Outer Join” statements.
10. The method of claim 4 wherein the pre-defined operation comprises changing one or more conditions in a “Join” statement.
11. The method of claim 4 wherein the pre-defined operation comprises converting the first query from a circular form to an equivalent non-circular form.
12. The method of claim 1 wherein the scenario comprises a “Dynamic SQL” scenario.
13. The method of claim 12 wherein the pre-defined operation comprises converting the first query from a dynamic SQL form to an equivalent non-dynamic SQL form.
14. The method of claim 12 wherein the pre-defined operation comprises separating the first query into two or more parts using a temporary data structure.
15. The method of claim 12 wherein the pre-defined operation comprises separating data dependencies in the first query into two or more different store procedures.
16. The method of claim 1 wherein the scenario comprises a “Sub-query” scenario.
17. The method of claim 16 wherein the pre-defined operation comprises moving a “Where” clause out of or into a sub-query statement of the first query.
18. The method of claim 1 further comprises generating one or more advices based on the first query.
19. A non-transitory computer-readable medium having stored thereon program code, the program code executable by a computer to:
receive a first query;
automatically recognize, at least one scenario for optimization by detecting specific syntax in the first query; and
automatically generate a second query by re-writing the first query using a pre-defined operation corresponding to the recognized scenario, wherein the pre-defined operation includes re-ordering “Inner Join” statements in the first query by placing tables at a same business level in adjacent positions.
20. A system comprising:
a non-transitory memory device for storing computer readable program code; and
a processor in communication with the memory device, the processor being operative with the computer readable program code to:
receive a first query;
automatically recognize, at least one scenario for optimization by detecting specific syntax in the first query; and
automatically generate a second query by re-writing the first query using a pre-defined operation corresponding to the recognized scenario, wherein the pre-defined operation includes re-ordering “Inner Join” statements in the first query by placing tables at a same business level in adjacent positions.