INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

slow call of UDF in DB2

slow call of UDF in DB2

(OP)
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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close