Ways to Perform Tuning of SQL Statements
SQL statement performance tuning is very important part of database applications that allows you to save time as well as money if done properly. If you are a SQL user, you might have noticed that it’s frustrating if response time is longer than expected; it happens if the database application does not have tuned SQL queries. You can tune the SQL statements in many ways, some of which are as follows:
Perform Testing using Realistic Data
It is necessary to perform volume testing and data type testing using realistic data before bringing it in the production environment. This is the way how you make sure that the application will perform well in business scenarios.
Have Good Understanding of Application, Business and Data
It is necessary to know the business and to understand the application and the data so that you can perform fine tuning of SQL statements. Naturally, if you do not know which business the application is handling or what data it manages; it would not be possible for you to perform tuning. If you know them, you can easily identify where the modification or retrieval of the data can be avoided as the same data may be present elsewhere. Moreover, such understanding will also enable you to make changes in tables or to establish links among tables in order to identify the queries that you could write better.
Prefer Full Table Scan instead of Indexed Scan
It is great choosing full table scan instead of indexed scan. The reason behind it is indexed scan performs fast when you select a few rows only. If you select above 15% of rows from a table, indexed scan is slower as compared to full scan. The reason for this slow performance is that the indexed scan performs multiple reads for each row. The full table scan, in comparison, reads all rows in one logical read operation.
Write Simple SQL Queries
You need to prefer writing simple SQL statements instead of very complex ones. If you divide one complex query into several simple queries, the performance will be improved.
Use ORDER BY Clause for the Indexed Scan
If you specify a column in an ORDER BY clause with an index defined, the optimizer will use the indexed scan in this case. Moreover, the indexed scan is used even when the WHERE clause is missing or it does not consist of that column. Therefore, you need to analyze which scan you want, and if you prefer full table scan, make use of ‘FULL’ SQL hint.
Prefer WHERE clause instead of Having
If you use WHERE clause, it will benefit you by taking advantage of an index that is defined on the columns in the WHERE clause.
As mentioned above, tuning SQL statements is very necessary to work smoothly and in a timely manner. Above are some of the important ways that could help you tune SQL queries. Moreover, it will be helpful for the end user if the application will have tuned SQL queries.
About the author: Mark K. William is a Sr. technical writer, who has written around 50 articles on different technology sectors which is posted on authority sites & blogs. Some of his articles are based on SQL server database recovery scenarios.