I love it when I can make simple fixes in the database that yield great results. Here are a few T-SQL tips that gave me great gain over the years. You have two goals with any database — storage and retrieval. Optimize both with these tips.
Stop Using SELECT *!
Many people know they shouldn’t use SELECT* but don’t fully understand the impact. When you use SELECT *, you force SQL Server to go to the heap or clustered index one way or the other because this is where all of the columns are located — and you just asked for all of them. Oh, and all of the other people using SELECT * are going to the same clustered index or heap on that table, increasing contention for resources. Stay tuned for an upcoming post on indexing that will cover this in more detail. For now, just trust me. Please stop using SELECT *. You’re hurting database performance. The truth is you probably don’t need all of the columns. Let’s just say your query looks something like this:
SELECT * from customer
I’m going to put some numbers here just to help demonstrate the point. Let’s say customer has 2000 rows and 30 columns. Notice there is no WHERE clause here — so you’re accessing all 2000 rows when you run this query. Perhaps you need 25 of the 30 columns, but it’s easier to just type SELECT *. I understand.
Let’s say those five additional columns add up to 30 bytes. You’re accessing 30 bytes multiplied by the number of rows (2000) multiplied by the number of times the query is run — in other words, the number of connections / users / applications that run the query. By the way, you can drag and drop columns from the Object Explorer in the SQL Server Management Studio Tool to the query window, so you don’t even have to type the columns.
Use a WHERE Clause
The WHERE clause filters the number of rows processed and increases the chance that SQL Server can use an index. What’s the exact tipping point? Well, that’s a complex question. I’ve spent hours understanding exactly when the query would tip and use a scan instead of a seek, an index vs. the table. In my most recent study of one query with a pretty large table, it was just over 1%!! I was pretty shocked myself. As a very general rule, try to aim for a WHERE clause that yields less than 5% of the table. Of course, sometimes you need to access more than 5%, and you won’t have this option.
I Don’t Like “LIKE”
That’s just me. Well, no, it isn’t. I found more problems in queries dealing with the LIKE operator than I can count. Don’t get me wrong. LIKE is very powerful, and sometimes you need it. However, try to solve your query without the use of wildcards when you can. You reduce the chances that an index can be used for your queries. Especially if your pattern match is at the beginning of the statement, you reduce the likelihood that SQL Server can find a useful index.
Use Stored Procedures
When you use stored procedures, you give SQL Server the chance to use the same execution plan over again. SQL Server has something called the query optimizer. This internal mechanism figures out the best way to run your query on a cost based analysis. If you use ad hoc queries, SQL Server figures this out every time. For queries you will run over and over, just turn them into stored procedures. It’s easy.
If your query looks like this:
Select last, first from customer
You can turn it into a stored procedure by changing it to:
Create procedure usp_CustInfo as select last, first from customer
Use Your Indexes Wisely
Indexes only help you when you search and sort. They potentially hurt you when you insert, update, or delete. Follow these guidelines:
- Create indexes for columns that appear in your WHERE clause.
- Try to use index keys (columns that are part of the index) that are short and stable over time.
- Do not create indexes on columns that aren’t accessed in that order. Consider a filing cabinet that is organized by zip code. If you search by alphabet or topic, it really does you no good to organize by zip code. Indexes are useful when we access the data in that order. If I never access the data by zip code, I am simply wasting time to organize by zip code. Create indexes for the columns you request.
- Create indexes on columns that have high variation. It really does you no good (and in fact, does harm) to have an index on a column with very few different values. For example, an index on a column of gender yields approximately 50% of the table in a normal distribution of the population. SQL Server will disregard such an index because you are accessing a large portion of the table. You’re maintaining an index for something that will likely be unused.