Major UDF performance issue

[thurring]

In tracking down a major performance issue with an SQL statement I have discovered that my UDF function is not only invoked for every row returned by the SQL but for every row retrieved in the block of records from the database! Additionally, the 'Deterministic' function attribute seemed to have no effect. i.e. my SQL was designed to retrieved only 1 record, under the covers the O/S decided to retrieve a block of 4481 records, the UDF function was invoked 4481 times! Is there something wrong with my SQL or UDF definition? Is this intentional behaviour? If so, can I restructure my SQL to avoid this? SQL: Select A, B, C, DoFuncton(A, B) as FuncResult from Myfile fetch first 1 rows only Function creation: CREATE FUNCTION DoFunction (DEC(7,0),CHAR(25)) RETURNS CHAR(25) RETURNS NULL ON NULL INPUT LANGUAGE RPGLE EXTERNAL NAME AWJBUPP DETERMINISTIC NO SQL NO EXTERNAL ACTION PARAMETER STYLE SQL ALLOW PARALLEL NOT FENCED Thanks for your assistance.

[B.Robins]

Re:Major UDF performance issue

I heard from a reliable source who heard from a reliable source that the Deterministic option is not operational until V5R3. I too discovered problems with a Deterministic UDF on my V5R2 system.

[thurring]

Re:Major UDF performance issue

We're on V5R4 and it's still not working!

[efnkay]

Re:Major UDF performance issue

Maybe you can throw into VisualExplain in the Navigator and get an idea of the I/O going on behind the UDF call. Perhaps a more explicit 'SELECT' statement using a 'WHERE' clause could avoid a Full Table scan, or hash table, or index scan or whatever he's doing with the SQL statement...I don't believe "FETCH FIRST 1 ROW" actually produces one record retrieval from the database, it just returns (the 1st) one row of the result set.

[thurring]

Re:Major UDF performance issue

Thanks for the reply. You are quite right about the 'FETCH n ROWS' - this was the problem. When the query includes a 'WHERE' clause which restricts the records included in the result set and the query doesn't include a 'FETCH n ROWS' clause the UDF function is invoked only for the restricted record set, not for every record in the block of records fetched. i.e. This invokes the UDF only once: SELECT A, B, C, DoFunction(A, B) AS FuncResult WHERE A = 'FULLKEYVALUE' This invokes the UDF for every record in the fetched block (1,000's of times!): SELECT A, B, C, DoFunction(A, B) AS FuncResult WHERE A = 'FULLKEYVALUE' FETCH FIRST 1 ROWS ONLY This is very fustrating. I can't see why IBM have implemented UDF's this way. We have a 3 tier application where the user potentially needs to be able to scroll through tables containing many millions of records. That is why we were using 'Fetch n Rows' - so that the application would retrieve say 100 records then another 100 if the user scrolled down far enough, then another 100 etc. Is ther another way of achieving this functionality?

[efnkay]

Re:Major UDF performance issue

Well...I understand why IBM executes a UDF just like built-in scalar functions. The UDF is essentially "your" scalar function. You would want it to be run for each record or row returned (from the database) in your selection. They are many ways to improve the performance and reduce the I/O done in your embedded SQL ILE/RPG programs. Most you are already familiar with using just like with native I/O. ie. You give your users a position-to field(s) that you take and build into your SELECT statement in the WHERE clause. Example: Filling/Paging Forward: "Select A.* From YourTable A Where (A.Keyfield >= osition_to)" or Filling/Paging backwards: Select A.* From YourTable A Where (A.Keyfield <= osition_to) ORDER BY A.KeyField DESC Also based on your expectations for a given key field(s)...You might improve performance a pinch with the OPTIMIZE FOR xxx ROWS as opposed to FETCH FIRST xxx ROWS clauses. Let us know how you do...!!!

[efnkay]

Re:Major UDF performance issue

And...There could be another way to FETCH FIRST ? ROWS ONLY. A couple of dream-times ago, I wrote a UDF (external RPG) called SLN() which takes no parms just returns a count or a line sequence number for each record selected. Like Query/400 would supply for you when you ran your query to the display. I can run down and post the source and the CREATE syntax used if you'd like to try it. It was used like this: select sln() as seq#, a.lolzon as Zone, min(a.lolcid) as Begin, max(a.lolcid) as Endin, dec(avg(a.lopipr),3,0) as PckPri from wholoc a where a.losrom = 'SA1' and a.lopipr <= 500 group by a.lolzon order by 1 Returns... ....+....1....+....2....+....3....+....4....+... SEQ# ZONE BEGIN ENDIN PCKPRI 1 00 00001 00821 500 2 01 01011A 01211 500 3 02 02011A 02621 500 4 03 03011 03991B 500 5 04 04011A 04995 500 6 05 05101 05791 500 So on and so forth... It can be used like this: select sln() as seq#, a.lolzon as Zone, min(a.lolcid) as Begin, max(a.lolcid) as Endin, dec(avg(a.lopipr),3,0) as PckPri from wholoc a where a.losrom = 'SA1' and a.lopipr <= 500 and sln() = 1 group by a.lolzon order by 1 Note: 1st record of the aggregated group, max(ENDIN) is 00821 not 00001. ....+....1....+....2....+....3....+....4....+... SEQ# ZONE BEGIN ENDIN PCKPRI 1 00 00001 00001 500 ******** End of data ********

[efnkay]

Re:Major UDF performance issue

