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:
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:
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:
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.
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.
Comment