Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

What would be the best index(es) to add to the tables in order to make this query as fast as possible. The sd.incidents tables is about 2.5M records, problems about 8K, and changes about 120K.

select
  --bunch of fields
from
    sd.incidents a
    left join sd.problems b on a.pr_number = b.pr_number
    left join sd.problems c on b.pr_number = c.parent_pr_number and c.is_root_cause = 1
    left join sd.changes d on b.caused_by_change_number_clean = d.change_number
where
    a.severity in (1,2,3) and
    (a.pr_number is not null or a.parent_number is null)
share|improve this question
* All primary keys, obviously, and the foreign keys. * seeriy * Pw_number, Parent_number. – TomTom Jul 19 '11 at 22:04
Also, make sure you have FK constraints on a.pr_number, b.br_number and c.pr_number (if appropriate). It can speed lookups. – ta.speot.is Jul 19 '11 at 22:13
should there be 1 field with "additional columns" in the index or should these all be their own, non clustered, index? – thomas Jul 19 '11 at 22:14
1  
Depends. Given your totally trivial amount of data I would stick with separate indices. – TomTom Jul 19 '11 at 22:15
How many rows is SELECT * FROM sd.incidents WHERE severity IN (1, 2, 3)? What is the minimum edition of SQL Server you have to code against? Which fields are actually selected (it makes a difference)? – Jon Seigel Jul 19 '11 at 23:29

migrated from stackoverflow.com Feb 13 '12 at 22:05

1 Answer

up vote 1 down vote

If you have a really large number of fields in the SELECT and/or the fields are wide, you should make sure the JOIN fields are your clustered indexes as well.

Pulling a few dozen fields via a key lookup can be the most expensive part of a query like this. Clustering on your JOIN keys forgoes that step.

The only real way to know is to run the query, check the execution plans, and see where your bottlenecks are. For the WHERE clause you could make a filtered index that just includes the cluster key of that table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.