Re: SQL performance

Hi Vern, it also translate well to Italian :-)

My basic question is why the SQL optimizer don't act the same on the Select
as for the Cursor? By design or some obscure reason?

However, getting back to the discussion of when to use SQL or traditional
access, the yardstick I use is mainly related to the detail (or cost) of
the information to be retrieved or the dependence of the information on its
content.

TIA
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco


Il giorno mer 21 ago 2024 alle ore 10:37 Vern Hamberg via RPG400-L <
rpg400-l@xxxxxxxxxxxxxxxxxx> ha scritto:

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

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.