slow UDF calls (db2v9)

jerzy.burzek · #1 (**permalink**) 02-22-10, 07:21

Hello,

i have a little performance problem with SQL UDF:

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

EAN_128 is simple select into table (few hundreds lines) and then addition of checksum to this value. checksum is calculated by calling UDF 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(....)" is slow. I've found out, that function itself tooks < 0.05 sec, but calling is expensive, it takes more than 1.5 sec, and it's really annoying.

So my question: How to speed up calling of SQL UDF (precompiling or somthinge like this kind of stuff?)

Thaks in advance
Boris

stolze · #2 (**permalink**) 02-22-10, 07:49

Can you show us the code?

The call/invocation of the UDF is not a problem, especially if it is defined as LANGUAGE SQL. The function is compiled into your SQL statement. So if you use procedural logic (BEGIN ... END) instead of set-orientation, you prevent the DB2 optimizer from optimizing your statement overall and force it to a procedural approach.

jerzy.burzek · #3 (**permalink**) 02-22-10, 08:33

hello, code is here:
function FILL_TO_LEN is missing, but it simply use REPEAT to fill-up varchar to desired length...

Code:

CREATE FUNCTION ESO.EAN_128_CHECKSUM(EanCode VARCHAR(32))
	RETURNS CHAR(1)
	LANGUAGE SQL
	DETERMINISTIC

BEGIN ATOMIC
	DECLARE iter_ int;
	DECLARE checkSum_ int;
	DECLARE multi_ int;
	
	IF (EanCode is null) THEN
		signal sqlstate '75004' set message_text = 'EanCode is null';
	END IF;
	
	SET checkSum_ = 0;
	SET multi_ = 3;
	SET iter_ = length(EanCode);
	while iter_ != 0 do
		SET checkSum_ = checkSum_ + (multi_ * + int(substr(EanCode, iter_, 1)));
		IF (multi_ = 3) THEN
			SET multi_ = 1;	
		ELSE
			SET multi_ = 3;
		END IF;
		
		SET iter_ = iter_ - 1;
	end while;
	
	
	SET checkSum_ = MOD(checkSum_, 10);
	IF (checkSum_ = 0) THEN
		SET checkSum_ = 10;
	END IF;
	RETURN CHAR(10 - checkSum_);
	
END@


CREATE FUNCTION ESO.EAN_128(EanCodeReq VARCHAR(4), Id bigint)
	RETURNS VARCHAR(32)
	LANGUAGE SQL
	NOT DETERMINISTIC
	NO EXTERNAL ACTION
	READS SQL DATA
BEGIN ATOMIC

	DECLARE ret_ varchar(32);
	DECLARE strData_ varchar(18);
	DECLARE rowCount_ int;
	DECLARE pocetMiest_ int;
	

	IF Id is null or EanCodeReq is null THEN
		signal sqlstate '75004' set message_text = 'Id/EanCodeReq is null';
	END IF;
	
	SET rowCount_ = (select count(ESO_ID) from NETTOSKO where eso_id=Id);
	
	IF (EanCodeReq = '00') THEN
		SET strData_ = (select ltrim(rtrim(char(Id))) from dummy);
		SET ret_ = '(00)' || ESO_FILL_TO_LEN(strData_, 17, '0', 'L') || EAN_128_CHECKSUM(strData_);
	ELSEIF (EanCodeReq = '01') THEN
		IF (rowCount_ != 1) THEN
			signal sqlstate '75004' set message_text = 'NETTOSKO, must exists exactly 1 row with given id';
		END IF;
		SET strData_ = (select ltrim(rtrim(ZBOZI_OCV)) from nettosko where eso_id=Id);
		SET ret_ = '(01)' || ESO_FILL_TO_LEN(strData_, 13, '0', 'L') || EAN_128_CHECKSUM(strData_);
	END IF;
	
	
	RETURN ret_;

END@

tonkuma · #4 (**permalink**) 02-22-10, 09:33

1) What is your platform?

2)

Quote:

... function itself tooks < 0.05 sec, ...

How did you found that?

3) One possibility may be that "select count(ESO_ID) from NETTOSKO where eso_id=Id" took time.
Do you have an index on eso_id of NETTOSKO?

jerzy.burzek · #5 (**permalink**) 02-22-10, 09:45

1) DB2/LINUXX8664 9.1.0
2) DB2 monitor
3) NETTOSKO is not problem (i am pretty sure, number of lines is < 100 and ESO_ID is PK)

by Db2Monitor it seems, that preparation of UDF is problem, here is line for first run:
select ean_128('00', 1233) from dummy
exec time 0,0094s, execs 1, preps 1

if i re-run select with same values, it fast as hell (cache? pre-compiled?)
select ean_128('00', 1233) from dummy
exec time 0,0065s, execs 1, preps 0

(see preps count)

so clear exec time is always like 0.00xx s, but overall call time is significantly different for 1st call and next calls with same input values (>1.5s vs <0.3s)

stolze · #6 (**permalink**) 02-22-10, 14:01

So you have a rather complex, procedural function and that is used in set-oriented context. If you can, you should write it as non-procedural function. (And I have yet to see a function where this is not possible.) This may give you better performance because DB2 can optimize the overall statement instead of feeding every single row through the procedural steps.

