There are multiple issues to take into account. For one thing, other indexes defined on these tables, including primary key indexes, can make a difference. Redundant indexes or the wrong indexes can sometimes hurt query performance. Also, the data distribution (i.e. statistics) can make a difference when you're dealing with millions of rows. So I can't say for sure what will work.
To check this out, I created the two tables with only the columns you listed, and loaded VS3_Stat with 500 rows, and VS3_Stat_Details with 50000 rows. When I checked the estimated execution plan in SSMS, it listed a missing index (lots of info on line about this).
The following results may not be relevant for your situation because the data distribution/statistics are different, but it can still be instructive to look at the relative costs for various indexes:
SELECT d.StatID, d.StatDetailID, d.DayViewedOn, d.Hits
FROM VS3_Stats AS s INNER JOIN VS3_Stats_Detail AS d
ON s.StatID = d.StatID
WHERE (s.CustNumber = 10221)
-- WITH YOUR INDEXES : .60088 -- table scans on both tables
-- WITH MISSING INDEX: .24997 -- index scan on VS3_Stats_Detail
-- Definition of missing index
CREATE INDEX idx01 ON VS3_Stats_Detail (StatID)
INCLUDE (StatDetailID, DayViewedOn, Hits);
With my data and the recommended index, the estimated execution plan cost was LESS THAN HALF with the recommended index.
NOTES:
1. If you're running SQL2000, with no INCLUDE option, create the index with all columns in the above index.
2. Adding indexes to a big table can slow down load/update procedures.
3. Test this with tables containing subsets of the actual data before putting it into production.
Slow Query
Posted - 05/09/2012 : 12:25:15
