Wednesday, March 21, 2012

Optimization Required for User Defined Function

Hi,

I've an UDF which inside has two query joined by union and it 's similar to this

select * from Table1 ... (several conditions)
union
select * from Table2 ... (several conditions) (this could takes long time to run)

Since i can't write dynamic sql into UDF , i can't avoid to insert Table2 into the query but to improve permormance I've seen how costant can help me.
For Example if I change my UDF in

select * from Table1 ... (several conditions)
union
select * from Table2 Where 1=2 AND (several conditions)

Optimazer is able to skip completely the second execution, so i need to transform 1=2 into a dynamic condition for example test a field table existence.
select * from Table2 Where Exist (select * from Table3 where Field1=1)

That is why i try to write a single UDF can adapt itself to several situations using second condition only where is necessary and not always.
The problem is the dynamic condition for simple could be, wasn't recognize as costant.

For Example
select top 1 * from MyTable where (select 1)=2
select top 1 * from MyTable where 1=2

If you see the execution plan of these 2 queries you could see that the first takes more than 80% of execution time and in the second less than 20%.
Moreover the second plan use a costant scan unlike the first doesn't it.

Do anyone know a way to tell to optimizer to use a simple condition as constant ? This improve drastically my UDF performance.... :( :(

Thanks.1) why is it essential to make it a function and not procedure or view.
2) how do u find

..you could see that the first takes more than 80% of execution time and in the second less than 20%...

if u r referring to execution-plan these % values relative to the batch and does not represent an absolute value. and practically both are taking 0 sec in my machine.
3) if u use "select" in a "where" it is evaluated once for each row of the outer query hence is inefficient.

No comments:

Post a Comment