Indeed dream-times before I started using /FREE. Posted for your use at your discretion. To create and use the SLN() UDF for "Select Line Numbering": Use this CREATE FUNCTION syntax: CREATE FUNCTION QGPL/SLN ( ) RETURNS INTEGER LANGUAGE RPGLE SPECIFIC QGPL/SLN NOT DETERMINISTIC NO SQL CALLED ON NULL INPUT NO EXTERNAL ACTION DISALLOW PARALLEL FINAL CALL NOT FENCED EXTERNAL NAME 'QGPL/SLN' PARAMETER STYLE DB2SQL ; Note: /COPY HCALLR member has compile options specific to our system. 0009.00 H DftActGrp(*NO) 0010.00 H ActGrp(*CALLER) 0011.00 H Bnddir('*LIBL/APIBND': 0012.00 H '*LIBL/GETBND': 0013.00 H '*LIBL/UPDBND') 0014.00 H FixNbr(*ZONED) 0015.00 * * Here's the RPG program... 0009.00 *--------------------------------------------------------------* 0010.00 /COPY *LIBL/QRPGLESRC,HCALLR 0011.00 *--------------------------------------------------------------* 0012.00 * Program Name: SLN() Select Line Number. This program is 0013.00 * defined as an external UDF for use as a 0014.00 * scalar function to return a sequential line 0015.00 * number for each record returned from any 0016.00 * given SELECT statement. To line number out- 0017.00 * put returned from the a SELECT statement: 0018.00 * 0019.00 * Example: SELECT SLN() AS SEQBR, A.* FROM UO00010 A 0020.00 * WHERE A.COMNO = 1 0021.00 * ORDER BY A.COMNO, A.ORDNO 0022.00 * 0023.00 *--------------------------------------------------------------------- 0024.00 * CopyBook Descr - SQLUDF Contains structures and definitions for 0025.00 * additional optional parameters when used 0026.00 * depending on parameter style specified on 0027.00 * CREATE FUNCTION statement. 0028.00 * SQLUDF CopyBook. 0029.00 /COPY *LIBL/QRPGLESRC,SQLUDF 0030.00 *--------------------------------------------------------------------- 0031.00 * 0032.00 *--------------------------------------------------------------------- 0033.00 * *ENTRY parameter list definitions. 0034.00 *--------------------------------------------------------------------- 0035.00 * 0036.00 * Return Line_Nbr 0037.00 D RtnLine s 10i 0 0038.00 * Return Null_Ind 0039.00 D RtnNull s 5i 0 0040.00 * 0041.00 * DB2 passes these parameters implicitly defined 0042.00 * by the DB2 database manager for external functions. 0043.00 * 0044.00 * SQL State 0045.00 D Sqlstat s 5a 0046.00 * Function Name 0047.00 D FuncNam s 139a Varying 0048.00 * Specific Name 0049.00 D SpecNam s 128a Varying 0050.00 * Return MsgTxt 0051.00 D RtnMsgx s 70a Varying 0052.00 * 0053.00 * DB2 passes any optional parameters after those 0054.00 * above when the CREATE FUNCTION statement indicates. 0055.00 * This parm passed when FINAL CALL specified on function 0056.00 * declaration with PARAMETER STYLE DB2SQL. 0057.00 * 0058.00 D CallType s 10i 0 0059.00 * 0060.00 * UDF Call type. {-1 = 1st, 0 = Normal, 1 = Final} 0061.00 * 0062.00 *================================================= ==============* 0063.00 * 0064.00 C *Entry Plist 0065.00 C Parm RtnLine 0066.00 C Parm RtnNull 0067.00 C Parm SQLstat 0068.00 C Parm FuncNam 0069.00 C Parm SpecNam 0070.00 C Parm RtnMsgx 0071.00 C Parm CallType 0072.00 * 0073.00 * Initialize Return Line Nbr counter on 1st call. 0074.00 * 0075.00 C If (CallType = UDF_FIRST_CALL) 0076.00 C Eval RtnLine = *Zero 0077.00 C Endif 0078.00 * 0079.00 * Increment RtnLine and set result null indicator. 0080.00 * 0081.00 C Eval RtnLine = (RtnLine + 1) 0082.00 C Eval RtnNull = *Zero 0083.00 * 0084.00 * Return w/*inlr off to preserve RtnLine between calls. 0085.00 C Return 0086.00 * 0087.00 C Eval *inlr = *on 0088.00 * 0089.00 *================================================= ==============*

[efnkay]

Re:Major UDF performance issue

My apologies. A friend of mine reminded me that I had modified the /COPY SQLUDF member used in the above RPG source post. You will normally find this guy in QSYSINC/QRPGLESRC if your i-admin people installed this (optional) piece of the OS. By the comments...I must have had some expectation that these definitions were already in a prior version...??? Dunno. * *-------------------------------------------------------------- * Unlike the comments above, these definitions were not in this * member from QSYSINC...Call types for using the FINAL CALL dec- * laration on the function definition are defined below. *-------------------------------------------------------------- * D UDF_FIRST_CALL s 10i 0 Inz(-1) D UDF_NORML_CALL s 10i 0 Inz(0) D UDF_FINAL_CALL s 10i 0 Inz(1) *

[thurring]

Re:Major UDF performance issue

Thanks for you multiple replies. I appreciate your time and effort. I'll try your suggestion in the next few days as soon as I get a chance. Thanks again.

[thurring]

Re:Major UDF performance issue

I've had the following posted as a solution from another forum: WITH cte1 AS ( SELECT A,B,C FROM MyFile FETCH FIRST ROW ONLY ) SELECT A,B,C,DoFunction(A,B) AS FuncResult FROM cte1

[efnkay]

Re:Major UDF performance issue

Very good. Did that perform better for you as a solution to your original performance issues?

[thurring]

Re:Major UDF performance issue

Yes, the UDF is only executed for the rows in the final result set. Thanks for your assistance.