Re: SQL CLI and performance

Good Morning

That sounds right. I am just learning to use CLI, so I will have to read on 
to figure out how to fetch multiple rows at once. I am only selecting the 
rows I need in the SQL statement. I have been using SQLBindCol to bind the 
columns to fields in a subfile, and then using a DOU loop to fetch one row 
at a time. I will try that (when I figure out how), and return with the 
results. Thanks! 

On 6/21/05, Wilt, Charles <CWilt@xxxxxxxxxxxx> wrote:
> 
> SQL _is_ faster.
> 
> If and only if you don't try to use it like RPG.
> 
> If you open a 'SELECT *' cursor and fetch one row at a time, it will be 
> slower.
> 
> On the other hand, using a 'SELECT field1, field2' (ie. return only the 
> columns you need) and fetch multiple rows at once, it will be slower.
> 
> Try your test again, this time doing a multi-row fetch of 1000 rows at a 
> time.
> 
> 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 Tony Carolla
> > Sent: Monday, June 20, 2005 12:23 PM
> > To: RPG programming on the AS400 / iSeries
> > Subject: SQL CLI and performance
> >
> >
> > I have started using Call-level interface SQL. It seems to
> > work as expected,
> > while a bit more complex than embedded. I have noticed that SELECT
> > statements executed against the local DB (served on the
> > AS/400 the RPG
> > program is running on), don't seem to perform as well as
> > straight RPGIV.
> > Obviously, there is more flexibility, using ORDER BY, JOINs,
> > etc., but I had
> > hoped that the speed would be at least the same, if not
> > better than RPG.
> >
> > I did a non-scientific comparison of filling up a four-field
> > subfile, then
> > displaying it. No ORDER BYs, or anything, just pick the first
> > 9999 records,
> > and display them. The SQL program takes double the time of a
> > similar RPG
> > program. I know how to improve sorting/joining/IF select
> > statements with
> > indexing, but this is just straight arrival-sequence reading.
> >
> > Has anybody else had this experience also? Can I do file
> > overrides that will
> > influence the performance of the SQL statements?
> >
> > --
> > "Enter any 11-digit prime number to continue..."
> > "In Hebrew SQL, how do you use right() and left()?..." -
> > Random Thought
> > "If all you have is a hammer, all your problems begin to look
> > like nails"
> > --
> > This is the RPG programming on the AS400 / iSeries (RPG400-L)
> > mailing list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> >
> >
> 
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
>