Tags
26 performance tuning questions and solutions, Avoid SQL Server functions in the WHERE clause for Performance, Avoid Using Function in WHERE Clause – Scan to Seek, Avoid Using Functions in WHERE Clause tutorial and example, Functions in the WHERE Clause, How to avoid convert function in the where clause, How to tune SQL queries, Increase SQL Server performance avoid using functions in Where Clause, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, sql - How do I avoid functions like UPPER in where clause, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Server 2008 Query Performance Tuning Distilled, SQL SERVER Interview questions, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL Tuning Overview, SQL Where Clause Performance, T-SQL Best Practices - Don't Use Scalar Value Functions in Where Clause, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, WHERE Clause Functions: A Word of Caution
SQL Performance and Optimization – How to write optimized queries
In this post we will talk about some precautions we can take while writing queries. Precaution is always better than cure. If we can take care of following things then we can definitely write queries which are optimizer friendly. Some of the points are-
1. Where Clause
Keep your columns in where clause clean. Do not put any functions around columns in the where clause. If you put any functions around the columns in the where clause your query will get a performance hit. Optimizer will give you a scan instead of a seek. And when the table grows people will start complaining that the query is slow. https://msbiskills.com/2015/06/19/avoid-using-function-in-where-clause-why/
2. Join
Keep your columns in join clause clean. Do not put any functions around columns on join conditions.
3. DataTypes
When ever you are joining two tables then always use columns with same datatypes in the joins clause. Also use same data type of scalars while comparison with columns in the where clause. If you don’t the SQL Server will up cast the lower data type to the upper one and you will get an implicit conversion, which is bad. For details please check https://msbiskills.com/2015/06/26/implicit-conversions-are-bad-for-performances-why/
4. Join Predicate
Always have a join predicate, other wise you will end up with cross joining two table and you will get an warning in the execution plan
5. Try to avoid Order by
Try to avoid an order clause on columns that don’t have indexes. Otherwise we will have to pay for Sort operator in the execution plan. If possible use Top with order by. Top with order by is fine. After the top operator is satisfied it stops the execution.
6. Exists
Always use exists if you are not fetching any columns from the right hand side table. Don’t use IN, they are bad for performance. Exists are good for performance as they use Semi joins internally.
7. Like
If you are using like operator to match string then use like ‘Pawan%’ rather than ‘%Pawan%’, this will give us a scan. Where as ‘Pawan% can give index seek which is great. For details click https://msbiskills.com/2015/06/23/like-is-sargable-how-to-write-better-where-clause-ii-lets-check/
7. SELECT *
Don’t ever do select * when fetching data from physical table.
For details please click https://msbiskills.com/2015/06/12/dont-ever-do-select-its-really-a-bad-idea-why/
8. Loops, cursors
Don’t use loops or cursors, Even loops are also cursors. You always wanted to insert bunch of rows
9. Nested Views,Triggers
Always try not to nest views and trigger because after a certain level you don’t know what’s going on in your system.
10. Which one is better? Equal to (=), IN, Between, etc.
a. = is better than not equal to (!=)
b. Between is better than IN
c. IN is better than NOT IN
11. Cross Apply
Try to use Cross apply(Corelation) rather than using joins, almost every time it will give you Nested Loop join.
I hope you have enjoyed the article. In the next post I will explain which one you should use – Temp table or table variable for performance ?. Thanks for reading !
-Pawan Khowal
MSBISkills.com
Improving my SQL BI Skills 