p.s: If I take your 0.05s and multiple this by 100 rows, I already have 5s total. So your 1.5s isn't that bad...

tonkuma · #7 (**permalink**) 02-22-10, 14:50

Another possibility is buffer hit ratio of NETTOSKO.
If pages of table and/or index were not in buffer at the first access, it would take time to read them.
Second and later, almost pages would be found in buffer.

jerzy.burzek · #8 (**permalink**) 02-22-10, 15:05

but i'm not sure if we undertstand each other ;-)

To clarify the background:
This "main" function (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.
After some tests we found out, calling of EAN_128 is problem.

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:

Code:

SET ret_ = '(00)' || ESO_FILL_TO_LEN(strData_, 17, '0', 'L') || EAN_128_CHECKSUM(strData_);

call takes 0.25s

Code:

if (...) then
  SET ret_ = '(00)' || ESO_FILL_TO_LEN(strData_, 17, '0', 'L') || EAN_128_CHECKSUM(strData_);
else if (...)
 SET ret_ = '(00)' || ESO_FILL_TO_LEN(strData_, 17, '0', 'L') ||   EAN_128_CHECKSUM(strData_);
else if (...)
 SET ret_ = '(00)' || ESO_FILL_TO_LEN(strData_, 17, '0', 'L') ||   EAN_128_CHECKSUM(strData_);
end if;

takes 0.7s etc etc.

To sum up:
-EAN_128 is called only once per cca 20-30 seconds, after scan from java code.
-select from nettosko is called only once per call just for data check, and search is indexed
-from my point of view it seems calling UDF within another UDF significantly slows down UDF call

So, this is my problem and i've not found any solution yet

stolze · #9 (**permalink**) 02-23-10, 17:15

As I mentioned, a UDF is compiled into the statement in which the function is used. That is done once when the so-called section is being created. You can by-pass this with dynamic SQL, but you don't have that here. What you do have is a bit more complex procedural function. That's the issue you have to address, i.e. optimize the function and what it is doing.

p.s: If you call the function 3 times, the internal logic has to be processed 3 times. Thus, it is not surprising to see the execution time to be about 3x as long compared to a single execution.

tonkuma · #10 (**permalink**) 02-26-10, 14:33

Here is a simplified EAN_128_CHECKSUM function.
(from my curiosity.)

Code:

------------------------------ Commands Entered ------------------------------
CREATE FUNCTION ESO.EAN_128_CHECKSUM_simplified(EanCode VARCHAR(32))
	RETURNS CHAR(1)
	LANGUAGE SQL
	DETERMINISTIC
RETURN
SELECT CASE
       WHEN EanCode is NOT null THEN
            CHAR(9 - 
                 MOD( SUM( MOD(3 + 2 * (iter_ + LENGTH(EanCode) ), 4)
                         * INT( SUBSTR(EanCode, iter_, 1) )
                         ) + 9
                    , 10 )
                )
       ELSE
            RAISE_ERROR('75004', 'EanCode is null')
       END
  FROM
       (VALUES      1,  2,  3,  4,  5,  6,  7,  8,  9
             , 10, 11, 12, 13, 14, 15, 16, 17, 18, 19
             , 20, 21, 22, 23, 24, 25, 26, 27, 28, 29
             , 30, 31, 32
       ) p(iter_)
 WHERE iter_ <= LENGTH(EanCode)
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

Simple test:

Code:

------------------------------ Commands Entered ------------------------------
SELECT EanCode
     , ESO.EAN_128_CHECKSUM(EanCode) EAN_CHECKSUM
     , ESO.EAN_128_CHECKSUM_simplified(EanCode) simplified
  FROM
       (VALUES '0', '1', '12', '123', '1234', '12345', '123456', '1234567', '12345678', '123456789'
             , '1234567890', '12345678901', '123456789012', '1234567890123', '12345678901234'
             , '123456789012345', '1234567890123456', '12345678901234567', '123456789012345678'
             , '012345', '0012345', '123450', '1234500'
             , '987', '98765', '987654', '987654321'
             , '9876543210', '98765432109', '987654321098'
             , CAST(NULL AS VARCHAR(32) )
       ) q(EanCode)
;
------------------------------------------------------------------------------

EANCODE                          EAN_CHECKSUM SIMPLIFIED
-------------------------------- ------------ ----------
0                                0            0         
1                                7            7         
12                               3            3         
123                              6            6         
1234                             8            8         
12345                            7            7         
123456                           5            5         
1234567                          0            0         
12345678                         4            4         
123456789                        5            5         
1234567890                       5            5         
12345678901                      2            2         
123456789012                     8            8         
1234567890123                    1            1         
12345678901234                   3            3         
123456789012345                  2            2         
1234567890123456                 0            0         
12345678901234567                5            5         
123456789012345678               9            9         
012345                           7            7         
0012345                          7            7         
123450                           3            3         
1234500                          7            7         
987                              4            4         
98765                            3            3         
987654                           5            5         
987654321                        5            5         
9876543210                       5            5         
98765432109                      8            8         
987654321098                     2            2         
SQL0438N  Application raised error or warning with diagnostic text: "EanCode 
is null".  SQLSTATE=75004

SQL0438N  Application raised error or warning with diagnostic text: "EanCode is null                                                       ".

Internet Services

Web Development

Digital Marketing

Consumer Tech