- Work with small result sets. In other words, limit the number of columns you select and limit the number of rows you return. Make your queries narrow (fewer columns) and shallow (fewer rows).
- Here’s a hint — avoid the use of optimizer “hints”. Hints are often misunderstood. A hint in SQL Server really is more of a mandate. A hint tells SQL Server how to behave if at all possible. If SQL Server were planning to use a shared lock but you gave it a hint to use a table lock exclusive in your hint, it would use the table lock exclusive if there was any way to do so. This would make your performance worse overall as other processes wait on your table lock to be released.
- Use sargable conditions instead of nonsargable. Sargable means “Search argument able”. Sargable arguments are more exact matches and are arguments where an index can be used. Some examples of this are using BETWEEN rather than IN. Using equals = rather than not equal <>.Instead of a query like this:
Select amount, qty from order where orderdate in (‘10/1/2012’, ‘10/2/2012’, ‘10/3/2012’)
Select amount, qty from order where orderdate between ‘10/1/2012’ and ‘10/3/2012’
The second query should use less CPU and is a more exact match. Both queries will give the same result.
- Avoid the use of <>, NOT, or !.These are nonsargable and also potentially cause the optimizer to figure out what is and then take the opposite result. If I have customers in California and Texas and I want to see the list of all of those not in California, then I could do either of the following to achieve the result. However, the second one is better.Instead of a query like this:
Select last, first from customer where state not like ‘CA’
Select last, first from customer where state = ‘TX’
- Use EXISTS instead of COUNT(*) to test for existence. COUNT(*) is very resource intensive because it counts all of the rows and is therefore scanning all of the rows. EXISTS, on the other hand, stops when it gets to any row that meets the criteria.