Query tuning tips

1.       If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a scan to perform this activity, instead try to use one of the following options, all of which offer better performance:

Use EXISTS or NOT EXISTS.

Use IN.

Perform a LEFT OUTER JOIN and check for a NULL condition.

2.       If you run into a situation where a WHERE clause is not sargable because of the use of a function on the right side of an equality sign (and there is no other way to rewrite the WHERE clause), consider creating an index on a computed column instead. This way, you avoid the non-sargable WHERE clause altogether, using the results of the function in your WHERE clause instead.

3.       If you find that SQL Server uses a SCAN instead of an SEEK when you use an IN or OR clause as part of your WHERE clause, even when those columns are covered by an index, consider using an index hint to force the Query Optimizer to use the index.

For example:

SELECT * FROM tblTaskProcesses WHERE nextprocess = 1 AND processid IN (8,32,45)

--takes about 3 seconds, while:

SELECT * FROM tblTaskProcesses (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45)

--returns in under a second.

4.       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. For example:

SELECT customer_number, customer_name  FROM customer

WHERE customer_number in (1000, 1001, 1002, 1003, 1004)

--Is much less efficient than this:

SELECT customer_number, customer_name  FROM customer WHERE customer_number BETWEEN 1000 and 1004

5.       Using the SUBSTRING function can force a scan instead of allowing the optimizer to use an index (assuming there is one). If the substring you are searching for does not include the first character of the column you are searching for, then a scan is performed. If possible, you should avoid using the SUBSTRING function and use the LIKE condition instead, for better performance.

6.       Where possible, avoid string concatenation in your Transact-SQL code, as it is not a fast process, contributing to overall slower performance of your application. If you need to use heavy string processing, consider creating a CLR function, which potentially will provide greater performance.

7.       If you want to boost the performance of a query that includes an AND operator in the WHERE clause, consider the following:

·         Of the search criterions in the WHERE clause, at least one of them should be based on a highly selective column that has an index.

·         If at least one of the search criterions in the WHERE clause is not highly selective, consider adding indexes to all of the columns referenced in the WHERE clause (assuming they will be selective).

·         If none of the columns in the WHERE clause are selective enough to use an index on their own, consider creating a covering/included index for the query.

8.       If you have a query that uses ORs and it not making the best use of indexes, consider rewriting it as a UNION ALL, and then testing performance.

9.       If your SELECT statement contains a HAVING clause, write your query so that the WHERE clause does most of the work (removing undesired rows) instead of the HAVING clause do the work of removing undesired rows. If the WHERE clause is used to eliminate as many of the undesired rows as possible, this means the GROUP BY and the HAVING clauses will have less work to do, boosting the overall performance of the query.

10.    The GROUP BY clause can be used with or without an aggregate function. But if you want optimum performance, don’t use the GROUP BY clause without an aggregate function. This is because you can accomplish the same end result by using the DISTINCT option instead, and it is faster.

11.    If you run into situations where perception is more important than raw performance, consider using the FAST query hint. The FAST query hint is used with the SELECT statement using this form:

·         OPTION(FAST number_of_rows)

·         where number_of_rows is the number of rows that are to be displayed as fast as possible.

12.    Avoid using variables in the WHERE clause of a query located in a batch file. The reason the indexes may not be used is because the Query Analyzer does not know the value of the variables when it selects an access method to perform the query. If you cannot avoid using variables in the WHERE clauses of batch scripts, consider using an INDEX query hint to tell the Query Optimizer to use the available indexes instead of ignoring them and performing a table scan.

13.    Non-sargable search arguments in the WHERE clause, such as “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500′” generally prevents (but not always) the query optimizer from using a useful index to perform a search. In addition, expressions that includes a function on a column, expressions that have the same column on both sides of the operator, or comparisons against a column (not a constant), are not sargable.