ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Improve this SQL function?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Improve this SQL function?

    Often in my SQL statements, I need to check 9 different code fields of an account record looking to see if it has a specific code. So I do something like this:

    Code:
    [FONT=courier new]select ACCT
    from MYFILE
    where (CODE1 = 'CRT' or CODE2 = 'CRT' or CODE3 = 'CRT' or CODE4 = 'CRT' or CODE5 = 'CRT' or CODE6 = 'CRT' or CODE7 = 'CRT' or CODE8 = 'CRT' or CODE9 = 'CRT')
    and ACCTBAL(ACCT) = 0 and ACCTSTS <> 'L'[/FONT]
    [FONT=verdana][/FONT]


    So I'm just selecting all accounts that have value 'CRT' in one of the code 9 code fields, plus two other criteria, one of which uses a function ACCTBAL that has no speed problem. This SQL statement returns results within a couple seconds.

    In order to simplify SQL statements that need to do something like this, I decided to make an SQL function to just do the work for me and tell me if the passed account contains the passed code in one of its 9 code fields, and just return to me a Y or N. So I made function HASALERT like this:

    Code:
    [FONT=courier new]CREATE or replace FUNCTION MYLIB.HASALERT(ACCTNO char(14),CODE CHAR(3))
    RETURNS CHAR (1)
    LANGUAGE SQL
    SPECIFIC MYLIB.HASALERT
    READS SQL DATA
    CONCURRENT ACCESS
    RESOLUTION DEFAULT
    FENCED
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    NO EXTERNAL ACTION
    SET OPTION DBGVIEW = *SOURCE,DATFMT=*ISO
    begin
    DECLARE ALERT1 CHAR(3);
    DECLARE ALERT2 CHAR(3);
    DECLARE ALERT3 CHAR(3);
    DECLARE ALERT4 CHAR(3);
    DECLARE ALERT5 CHAR(3);
    DECLARE ALERT6 CHAR(3);
    DECLARE ALERT7 CHAR(3);
    DECLARE ALERT8 CHAR(3);
    DECLARE ALERT9 CHAR(3);
    DECLARE YESORNO CHAR(1);
    
    /* Retrieve 9 codes for the passed account */
    Select CODE1, CODE2, CODE3, CODE4, CODE5,
           CODE6, CODE7, CODE8, CODE9
     into  alert1, alert2, alert3, alert4, alert5,
           alert6, alert7, alert8, alert9
     from  MYFILE
    where  ACCT = acctno;
    
    /* Determine if the passed code is one of the 9 */
    If code = alert1 or
       code = alert2 or
       code = alert3 or
       code = alert4 or
       code = alert5 or
       code = alert6 or
       code = alert7 or
       code = alert8 or
       code = alert9
    then set YESORNO = 'Y';
    Else set YESORNO = 'N';
    End If;
    
    Return YESORNO;
    
    End;[/FONT]
    [FONT=verdana][/FONT]


    There may be a better way to do this in the function, and I would love to see some ideas to improve it.

    However, what really surprised me is how poorly this new HASALERT function performs! It's basically doing the same thing as what I was doing manually, but it runs about 100-200 times slower! This SQL statement, doing the same thing but using the new function HASALERT, takes minutes to run instead of seconds:

    Code:
    [FONT=courier new]select ACCT
    from MYFILE
    where HASALERT(ACCT,'CRT') = 'Y'
    and ACCTBAL(ACCT) = 0 and ACCTSTS <> 'L'[/FONT]
    [FONT=verdana][/FONT]


    Is the function HASALERT so poorly written that it could account for this huge difference in performance? What can I do to improve this?

    Your insight and guidance would be appreciated.

  • #2
    Hey Viking try this.

    select ACCT from MYFILE where 'CRT' In (CODE1,CODE2,CODE3,CODE4,CODE5,CODE6,CODE7,CODE8,C ODE9) and ACCTBAL(ACCT) = 0 and ACCTSTS <> 'L'
    Jim

    Comment


    • #3
      Have you ran it thru Visual Explain? I try to make a habit of doing that.

      Comment


      • #4
        I'm thinking that if you want this to be a function that something along the lines of:

        Code:
        CREATE or replace FUNCTION ramlib.HASALERT(ACCTNO char(14),CODE CHAR(3))
        RETURNS CHAR (1)
        LANGUAGE SQL
        SPECIFIC ramlib.HASALERT
        READS SQL DATA
        CONCURRENT ACCESS
        RESOLUTION DEFAULT
        FENCED
        NOT DETERMINISTIC
        CALLED ON NULL INPUT
        NO EXTERNAL ACTION
        SET OPTION DBGVIEW = *SOURCE,DATFMT=*ISO
        begin
        DECLARE ALERT1 CHAR(3);
        DECLARE ALERT2 CHAR(3);
        DECLARE ALERT3 CHAR(3);
        DECLARE ALERT4 CHAR(3);
        DECLARE ALERT5 CHAR(3);
        DECLARE ALERT6 CHAR(3);
        DECLARE ALERT7 CHAR(3);
        DECLARE ALERT8 CHAR(3);
        DECLARE ALERT9 CHAR(3);
        DECLARE YESORNO CHAR(1);
        
        /* Retrieve 9 codes for the passed account */
        
        Select CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END  into YESORNO
         from  MYFILE
        where  ACCT = acctno and code in (code1,code2,code3,code4,
               code5, code6, code7, code8, code9);
        
        /* Determine if the passed code is one of the 9 */
        
        Return YESORNO;
        
        End;

        Comment


        • #5
          Except the only DECLARE you'll need is for the YESORNO - no need for the ALERT1-ALERT9.

          Comment

          Working...
          X