Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2010
    Posts
    4

    Unanswered: slow UDF calls (db2v9)

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2010
    Posts
    4

    hello, here's code

    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@

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) What is your platform?

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

  5. #5
    Join Date
    Feb 2010
    Posts
    4
    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)

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  8. #8
    Join Date
    Feb 2010
    Posts
    4

    really thanks very much for all answers

    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

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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                                                       ".

Posting Permissions

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