aravind-305595 (6/29/2010)

---

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

Query Format:
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.

Jeff Moden (6/30/2010)

---

Heh... I finally beat Grant to the punch for a change.

aravind-305595 (7/9/2010)

---

Hi, Thanks all. I thought to re-write that piece of code with suggestion given in this forum. Now I face another issue with the joins, my code looks like,
update table1 set col1 = s.col1
from table1 w inner join [SQLServer2].[database1].[dbo].[table1] s on w.col2 + ':' + w.col3 = s.col2 and w.col4 = s.col3
where w.col1 is null
and w.col2 is not null.

I am using two concatenated columns the join and the joining table is from a linked server. The table1 has around 2 million records.
The same query works in 2 minutes in SQL 2000 but it takes around 18 minutes in SQL 2008.
I dont know why the joins degrade the performance for these kind of queries in SQL 2008. Please suggest some best idea to overcome this issue. Again if I have to re-write his code, I am not sure how many more will come with this kind of scenario in the joins.