Two Queries are Better Than One
To improve performance of a query (Query/400, SQL/400, Open Query File, and so on) where large files (more than 100,000 records) are joined, use paired queries. The first query selects records and uses a database file for output. The second query uses the file created by the first query and sorts it, using any of the output options. Our query performance improved three to one using this method.
Faster Processing by Sequential Read
When processing a file in an RPG program, it is more efficient to process it sequentially than by key. So if you don't need the records in any particular order-for example, when all you want to do is update every record in a file- leave the 'K' out of column 31 in the F-specs. The first time I tried this, I saved 20 minutes on a two-hour batch job.
Faster CPYF File Copies
When you are copying one indexed file to another file and the physical record sequence of the resulting file does not matter, key a '1' in the FROMRCD parameter of the CPYF command. This will cause the copy to read the from file in arrival sequence, which is much faster than reading the file in keyed sequence. Here is an example of the command:
CPYF FROMFILE(LIB1/AAA) TOFILE(LIB2/BBB) MBROPT(*REPLACE) + CRTFILE(*YES) FROMRCD(1)