10. Don't do negative searches
Take the simple query SELECT * FROM Customers WHERE RegionID <> 3. You can't use an index with this query because it's a negative search that has to be compared row by row with a table scan. If you need to do something like this, you may find it performs much better if you rewrite the query to use the index. This query can easily be rewritten like this:
SELECT * FROM Customers WHERE RegionID < 3 UNION ALL SELECT * FROM Customers WHERE RegionID
This query will use an index, so if your data set is large it could greatly outperform the table scan version. Of course, nothing is ever that easy, right? It could also perform worse, so test this before you implement it. There are too many factors involved for me to tell you that it will work 100 percent of the time. Finally, I realize this query breaks the "no double dipping" tip from the last article, but that goes to show there are no hard and fast rules. Though we're double dipping here, we're doing it to avoid a costly table scan.
OK, there you go. You won't be able to apply all of these tips all of the time, but if you keep them in mind you'll find yourself using them as solutions to some of your biggest issues. The most important thing to remember is not to take anything I say as the gospel and implement it because I said so. Test everything in your environment, then test it again. The same solutions won't work in every situation. But these are tactics I use all the time when addressing poor performance, and they have all served me well time and again.
Go forth and test, and feel free to send me emails with your success stories. I love hearing when people are able to improve their processes with simple techniques.
Sign up for CIO Asia eNewsletters.