RE: crazy embedded sql performance

However it is also true that good SQL can beat native I/O.

In addition, an LF designed for a native I/O program tends to be only
used by that one program while SQL indexes tend to have more reusability
since the query engine can make use of multiple indexes at a time.

Yivi,

I'd expect results such as yours if the procedure is summing or counting
detail records.

With the native RPG I/O, each detail record must be brought out of the
DB and passed to your RPG.  Whereas with SQL, the DB is only passing
back the summary record(s).

Other ways the SQL can perform better
1) small number of fields actually being returned (but as Tommy
mentions, an LF can help native RPG I/O do the same)
2) using a multi-row fetch ( usually takes > 100 rows at a time to see
benefits)


HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Holden Tommy
Sent: Thursday, November 17, 2005 9:19 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: crazy embedded sql performance

Could be that the chain/read version is I/O bound.  Embedded SQL can
compete with "some" native I/O Apps but "good" code using proper indexes
(LFS etc) can still beat SQL performance.  The benefits of using SQL
opposed to native I/O can be duplicated by creating LFs that contain
only certain fields from the PFs, etc.  But that comes at a cost as
well. 


Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Yivi
Sent: Thursday, November 17, 2005 5:24 PM
To: RPG programming on the AS400 / iSeries
Subject: crazy embedded sql performance

Hi everybody.

Recently I converted a rather simple program from using old style
chain/reade over lfs operations to embedded sql queries, which I find
prettier even if they have to be coded breaking free-format.

The thing is that I am getting a very steep difference in performance
between the two versions, with the SQL one completing the same tasks in
about a third of the time than the old one.

I am not complaining about the 40 minutes gained, but I was rather
afraid
that the change would bring a penalty in performance, and not a bonus. I
am
still skeptic, and tend to believe something is very wrong with the
chain/reade version, even if looks good to the eye and gives the right
results.

Anyone else had a similar experience? Or I am in the right track with my
lack of faith?

Regards,

I.-