Sql Server Query Performance Tuning / Sql Query Optimization Tips in SQL Server

Tip 1: Always use WHERE Clause in SELECT Queries while we don’t need all the rows to be returned. This will help to narrow the return rows else it will perform a whole table scan and waste the Sql server resources with increasing thenetwork traffic. While scanning the whole it will lock the Table which may prevent other users to access the table. Tip 2: It is seen many times developers use codes like   SELECT * FROM Table WHERE LOWER(Name)='India' Instead of writing it like the below SELECT * FROM Table WHERE Name='India' Of course both the queries does the same work but 2nd one is better and retrieves rows more speedy than the first query. Because Sql Server is not case sensitive Tip 3: While running a query, the operators used with the WHERE clause directly affect the performance. The operators shown below are in their decreasing order of their performance.   = >,>=,<, <= LIKE <> Tip 4 : When we are writing queries containing NOT IN, then this is going to offer poor performance as the optimizer need to use nested tables scan to perform this activity. This can be avoided by using EXISTS or NOT EXISTS. When there is a choice to use IN or EXIST, we should go with EXIST clause for better performance. Tip 5: It is always best practice to use the Index seek while the columns are covered by an index, this will force the Query Optimizer to use the index while using IN or OR clauses as a part of our WHERE clause.  SELECT * FROM Table  WHERE Status = 1 AND ID IN (406,530,956) Takes more time than  SELECT * FROM Table (INDEX=IX_ID)  WHERE Status = 1 AND ID IN (406,530,956)

