Re: SQL performance

Am 20.08.2024 um 22:32 schrieb VERNON HAMBERG Owner via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>:

+1 on working with data sets!

Thanks Vernon.

On Tue, 20 Aug, 2024 at 10:17 AM, Daniel Gross <daniel@xxxxxxxx> wrote:

To utilize the full power of SQL you should think about "data sets" and how the a linked together - maybe you can join the data to another table - so that you don't have thousands of open/close operations or select-into statements.

What I mean with that comment is, that to fully utilize SQL in application programming, you have to let go of the "good old RPG chain or setll/reade" logic.

When designing a new or redesigning an existing application, I always start with SQL - and I try to implement all data access in 1 SQL statement.

That means, joining all additional data, using UNION to append different datasets, and so on - and even adding "level checks" using ROW_NUMBER OVER(PARTITION BY ...) to always know where in my dataset I am. Only after that, I might implement a SQL cursor loop in RPG.

So my recommendation for embedded SQL to other programmers is always - don't just replace RPG with SQL statements - replace the whole logic.

The funny thing is - those SQL statements might look hard to understand - but you can simply copy them into iACS and run them to understand what they are doing. You can't do that with SETLL/READE.

Kind regards,
Daniel