RE: SQL CLI and performance

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.
> 
>