Hello,
i have a little performance problem with DB2 UDF:

i've created 2 UDFs:
EAN_128(EanCodeReq VARCHAR(4), Id bigint) returns VARCHAR(32)
and
EAN_128_CHECKSUM(EanCode VARCHAR(32)) RETURNS CHAR(1)

EAN_128 simple select into table (< 100 lines), fetches varchar value
and then adds checksum to this value. Checksum is calculated by
calling EAN_128_CHECKSUM (simple while loop, some multiplications and
additions)

My problem: EAN_128 is toooooo sloooow, or better said, calling
EAN_128 by "select EAN_128(....) from dummy" is slow. I've found out,
that the body of function (select, some ifs and checsum calculation)
tooks < 0.05 sec, but calling is expensive, it takes more than 1.5
sec.

To clarify the background:
EAN_128 is called from our ERP (java/j2ee) each time employee in
warehouse scans palette.
After scan, label with barcode is printed, and this print (in fact,
some java stuff+ean_128 generation) takes too long time.

Im java developer, so sorry for my primer, but it seems to me like DB2
"compiles" this function each time it's called. And i've also find
out, that more lines with call of another UDF is put into EAN_128, the
more time first call takes.

ie,
if i put only this line into EAN_128:
SET ret_ = '(00)' || strData || EAN_128_CHECKSUM(strData);

call takes let's say 0.25s

but if i put into EAN_128 this:
if (...) then
SET ret_ = '(00)' || strData || EAN_128_CHECKSUM(strData);
else if (...)
SET ret_ = '(01)' || strData|| EAN_128_CHECKSUM(strData);
else if (...)
SET ret_ = '(03)' || strData || EAN_128_CHECKSUM(strData);
end if;
call takes 1.4 sec

To sum up:
-EAN_128 is called only once per cca 20-30 seconds, from java code
-from my point of view it seems calling UDF within another UDF
significantly slows down UDF call

Has anobody experienced this behaviour or i've totally missed
something?

On 2/23/2010 9:34 AM, boris brinza wrote:
What version of DB2 are you using?
If it's DB2 9.7 do you use BEGIN or BEGIN ATOMIC? I.e. is the function
compiled or inlined.
Keep in mind that each and every invocation of the checksum function is
expanded if is is inlined (BEGIN ATOMIC).
So you series if IF THEN ELSE causes huge plans.
Instead you should do:
SET ret = CASE WHEN .. THEN '(00)' WHEN ... THEN '(00)' ... END ||
EAN_128_CHECKSUM(strdata);

I.e. minimize the number of nested function calls.
And CASE expressiosn ar magnitudes denser than IF THEN ELSE in inline
SQL PL

Cheers
Serge



--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab