problem with a query

  I have a query that joins 3 tables together. The large table has around 3.5 million rows. The others are much smaller (a few thousand rows).

  This query runs once per week (Tuesday). Index and table statistics (estimate) are taken every Saturday. The query should only take approximately 5-7 minutes to run. However, I've had weeks where it runs for almost 10 hours. The last time this happened, I rebuilt one of the secondary indexes that it was using and the query ran in 5-7 minutes once again.

  This week the query once again has taken 10 hours. I looked at the index and it is only 2 levels deep (so it doesn't appear to be unbalanced). There has been data deleted from the table since the last index rebuild, but I don't think that this should cause any problems.

  I haven't tried rebuilding the secondary index again because I want to find out what the problem is. Any ideas?

--
-Keith


Sent via Deja.com http://www.deja.com/
Before you buy.