If you’ve worked with SQL Server databases for any amount of time, it’s practically a given that you’ve run into slow running queries. Sometimes the reason for the slow response time is due to the load on the system, and other times it is because the query is not written to perform as efficiently as possible (I find this reason much more common). Learn how you may be able to improve performance by knowing what to look for in your query designs.
Operate on small sets
The fewer rows you are dealing with, the quicker you will find what you want. If the query optimizer sees that the number of rows in the table is sufficiently small, no indexes on that table will be used. The optimizer will realize that it is faster to scan the rows in the table to satisfy the request rather than traverse an index structure to find the rows.
Limit the columns returned
When returning data in your applications, the less data that is returned, the faster the information is transmitted over the network — this goes for the amount of rows returned, as well as the number of columns returned. This is why I am against using the SELECT * statement, especially in a production environment. In my experience with database administration and development, I have seen very few times that have warranted using a SELECT * statement. The reason for this is twofold. It doesn’t make sense to bring back columns that you are not going to be using. The second reason (which I feel is more important) is that using SELECT * can break existing code. Consider the following example.
I have an INSERT statement in my production environment. I use a SELECT * statement as a data source in my INSERT statement. This isn’t a big deal because my SourceTable has the same number of columns in it as the DestinationTable.
SELECT INTO DestinationTable
(Fld1, Fld2, Fld3)
SELECT *FROM SourceTable.
A business situation arises in which I need to add a field to my SourceTable table.
ALTER TABLE SourceTable ADD Fld4 INT
Adding this new field will break my INSERT statement, which will cause problems in my production environment.
Searching for rows
The manner in which rows are searched for in a database table will always be one of the more vital implementations in your database environment. The SQL Server query optimizer will operate much more efficiently for some WHERE statements as compared to other WHERE statements based upon how the statement is written even if the outcome of the statements is the same.
The following example uses the IN() statement to specify a series of values being searched for. For this example, assume the OrderID column as a NonClustered index.
SELECT * FROM ProductSales WHERE OrderID IN(4494, 4495, 4496)
This statement is exaactly the same as using an OR operator to specify the three values being searched for. Either statement will cause SQL Server not to use the index on the field and to cycle through the rows in the table searching for the values. Since the values used in the example are contiguous, I can use the BETWEEN operator instead. This will allow the query optimizer to effectively use the index.
SELECT * FROM ProductSales WHERE OrderID BETWEEN 4494 AND 4496
In general, most types of exclusion statements in your WHERE clause will cause SQL Server to not be able to use an index. The following are some additional examples:
<>, !, OR, NOT IN, NOT EXISTS
The manner in which LIKE statements are used also makes a difference. If you are able to specify the beginning character(s) of the statement you are searching for, you will have a better chance of your statement using an index. If you specify a wildcard(%) before any type of search string, the optimizer will be unable to use an index.
SELECT * FROM Customers WHERE LastName LIKE '%TR%'
Date searches are sometimes a little tricky to perform in the database; for instance, I have seen numerous situations where date functions are used for date searches. In the following example, the sales records are being retrieved from the SalesHistorry table for August 15, 2005:
YEAR(SaleDate) = 2005 ANDDAY(SaleDate) = 15
The functions in the WHERE clause will cause SQL Server to perform the function on every row being searched, which means the index will not be used. This is why I discourage the returns values from functions to be used as criteria in queries. The following code shows how you can rewrite the statement so that an index is used, and the results are returned in a much quicker fashion.
SaleDate >= '8/15/2005' ANDSaleDate < ‘8/16/2005'
In my next column, I plan to look at some of the more advanced techniques that you can use to optimize queries.
Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at email@example.com.
Get SQL tips in your inbox
TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!