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

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).

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

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:
(@ is the statement terminator.)

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

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?