sql - Extracting WHERE clause to speed up query -

the query shown in "section 1" below takes on 2.5 hrs complete. have been tasked speeding , have question whether change made legitimate (i.e. not change result). modified code completes in < 30 mins. many thanks.

1) original query

select i.fundcd     ,i.maxdate     ,v1.infocodename parentinfocodename     ,v2.infocodename     ,fieldvalue     ,i.notformatteddecimalvalue fieldvalue     ,i.asofdate #tmp_hfri pmw.dbo.vinfocodewithhierarchy v1 inner join pmw.dbo.vinfocodewithhierarchy v2 on v1.codenode = v2.parentcodenode inner join pmw.dbo.vfundinfo on v2.infocodeid = i.codeid v1.infocodeid in (         692857         ,693600         ) 

2) saw clause in last line requires 1 of 2 values present in v1.infocodeid. v1 figured prior select of rows in v1 values of v1.infocodeid , use in query rather rows in v1 (1049 rows) in order speed full query.

3) did:

select *    #t1    pmw.dbo.vinfocodewithhierarchy v1     v1.infocodeid in (692857,693600) 

4) ran query. takes < 30mins.

select i.fundcd     ,i.maxdate     ,v1.infocodename parentinfocodename     --convert(float,replace(i.fieldvalue,'%',''))*0.01 fieldvalue,     ,v2.infocodename     ,i.notformatteddecimalvalue fieldvalue     ,i.asofdate #tmp_hfri #t1 v1 inner join #t1 v2 on v1.codenode = v2.parentcodenode inner join pmw.dbo.vfundinfo on v2.infocodeid = i.codeid v1.infocodeid in (         692857         ,693600         ) 

yes legitimate technique, in case potentially excluding rows. in first query limiting results of v1 where v1.infocodeid in (692857,693600), in second query because referencing temporary table in place of v2, limiting results here, second query equivalent to:

select  i.fundcd,         i.maxdate,         v1.infocodename parentinfocodename,         --convert(float,replace(i.fieldvalue,'%',''))*0.01 fieldvalue,         v2.infocodename,         i.notformatteddecimalvalue fieldvalue,         i.asofdate    #tmp_hfri    pmw.dbo.vinfocodewithhierarchy v1         inner join pmw.dbo.vinfocodewithhierarchy v2              on v1.codenode = v2.parentcodenode         inner join pmw.dbo.vfundinfo              on v2.infocodeid = i.codeid   v1.infocodeid in (692857 ,693600) ,     v2.infocodeid in (692857 ,693600); -- additional filter  

the reason seeing performance difference down intermediate materialisation. legitimate technique force intermediate materialisation executing smaller parts , storing result in temp table though. adam machanic has written pretty article on it, , there connect item open sql server add query hint allow users force intermediate materialisation of result set avoid need temp table.

you may still find using temp table faster, still need refer view v2:

select codenode, infocodename -- put fields need temp table #t1 pmw.dbo.vinfocodewithhierarchy v1  v1.infocodeid in (692857,693600)  select  i.fundcd,         i.maxdate,         v1.infocodename parentinfocodename,         --convert(float,replace(i.fieldvalue,'%',''))*0.01 fieldvalue,         v2.infocodename,         i.notformatteddecimalvalue fieldvalue,         i.asofdate    #tmp_hfri    #t1 v1         inner join pmw.dbo.vinfocodewithhierarchy v2              on v1.codenode = v2.parentcodenode         inner join pmw.dbo.vfundinfo              on v2.infocodeid = i.codeid; 

if still encountering performance issues (which query reduced 30 minutes guess are), need @ execution plan, identify bottlenecks , possibly create indexes.