Slow Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Slow Query

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2012-05-09 : 12:25:15

The following query takes 25-30 seconds to return all results.

Here's the query:
SELECT d.StatDetailID, d.StatID, 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)

VS3_Stats has a nonclustered index on Custnumber.
VS3_Stats_Detail has a nonclustered index on StatID.
There are 6 million records in VS3_Stats_Detail.

Should it take 25-30 seconds to pull the results? If so, is there a better way to make this much faster? Thanks.

RL
Starting Member

15 Posts

Posted - 2012-05-09 : 14:24:05

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources