SQL tuning is an extensive topic in itself that deserves coverage in books dedicated to stored programming. This is because it is considered as a vital skill for those who create stored programs in MySQL or Oracle.
There are several other reasons as well, which we will discuss in this blog.
DBA Tuning of SQL Queries – Why They are Important
Take a look at some reasons why SQL tuning is so important –
- Those who are not convinced about the importance of DBA tuning need to remember a simple fact that SQL statement execution takes up the most time when stored program execution time is analyzed.
- Additionally, a database that is insufficiently tuned or not tuned at all is tremendously slow by comparison.
- An untuned database cannot scale well with the growth in data volumes. This means that although your program appears to perform acceptably well at the moment, chances are, it will face serious performance issues in the future without proper tuning.
Database Query Optimization – An Informative Example
Given below is an example that explains the necessity of SQL query tuning in overall system performance. Let us consider a query that performs a straightforward join between two tables:
FROM tx_10000 x,ty_10000 y
GROUP by x.sales_id;
As data gets collected every day, the size of the tables also increases. At first, there may not be any visible change in performance, but that will change just after some days. After about seven days, the application will become nearly inoperable if the query plan is not correctly selected by the database SQL optimizer, and that’s when the need for a DBA SQL tuning is realized.
On closer analysis, the DBA shall uncover a proportional relationship between table size and elapsed time. It is also observed that along with the worsening query performance as the tables expand, there is also an acceleration in the rate of increase.
The DBA may project the estimated time to be around twenty hours as the size of the tables approaches their peak sizes, around a million rows, which is truly a poor performance trend.
The issue and solution both seem obvious once the SQL statements used in the application are assessed, and the solution is to perform database query optimization by supporting the join with an index, which can be created in this manner:
CREATE INDEX i_ty_1000 ON ty_1000 (sales_id);
This brings a remarkable improvement in the performance trend, with over 99 percent decrease in elapsed time for the mentioned query. Furthermore, the database is over a hundred times quicker and it can now scale well with the raise in table data volumes.
Such dramatic changes cannot be achieved through hardware upgrades, stored program tuning, or any amount of server enhancement. Even if any of these were to be carried out, it wouldn’t make much of a difference in the long run.
This is because the rapid degradation would ultimately quash any performance improvements attained by other techniques. This is why DBA tuning is more important than any other mode of optimization and should be performed first.
Oracle or MySQL SQL query tuning is the most essential aspect of application performance in general, so make sure that SQL is tuned before any other sort of optimization activities are carried out.