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:

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:

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:

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:

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:

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:

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: