Re: SQL performance

Hi Daniel

My +1 is an agreement with your statement about thinking about sets. In COMMON's basic SQL bootcamp, I was given the opportunity to speak about SELECT statements - and in that presentation, set theory was a vitally important part of what I said. As you say here, replacing record-level-access with several FETCH's will usually have poor performance. In its simplest form, using a JOIN of header and detail tables is a basic first step.

At any rate, I believe we might be agreeing "violently" - does that translate well auf Deutsch? I hope so!

Regards
Vern

On 8/21/2024 12:12 AM, Daniel Gross wrote:

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