mercredi 15 avril 2009 08:52
select sum(ForecastedDays) from tblForeCastedProjectPeriodValues
Where PeriodId in (Select top(@NoOfMonths) PeriodId from tblPeriod
can you give alternative solution to improve the query performance.
Is there is any alternate solution for this statement.
Toutes les réponses
mercredi 15 avril 2009 15:24What indexes (indices) do you have on both tables?
Unless you have a clustered index that covers the subquery properly, you're going always going to get a table scan of tblPeriod. With the right clustered index on tblPeriod.PeriodId, you could improve that to a range scan, but >= is one of those WHERE clause eeevils.
Try this out and see if it gives you any better performance. Without the schema and representative data to test it on, I'm shooting in the dark.
SELECT SUM(ForecastedDays)Whenever you're tuning (indexes or alternative queries), compare the query plans before and after to make sure you're moving in the right direction...
FROM tblForeCastedProjectPeriodValues f
SELECT TOP(@NoOfMonths) PeriodId
ON f.PeriodId = p.PeriodId
David Reed - MSFT - Microsoft Certified Architect|SQL Server - http://blogs.msdn.com/reedme/