I have an existing code in sql server 2000, which after migrating to sql server 2008 the performance has become poor.
The query inside the code have a select statement which uses joins inside it. The joining statement has a function call on one of the joining column. The query returns the results faster in 2000 environment but it takes several minutes in 2008 to return the results.
select col1, col2, col3 .....
from #tmp t inner join table1
on table1.col1 = t.col1
left join table2 on table1.col1 = table2.col2
left join table3 on dbo.func_logic(table1.col1) = table3.col1
where table1.col4 <> 5 -- some business logic
Does 2008 has some kind of settings that woule enable the performance of the query faster?
Note: When I looked at the query plan of 2008, the place where we have the function call goes for the Lazy pool.
In the query plan of 2000, I dont see anything like that.