Tip 6: While we use IN, in the sql query it better to use one or more leading characters in the clause instead of using the wildcard character at the starting. SELECT * FROM Table WHERE Name LIKE 'm%' SELECT * FROM Table WHERE Name LIKE '%m' In the first query the Query optimizer is having the ability to use an index to perform the query and there by reducing the load on sql server. But in the second query, no suitable index can be created while running the query. Tip 7: While there is case to use IN or BETWEEN clause in the query, it is always advisable to use BETWEEN for better result. SELECT * FROM Table  WHERE ID BETWEEN (5000 AND 5005) Performs better than SELECT * FROM Table  WHERE ID IN (5000,5001,5002,5003,5004,5005) Tip 8: Always avoid the use of SUBSTRING function in the query. SELECT * FROM Table WHERE Name LIKE 'n%' Is much better than writing SELECT * FROM Table WHERE SUBSTRING(Name,1,1)='n' Tip 9 : The queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written. So certain things should be taken care of like Provide the least likely true expressions first in the AND. By doing this if the AND expression is false at the initial stage the clause will end immediately. So it will save execution time If all the parts of the AND expression are equally like being false then better to put the Complex expression first. So if the complex works are false then less works to be done. Tip 10: Its sometimes better to combine queries using UNION ALL instead of using many OR clauses. SELECT ID, FirstName, LastName FROM Table WHERE City = 'Wichita' or ZIP = '67201' or State= 'Kansas' The above query to use and index, it is required to have indexes on all the 3 columns. The same query can be written as SELECT ID, FirstName, LastName FROM Table WHERE City = 'Wichita' UNION ALL SELECT ID, FirstName, LastName FROM Table WHERE ZIP = '67201' UNION ALL SELECT ID, FirstName, LastName FROM Table WHERE State= 'Kansas' Both the queries will provide same results but if there is only an index on City and no indexes on the zip or state, then the first query will not use the index and a table scan is performed. But the 2nd one will use the index as the part of the query. Tip 11:  While the select statement contains a HAVING clause, its better to make the WHERE clause to do most of the works (removing the undesired rows) for the Query instead of letting the HAVING clause to do the works.  e.g. in a SELECT statement with GROUP BY and HAVING clause, things happens like first WHERE clause will select appropriate rows then GROUP BY divide them to group of rows and finally the HAVING clause have less works to perform, which will boost the performance. Tip 12: Let’s take 2 situations A query that takes 30 seconds to run, and then displays all of the required results. A query that takes 60 seconds to run, but displays the first screen full of records in less than 1 second. By looking at the above 2 situations a developer may choose to follow the 1st option, as it uses less resources and faster in performance. But actually the 2nd one is more acceptable by a DBA. An application may provide immediate feedback to the user, but actually this may not be happening at the background. We can use a hint like SELECT * FROM Table WHERE City = 'Wichita' OPTION(FAST n) where n = number of rows that we want to display as fast as possible. This hint helps to return the specified number of rows as fast as possible without bothering about the time taken by the overall query. -------------------------------------------------------------------------------------------------------------------------- SQL Tuning/SQL Optimization Techniques: 1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'. For Example: Write the query as SELECT id, first_name, last_name, age, subject FROM student_details; Instead of: SELECT * FROM student_details; 2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes. For Example: Write the query as SELECT subject, count(subject)  FROM student_details  WHERE subject != 'Science'  AND subject != 'Maths'  GROUP BY subject; Instead of: SELECT subject, count(subject)  FROM student_details  GROUP BY subject  HAVING subject!= 'Vancouver' AND subject!= 'Toronto'; 3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query. For Example: Write the query as SELECT name  FROM employee  WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)  FROM employee_details)  AND dept = 'Electronics';  Instead of: SELECT name  FROM employee WHERE salary = (SELECT MAX(salary) FROM employee_details)  AND age = (SELECT MAX(age) FROM employee_details)  AND emp_dept = 'Electronics'; 4) Use operator EXISTS, IN and table joins appropriately in your query. a) Usually IN has the slowest performance. b) IN is efficient when most of the filter criteria is in the sub-query. c) EXISTS is efficient when most of the filter criteria is in the main query. For Example: Write the query as Select * from product p  where EXISTS (select * from order_items o  where o.product_id = p.product_id) Instead of: Select * from product p  where product_id IN  (select product_id from order_items 5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship. For Example: Write the query as SELECT d.dept_id, d.dept  FROM dept d  WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept); Instead of: SELECT DISTINCT d.dept_id, d.dept  FROM dept d,employee e  WHERE e.dept = e.dept; 6) Try to use UNION ALL in place of UNION. For Example: Write the query as SELECT id, first_name  FROM student_details_class10  UNION ALL  SELECT id, first_name  FROM sports_team; Instead of: SELECT id, first_name, subject  FROM student_details_class10  UNION  SELECT id, first_name  FROM sports_team; 7) Be careful while using conditions in WHERE clause. For Example: Write the query as SELECT id, first_name, age FROM student_details WHERE age > 10; Instead of: SELECT id, first_name, age FROM student_details WHERE age != 10; Write the query as SELECT id, first_name, age  FROM student_details  WHERE first_name LIKE 'Chan%'; Instead of: SELECT id, first_name, age  FROM student_details  WHERE SUBSTR(first_name,1,3) = 'Cha'; Write the query as SELECT id, first_name, age  FROM student_details  WHERE first_name LIKE NVL ( :name, '%'); Instead of: SELECT id, first_name, age  FROM student_details  WHERE first_name = NVL ( :name, first_name); Write the query as SELECT product_id, product_name  FROM product  WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price) Instead of: SELECT product_id, product_name  FROM product  WHERE unit_price >= MAX(unit_price)  and unit_price <= MIN(unit_price) Write the query as SELECT id, name, salary  FROM employee  WHERE dept = 'Electronics'  AND location = 'Bangalore'; Instead of: SELECT id, name, salary  FROM employee  WHERE dept || location= 'ElectronicsBangalore'; Use non-column expression on one side of the query because it will be processed earlier. Write the query as SELECT id, name, salary  FROM employee  WHERE salary < 25000; Instead of: SELECT id, name, salary  FROM employee  WHERE salary + 10000 < 35000; Write the query as SELECT id, first_name, age  FROM student_details  WHERE age > 10; Instead of: SELECT id, first_name, age  FROM student_details  WHERE age NOT = 10; 8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause. For Example: Write the query as SELECT id FROM employee  WHERE name LIKE 'Ramesh%'  and location = 'Bangalore'; Instead of: SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee  WHERE name LIKE 'Ramesh%'; 9) To store large binary objects, first place them in the file system and add the file path in the database. 10) To write queries which provide efficient performance follow the general SQL standard rules. a) Use single case for all SQL verbs b) Begin all SQL verbs on a new line c) Separate all words with a single space d) Right or left aligning verbs within the initial SQL verb ----------------------------------------------------------------------------------------------------------------------------- Here are the different performance optimization tips and topics for this article: Basic query optimization fundamentals An example of Hash Match Aggregate versus Stream Aggregate The Baker’s Dozen Spotlight: how the SQL Server 2012 Columnstore index can help More information on the Columnstore index Queries using dates - what works well, what doesn’t Trying to outsmart the optimizer with NULL checks Queries that are search-argument optimized and queries that aren’t Are correlated subqueries bad? New capabilities in SQL Server 2012 - are they faster? Recursive queries versus loops APPLY and Table-valued functions versus other approaches Inserting and updating multiple rows in one procedure, using INSERT and UPDATE Inserting and updating multiple rows in one procedure, using MERGE