Nested SQL Loop - Performance Hit

[D.Abramowitz]

I am building a subfile on an SQL loop that is based on selection criteria from an outer SQL loop.

It works. However I'm taking one heck of a performance hit. I believe this is because after all the records are retrieved based on one selection criteria, the inner loop closes the cursor, and control is returned to the outer loop. A new record is FETCHed in the outer loop, a dynamic SQL statement is created, the inner loop cursor is opened, and the process starts again.

Would a right outer join be useful in these circumstances?

Dave

[efnkay]

It sounds like a "join" is what you're arriving at between the outer loop, and some matching or qualifying records from your inner loop. In most cases, if you can get what you need in a single SQL statement, it will out perform the combination of separate selects, statement preparation, cursor opening and closing, binding variables, etc.

If you have the Navigator installed, (we're still at v5r4) it's got a nice little feature called Visual Explain. (Click Databases, click the "Run SQL Script" linked application on the bottom right of the Navigator panel.) You can plug your SQL statements right into this edit panel, click Run and Explain. It will show you where your statement is grinding, the resources it takes, temporary indices built, and the number of records scanned or included in each "select" statement, etc. I've found it extremly useful in figuring out what a SQL statement is doing wrong, and "where" it's doing it at. Good Luck!