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.
I agree with Gianluca. Using a SCALAR function as part of a join is a guarantee of slow code. See if you can rewrite the function to be an Inline Table Valued Function which returns more than one row at a time and join the function as if it were a table.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs