Tips to Speed up SQL Server database queries

by Tim Chapman, TechRepublic

Close

Please login or sign up to clip this article.
E-mail
Password

Remember me

Log in | Sign up | Why join Builder AU's community? See the benefits.

Please login or sign up to clip this article.
E-mail
Password

Remember me

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 optimiser sees that the number of rows in the table is sufficiently small, no indexes on that table will be used. The optimiser will realise 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 optimiser 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 optimiser 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 optimiser will be unable to use an index.

SELECT * FROM Customers WHERE LastName LIKE '%TR%'

Date searches
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:


SELECT SaleID
FROM SalesHistory
WHERE
MONTH(SaleDate) = 8 AND
YEAR(SaleDate) = 2005 AND
DAY(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.


SELECT SaleID
FROM SalesHistory
WHERE
SaleDate >= '8/15/2005' AND SaleDate < '8/16/2005'