Have you met with forest analysis of the SQL query population of an industrial application (for example, for optimization)?
I want to ask this question to the Khabrovsk citizens.
Modern information systems are built on various types of DBMSs and yet relational DBMSs remain the most common and used . Interesting statistics on this topic HERE and HERE .
During the development and modification of systems, the level of formalization of knowledge of analysts and developers remains small (automation of creating smart queries or taking into account a number of clear rules) and most often the resulting SQL queries are written “normally”, “as usual”, “they write in our company”, and optimization issues remain at the stage of query execution in the DBMS and the subsequent stages of optimization (in the worst case, they wait when everything starts to slow down).
The amount of manual code remains large even thoughthe presence of a large number of convenient tools to avoid their manual writing (including ORM). And it’s not always possible to use such tools, especially when it comes to very complex analytical queries, including complex data analysis. And tools like ORM are used in more or less recent projects, and only for trivial queries.
In any case, the need to optimize queries and, possibly, the structure of the DBMS is constant on an industrial basis. Even with “perfect” design this cannot be avoided since the environment is changing and the customer himself does not always know what will happen tomorrow (how the laws, the market, customers, what new ideas will appear, etc.).
When optimizing in a DBMS, a number of the same problems arise, as in optimizing code for a common programming language - searching and tracking the same code, replacing it in all places with new, optimized, etc. ... And it would be nice if all SQL queries were in one place and you could go through a simple search and replace))) But it so happened historically that most technologies do not separate the request layer into separate structures / objects / files / etc. In the best case, the requests are indeed allocated in separate files.
Option to receive all requestsmaybe, for example, an SQL logger (almost all DBMSs have either a built-in or can be screwed). But in this case, you need to choose the period for receiving all requests, for example, in a year, which takes a long time (for a regular enterprise, all the basic operations that can be ... almost :) take place in a year), and the problem of determining the parameters will remain open ...
Here, on Habr Internet in Russian. and English. languages have a lot of information on query optimization and the DBMS structure separately, but there are really not enough materials on the topic of analyzing all queries for subsequent optimization. And as for the more recommendations of analytical, automated tools for this, I did not find ...
Tell me, Khabrovsk residents, have you met with a comprehensive analysis of the entire population of SQL-queries of the application?
In my opinion there isThere are three main reasons for a comprehensive analysis of the structure of SQL queries :
1. code optimization : highlighting duplicate code, replacing duplicate code during its optimization, issuing various automatic tips for improving the code structure.
2. code refactoring (optimization in this case is not a mandatory effect).
3. scientific interest , for example, analysis of the SQL forests of industrial systems for subsequent research, for example, simulation of the SQL query forest to analyze new algorithms for optimizing the operation of a DBMS.
In my opinion, the availability of such an analysis would significantly simplify the work of many programmers and some scientists working on optimization algorithms in the DBMS.
Modern information systems are built on various types of DBMSs and yet relational DBMSs remain the most common and used . Interesting statistics on this topic HERE and HERE .
During the development and modification of systems, the level of formalization of knowledge of analysts and developers remains small (automation of creating smart queries or taking into account a number of clear rules) and most often the resulting SQL queries are written “normally”, “as usual”, “they write in our company”, and optimization issues remain at the stage of query execution in the DBMS and the subsequent stages of optimization (in the worst case, they wait when everything starts to slow down).
The amount of manual code remains large even thoughthe presence of a large number of convenient tools to avoid their manual writing (including ORM). And it’s not always possible to use such tools, especially when it comes to very complex analytical queries, including complex data analysis. And tools like ORM are used in more or less recent projects, and only for trivial queries.
In any case, the need to optimize queries and, possibly, the structure of the DBMS is constant on an industrial basis. Even with “perfect” design this cannot be avoided since the environment is changing and the customer himself does not always know what will happen tomorrow (how the laws, the market, customers, what new ideas will appear, etc.).
When optimizing in a DBMS, a number of the same problems arise, as in optimizing code for a common programming language - searching and tracking the same code, replacing it in all places with new, optimized, etc. ... And it would be nice if all SQL queries were in one place and you could go through a simple search and replace))) But it so happened historically that most technologies do not separate the request layer into separate structures / objects / files / etc. In the best case, the requests are indeed allocated in separate files.
Option to receive all requestsmaybe, for example, an SQL logger (almost all DBMSs have either a built-in or can be screwed). But in this case, you need to choose the period for receiving all requests, for example, in a year, which takes a long time (for a regular enterprise, all the basic operations that can be ... almost :) take place in a year), and the problem of determining the parameters will remain open ...
Here, on Habr Internet in Russian. and English. languages have a lot of information on query optimization and the DBMS structure separately, but there are really not enough materials on the topic of analyzing all queries for subsequent optimization. And as for the more recommendations of analytical, automated tools for this, I did not find ...
Tell me, Khabrovsk residents, have you met with a comprehensive analysis of the entire population of SQL-queries of the application?
In my opinion there isThere are three main reasons for a comprehensive analysis of the structure of SQL queries :
1. code optimization : highlighting duplicate code, replacing duplicate code during its optimization, issuing various automatic tips for improving the code structure.
2. code refactoring (optimization in this case is not a mandatory effect).
3. scientific interest , for example, analysis of the SQL forests of industrial systems for subsequent research, for example, simulation of the SQL query forest to analyze new algorithms for optimizing the operation of a DBMS.
In my opinion, the availability of such an analysis would significantly simplify the work of many programmers and some scientists working on optimization algorithms in the DBMS.
Only registered users can participate in the survey. Please come in.
Have you met with a comprehensive analysis of the entire population of SQL queries of a large application?
- 2.1% Yes, I used automatic optimization tools (please write about them in the comments) 2
- 22.8% Yes, but only analytical methods of optimization ... (please write about them in the comments) 21
- 64.1% No 59
- 16.3% Difficult to answer 15