Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    27

    Unanswered: UDF Performance Imlication

    Hi,

    Are there any performance implications of using UDFs in sql? If check the access plan for the same sql with and without UDFs , it shows a difference of around 1000 timerons.

    So does that mean , the UDF comes with extra overhead?
    Thanks
    Rajesh

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What kind of UDFs are you talking about - LANGUAGE SQL or external ones?

    A LANGUAGE SQL UDF is compiled into the SQL statement in which it is used. So it is like inlining code. If the UDF is defined properly, there will be no difference (except for the marginal overhead needed to resolve the UDF during statement compilation) in terms of timerons and query execution. Hence, give us some details like: the SELECT statements, CREATE FUNCTION statement and also some information on your environment (DB2 version and platform).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2007
    Posts
    27
    The UDF is a SQL scalar UDF which queries a table and returns a value.
    The structure is as below.

    Create function test(.....)
    returns decimal(24,4)
    language sql
    begin atomic set var = select <col> from <table name>(ll give single column value)
    return var;


    Select col1,col2,test(...),.....from table a join table b.....

    I am using db2 ver 9 on linux.
    Thanks,
    Rajesh

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The BEGIN ATOMIC ... END block is a problem. You hinder the optimizer to optimize the overall statement because of this block. (Of course, the optimizer is getting smarter about this by the day, but still you break with the relational model and that makes things unnecessarily hard.)

    Try this:
    Code:
    CREATE FUNCTION test(...)
       RETURNS DECIMAL(24, 4)
       LANGUAGE SQL
       DETERMINISTIC
       READS SQL DATA
       RETURN <col> FROM <table name>
    @
    (@ is the statement terminator.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Nov 2007
    Posts
    27
    Thanks..for the info..But what if we had some logic and it cant be done in the single return statement? What can be done in that case..
    Like function for date format... Here we need to first check whats the required format and accordinly process it..

    Thanks
    Rajesh

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The statement "can't be done in a single return statement" is wrong. I have yet to see something where procedural SQL is a must. Sure, there are things that work better (performance-wise) with procedural SQL and others better with pure relational constructs.) Just because it may not be obvious or because humans can better follow procedural code does not imply that functional/relational programming can't solve the problem. Tell us exactly what the requirements are, and we can come up with a way to write the function that is in non-procedural SQL.

    Anyway, you were comparing a UDF to a subselect directly inside the query. If you bring up the argument of more complex functions, your comparison falls apart, doesn't it?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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