Why is this query slow: 1

TimChapman — Sat, 24 Oct 2009 13:41:00 GMT

Since this is the first part of this series, I'll give a little background as to what I want to accomplish. At some point, you're going to run into a situation where a query on your SQL Server system is "slow". Slowness is usually ambiguous, and almost always relates to a persons reference. However, when we hear that something is slow or broken we need to check it out and see what may be the cause. So, my goal is to introduce simple to increasingly complex queries and show you the steps I like to take to diagnose what may be causing them to be "slow".

Statement 1:

SELECT *
FROM TableA a
JOIN TableB b ON a.PrimaryKeyField = b.ForeignKeyField
WHERE b.LookupValue = 3

The first thing I like to look for are the simple things. 9 times out of 10 something simple is being missed and causing poor performance. So, in the query above I first want to look for any columns that may need to be indexed. My TableA table has a clustered index (default) on the Primary Key field (named PrimaryKeyField), so single record lookups should perform seek operations. Next I want to look at the ForeignKeyField in TableB. Since I am joining on this field back to the PrimaryKeyField in TableA I want to make sure this field has a nonclustered index on it to facilitate faster lookup operations. If there is indeed a foreign key constraint defined between these two tables based on this relationship, the nonclustered index on the ForeignKeyField in TableB becomes even more important. SQL Server has to perform a lookup check to make sure the constraint is satistified anytime a record is inserted (or the ForeignKeyField is updated) in the table, so if SQL Server can use an index to perform the seek it is all the better.

Next I want to check the criteria in the WHERE statement. Note: The criteria in the JOIN operations can be thought of as "criteria", so anytime you're considering criteria you should also be considering indexes to quickly satisfy the criteria. Because I am doing a lookup on the LookupValue field, I'll want to see if an index on this field makes sense. Some indexes make sense, some do not....more on that later.

I also want to remove the SELECT * portion of the query and replace it with a specified field list. Bringing back all of the fields involved almost always results in a slower query, and can end up breaking your data layer or interface code if you're not careful.

Lastly, I want to make sure my table references are prefixed with the schema that the table belongs to. I like to do this so that SQL Server doesn't have to do the determination for me.

I plan on posting more of these in the future. If you like them and/or have any suggestions that you would like for me to go over, please let me know!

Tim

Tip of the Week : SQL Server, poor performance

Why is this query slow: 1