Tuning the SQL queries will result in positive impact on the performance of database.
What is Query Execution Plan?
Query execution plan can be defined as the list which contains the details of a particular process done by the RDBMS. This plan contains the details about indexes and joins processed by the SQL query. The estimated resource cost to process a particular SQL query is also defined in the query execution plan. Understanding the executional plan of a query is highly important to efficiently fine tune the database queries.
- Do you Know: SQL Clustered & Non-clustered Index
The SQL queries can be tuned by following these steps.
Reduce the number of rows returned by a query
This step is very clear. The number of rows returned by the query is inversely proportional to the efficiency of database.
Removing Unnecessary Columns and Tables
The less unused space in the database will increase the efficiency of the query. The GROUP BY statement in SQL is better than the DISTINCT statement. This is done because the sort performed by GROUP BY statement finds the duplicate data in the early query process when compared to DISTINCT. The DISTINCT statement finds the duplicate data only in the last step. The removing of unnecessary columns and tables will result in less duplicate data so it will obviously improve the performance of the query.
Using “hints” might help in improving the performance of the database. Hints are separate syntax which can be included in the SQL. Hints direct the query optimizer to perform a certain action which will result in fine tuning the database. The use of certain indexes are also mentioned using hints.
Using SQL Query Optimizers
Query optimizers present in a database have a dedicated operations. Utilizing the perfect query optimizer for your operation will result in database fine tuning. The following are the important points to remember while using a query optimizer.
- Reduced Database Statistics
Cost based query optimizers work on the basis of database statistics, reduced database will affect the performance of query optimizers. Rule based optimizers are highly effective in the case of reduced database statistics.
- Taking care of Order of Predicates
The knowledge of optimizer’s stand about order of predicate inside a WHERE clause of a account is necessary. The effects made by the order of predicate is also evaluated by the query optimizer. For example: if we have a SQL statement which says WHERE website_name = “sheahealth.com” then there will be a predicate which compares the column “website_name”. If the SQL statements is WHERE website_name = “sheahealth.com” AND website_topic = “health” then the number of predicates is two. The number of predicate used in a query should be lowered to fine tune the database. In other words, a predicate should reduce the rows evaluated by the optimizer.
- Order of Tables
Similar to predicate, the order of tables will have maximum effect on the efficiency of the query. The order of a table present in a database can be found in JOIN and FROM clause. The best option available to RDBMS is to select the most selective table. This process will remove the rows from the resultant set which enables the query process to be more efficient.
- Rewriting Query Process
The developer should keep tabs on the process which queries are rewritten by the optimizer. The rewritten queries should be more efficient when compared to the former queries. Optimizers will rewrite the subqueries into the equivalent joins of the SQL. Some DBMS have options which can enable the query rewriting process.
Tuning of SQL Queries to Increase the Performance of Indexes
The performance of SQL queries depend heavily on the performance of indexes. So indexes are tuned well to provide maximum performances.
The processes mentioned above are only basic methods which can be used to tune the database. However the situation in fine tuning the database might be different and it is up to the SQL developer to choose his/her way to improve the query performance.