DBA

Question

 

select sum(ForecastedDays) from tblForeCastedProjectPeriodValues

Where PeriodId in (Select top(@NoOfMonths) PeriodId from tblPeriod

where PeriodId>=@PeriodId)

 can you give alternative solution to improve the query performance.

Is there is any alternate solution for this statement.

---

D.Shiva

Answer 1

What 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)
FROM tblForeCastedProjectPeriodValues f
INNER JOIN
(
     SELECT TOP(@NoOfMonths) PeriodId
     FROM tblPeriod
     WHERE PeriodId>=@PeriodId
) p
     ON f.PeriodId = p.PeriodId

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...

---

David Reed - MSFT - Microsoft Certified Architect|SQL Server - http://blogs.msdn.com/reedme/