Optimization of query

My Query is like this..

set @.Grouptitle = @.GroupPFR

set @.GroupOrder = 5

set @.Unittype = 2

set @.MetricName = 'Product to Net Revenue %'

set @.MetricOrder = 6

insert into @.FinalData (Grouptitle,MetricName,UnitTypeID,WeekDate,WeekValue,GroupOrder,metricOrder)

select @.GroupTitle,@.MetricName,@.UnitType,f1.weekdate,

max(f1.WeekValue)/case when max(f2.WeekValue) = 0 then NULL else max(f2.WeekValue) end,

@.GroupOrder,@.MetricOrder --from @.temptable

from @.FinalData f1 inner join @.FinalData f2 on f1.weekdate = f2.weekdate

where (f1.Grouptitle = @.GroupPFR and f1.MetricName = '$ Products')

and ( f2.Grouptitle = @.GroupRevenue and f2.MetricName = 'Net Revenue')

group by f1.weekdate

There are many calculations like this in my procedure.

and It takes like 3 min to run whole procedure

now as I am doing group by..

So In Execution plan it show me that 60% of the query time is take n by SORT operation..

can any one give me any other option to do this.

Thanks

Its difficult to make any absolutes without seeing the whole query and DDL etc. However, a possible suggestion would be to use temp tables rather than table variables. You are then able to create indexes which may improve the performance of the query and SQL Server keeps distribution statistics on temp tables which may also help it select a better plan.

HTH!

|||

If I use temp table my procedure will recompile..

wont that increse the executin time?

|||Try changing your code to:

set @.Grouptitle = @.GroupPFR

set @.GroupOrder = 5

set @.Unittype = 2

set @.MetricName = 'Product to Net Revenue %'

set @.MetricOrder = 6

insert into @.FinalData (Grouptitle,MetricName,UnitTypeID,WeekDate,WeekValue,GroupOrder,metricOrder)

select @.GroupTitle,@.MetricName,@.UnitType,f1.weekdate,

max(f1.WeekValue)/case when max(f2.WeekValue) = 0 then NULL else max(f2.WeekValue) end,

@.GroupOrder,@.MetricOrder --from @.temptable

from @.FinalData f1

inner join @.FinalData f2 on f1.weekdate = f2.weekdate and f2.Grouptitle = @.GroupRevenue and f2.MetricName = 'Net Revenue'

where (f1.Grouptitle = @.GroupPFR and f1.MetricName = '$ Products')

group by f1.weekdate

|||It may well increase execution time though this may less than the gains of using the temp table. As i mentioned, its difficult to say for definite with only that code to go by.

The only way you'll be able to tell is to load test the suggestions and see which one performs best.

Good luck.
|||How can I create index on temp table|||

Just like any other table:

Code Snippet

create table #rich
(int1 int)

create index idx_rich_1
on #rich(int1)

drop table #rich

|||If i use KEEP PLAN in my query than it wont every time recompile for Temp table. Is that right?|||

I believe thats correct. Check out the following links which may be of assistance:

http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

sql