iSeries RPG Embedded SQL Performance

Gilly400

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.

Asked: November 12, 2008 2:17 PM Last updated: November 14, 2008 10:23 PM

Answer Wiki

Last updated: November 12, 2008 2:19 PM GMT

Gilly40023,730 pts.
History
Contributors
- Ordered by most recent
- Gilly40023,730 pts.

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

philpl1jb Nov 12, 2008 3:33 PM GMT

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

54,090 pointsBadges:

report
Vatchy Nov 13, 2008 6:57 PM GMT

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,415 pointsBadges:

report
Sloopy Nov 14, 2008 4:18 PM GMT

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 Nov 14, 2008 4:19 PM GMT

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 Nov 14, 2008 4:19 PM GMT

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 Nov 14, 2008 9:38 PM GMT

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.

54,090 pointsBadges:

report
philpl1jb Nov 14, 2008 10:23 PM GMT

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.

54,090 pointsBadges:

report