Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Location
    Treviso (Italy)
    Posts
    17

    Unanswered: 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.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •