Introduction: This document list the ways of improving the performance of T-SQL. It has few tips and techniques that we used in our live project.
Query tuning:
- If your SQL Server database is not configured to be case sensitive, you don’t need to use LOWER or UPPER to force the case of text to be equal for a comparison to be performed; e.g.
SELECT column_name FROM table_name
WHERE LOWER (column_name) = ‘name’ (Not preferred)
SELECT column_name FROM table_name
WHERE column_name = ‘name’ (Preferred)
Both the queries will fetch same result unless SQL Server database is not configured to be case sensitive. To check case sensitivity see here.
- Use ‘= ‘ as much as possible, and ‘<>’ as least as possible:
Operators at the top will produce results faster than those listed at the bottom.
- =
- >, >=, <, <=
- LIKE
- <>
- Avoid using AUTO_SHRINK:
Set the AUTO_SHRINK database option to OFF. Turning it, ON may fragment your indexes. If you know that the space that you are reclaiming will not be needed in the future, you can reclaim the space by manually shrinking the database.
- Stop using “*” in query:
It is advisable to use column names rather than ‘*’ in select query. “*” may cause index scan or avoid the index altogether.
- Avoid using IN clause:
In some circumstances, you would be better to spell IN as EXISTS (especially when preceded by NOT.) The problem is that IN and EXISTS handle NULL values differently.
When dealing with null values ‘NOT IN’ won’t give same result as ‘NOT EXISTS’. Check here.
- Avoid implicit Conversion:
Implicit conversion can cause index scan. Avoid any implicit conversions and use proper data type to avoid it.
- Avoid use of SELECT COUNT(*):
There is an alternate way to get the count of rows in a particular table. This will improve the performance in case the table contains huge number of rows.
Select count (*) from <table_name> (Not preferred)
SELECT rows FROM sysindexes WHERE id = OBJECT_ID (”) AND indid < 2 (Preferred)
- Avoid using HAVING clause:
The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.
- Avoid using the DISTINCT clause:
The use of DISTINCT clause sorts the result, which results into performance degradation. You should use this clause only when it is necessary.
- Try to use UNION ALL statement instead of UNION:
The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether they exist or not.
- Avoid use of “NOT IN” Clause:
If you have a query that uses NOT IN then try following options for better performance:
- NOT EXIST
- Perform a LEFT OUTER JOIN and check for a NULL condition
- Try to replace IN with BETWEEN:
When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is much more efficient.
Example:
WHERE customer_number in (1000, 1001, 1002, 1003, 1004) (Not preferred)
WHERE customer_number BETWEEN 1000 and 1004 (Preferred)
- Use ‘is null or‘ over ‘isnull ()’:
Operators are normally faster than functions. In this case use of ‘is null or‘will fetch the results faster than ‘isnull ()’ function. E.g.
Let @in_name be the input variable.
SELECT column_names FROM table_name
WHERE isnull (@in_name, column_name) = column_name) (Not preferred)
SELECT column_names FROM table_name
WHERE (@in_name is null or column_name=@in_name) (Preferred)
- In case of insufficient indexes, use “UNION ALL” clause and not “OR” clause:
A query with one or more “OR” clauses can sometimes be rewritten as a series of queries that are combined with a “UNION ALL” statement to boost the performance of the query.
Example:
WHERE dept = ‘retx’ or city = ‘karnal’ or division = ‘adidas’
(Not Preferred because this query has three separate conditions in the WHERE clause. In order for this query to use an index, then there must be an index on all three columns found in the WHERE clause)
SELECT employeeID, firstname, lastname FROM names WHERE dept = ‘retx’
UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE city = ‘karnal’
UNION ALL
SELECT employeeID, firstname, lastname FROM names WHERE division = ‘adidas’
(Preferred, because if there is only an index on dept, but not the other columns in the WHERE clause, then the first version will not use any index and a table scan must be performed. However, in the second version of the query will use the index for part of the query, but not for the entire query)
- Rearrange where clause conditions:
If you have a WHERE clause that includes expressions connected by two or more AND operators, SQL Server will evaluate them from left to right in the order they are written. Because of this, you may want to consider one of the following when using AND:
- Locate the least likely true AND expression first. This way, if the AND expression is false, the clause will end immediately, saving time.
- If both parts of an AND expression are equally likely being false, put the least complex AND expression first. This way, if it is false, less work will have to be done to evaluate the expression.
- Avoid sorting of the data:
Sorting operation consumes more resources. Sorting often occurs when any of the following Transact-SQL statements are executed:
- ORDER BY
- GROUP BY
- SELECT DISTINCT
- UNION
- CREATE INDEX (generally not as critical as happens much less often)
In many cases, these commands cannot be avoided. On the other hand, there are few ways that sorting overhead can be reduced. These include:
- Keep the number of rows to be sorted to a minimum. Do this by only returning those rows that absolutely need to be sorted.
- Keep the number of columns to be sorted to the least. In other words, do not sort more columns that required.
- Keep the width (physical size) of the columns to be sorted to a minimum.
- Sort column with number datatypes instead of character datatypes
- Try to avoid WHERE clauses that are non-sargable:
The term “sargable” (which is in effect a made-up word) comes from the pseudo-acronym “SARG”, which stands for “Search ARGUMENT,” which refers to a WHERE clause that compares a column to a constant value. If a WHERE clause is sargable, this means that it can take advantage of an index (assuming one is available).
Example:
WHERE SUBSTRING (login, 1, 1) = ‘infosys’
(Non-sargable so not preferred)
WHERE login like ‘infosys%’
(Sargable so preferred)
WHERE DATEDIFF (yy, datofbirth, GETDATE ()) > 21
(Non-sargable so not preferred)
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
(Sargable because the fucntion is not applied directly on column so preferred)
WHERE NOT column_name > 5
(Non-sargable so not preferred)
WHERE column_name <= 5
(Sargable so preferred)
WHERE column_name LIKE ‘%m’
(Non-sargable so not preferred)
WHERE column_name LIKE ‘m%’
(Sargable so preferred) If you use a leading character in your LIKE clause, then the Query Optimizer has the ability to potentially use an index to perform the query, speeding performance and reducing the load on SQL Server.
- In a WHERE clause, the various operands used directly affect how fast a query is run. The operand at the top will produce results faster than those listed at the bottom:
- A single literal used by itself on one side of an operator.
- A single column name used by itself on one side of an operator.
- a single parameter used by itself on one side of an operator.
- A multi-operand expression on one side of an operator.
- A single exact number on one side of an operator.
- Other numeric number (other than exact), date, and time.
- Character data, NULLs.
Procedure Tuning:
- Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement:
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement. Use NOCOUNT at the beginning of all your Stored Procedures, Triggers and Functions. - Avoid using SQL Server cursors:
Drawbacks:
- They generally use many SQL Server resources and hit the performance.
- Using cursors can reduce concurrency and lead to unnecessary locking and blocking
Alternatives:
- Use Temp Tables
- Temp Variables
- Try using ‘temp variable’ or ‘temp tables’ to avoid the main table reference more than once:
If a particular set of data is fetched from joining more than one tables and this set of data is needed in more than one query than use temp tables or temp variables instead of referring main tables repeatedly.
Temporary tables or variables keeps the data into a separate area reducing main table scan and avoiding locks.
Cursor Tuning:
- Try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor:
- Generally, a FORWARD-ONLY cursor is the most performant, followed by DYNAMIC, KEYSET, and finally STATIC, which is generally the least performant.
- When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. e.g.
DECLARE cursor_name CURSOR FAST_FORWARD FOR select_statement
- When you are done using a cursor, don’t just CLOSE it, you must also DEALLOCATE it:
Deallocation is required to free up the SQL Server resources used by the cursor.e.g.
Example: Close cursor_name
Deallocate cursor_name
