iSeries RPG Embedded SQL Performance

23730 pts.
Tags:
AS/400 performance
Embedded SQL
IBM iSeries
Query/400
RPG
SQL
Hi, I'm trying to replace some long running Query/400 jobs with RPG and embedded SQL. Some of the existing jobs run for almost 24 hours on our machine. I've already successfully managed to reduce a few of these jobs to a matter of minutes using embedded SQL. So far, so good. My problem is when these jobs join very large numbers of records - we have files with over 200 million records. I've tried to replace these jobs with embedded SQL, but I don't really get the performance improvement I was hoping for. Has anybody got any tips for speeding these jobs up? Here's an example of one of the embedded SQL statements :-
SELECT                                     
  ALL
        FILE01_A, FILE02_B,                    
        SUBSTR(DIGITS(DATE_FIELD),4,2) AS MONTH, 
        FILE02_C, FILE01_D, FILE01_E, FILE01_F     
  FROM  FILE01,  FILE02
  WHERE FILE01_KEY01 = FILE02_KEY01
   AND  FILE01_KEY02 = FILE02_KEY02
   AND (DATE_FIELD >= 1080101                  
   AND  CHECK_CODE <> 'AB'')
Thanks for any advice on this, Martin Gilbert.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

There should (of course) only be one quote before and one after the AB in the last line of the statement. I messed up with the cut and paste….

Regards,

Martin Gilbert.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    Martin Your join is doing what Query does - joining the entire files and then discarding bad joins. This is speeded up with subselects. So the number of records joined and then discarded is reduced. Assuming that both of your restrictions are on FILE2 the improvided query would look like this. Much like other types of programming SQL does the stuff in ( ) first so it builds a temp file of the qualifing records from File2 and then joins those to all the records in File1. Both joined files might be handled in subselects if you have where clauses on both. SELECT ALL FILE01_A, FILE02_B, SUBSTR(DIGITS(DATE_FIELD),4,2) AS MONTH, FILE02_C, FILE01_D, FILE01_E, FILE01_F FROM FILE01 join ( Select * from File2 where DATE_FIELD >= 1080101 AND CHECK_CODE <> 'AB') on FILE01_KEY01 = FILE02_KEY01 AND FILE01_KEY02 = FILE02_KEY02
    49,940 pointsBadges:
    report
  • Vatchy
    The easiest method I've found to speed up SQL and queries is to create the logical file that the SQL is creating - permanently. If you only run this once per month or so you might not want the additional access path on your system but if you run if every day or every week then (IMHO) it is definitely worth it.
    1,410 pointsBadges:
    report
  • Sloopy
    Biggest speed-up tip - DON'T use DDS-defined logical files! See here for a full explanation: The link also shows what you have to do to stop SQL using logical files by default. Instead, you should create SQL Views. Vatchy is correct that you should create the intermediate file which SQL creates - but that is not an iSeries, DB/400 logical file, it's an SQL View. The major overhead on your SQL looks like the date and check code selection. Making those the major keys in your view may make a considerable difference to the processing time. Regards, Sloopy
    2,195 pointsBadges:
    report
  • graybeard52
    You need to make sure this query gets run thru the newer SQE engine and not CQE. While performance is very much system and file dependent, I found about 400% improvement. Here's some other things that might help. (1) Check file QUSRSYS/QAQQINI file. Make certain "Ignore derived indexes" option is set to *YES (default is *NO) (2) As suggested, building indexes is very important. If you have V5R4 or better, run the query through Visual Explain and build the suggested indexes. (3) Create a seperate memory pool for SQL requests and set the minimum and maximum sizes identical. This prevents performance tuning from shriking the available memory while the query is running. This step alone helped me bring a 27 hour query down to 1 hour.
    3,115 pointsBadges:
    report
  • Sloopy
    Oh, WHY is this editor not showing the link I inserted?? Look here: http://www.itjungle.com/fhg/fhg040908-story01.html :-~
    2,195 pointsBadges:
    report
  • philpl1jb
    Yes use Physical files or views for the files queried in the SQL, not logical files. I think you want to create indexes over the files for the key fields. But I think that the use of the subselect in the SQL will make the biggest difference.
    49,940 pointsBadges:
    report
  • philpl1jb
    When you create a logical file or a view, you specify the same stuff, columns, order, omit/select rules. It's easy to think of a view as an SQL logical but it's not. The logical file contains the index used to find the data, it taks a significant amount of space, it requires updates when data is added, changed or deleted. The view contains the set of rules to get the data, no index, no map, nothing that will really speed up the system .. except that on the SQL engine will be used. Want speed use the physical or view but build the indexes.
    49,940 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following