SQL is hardly the most efficient way to access a database file. SQL’s strength relies on being a structed language that can be applied to several databases without change not on its speed.
Internal program database access will perform better in most cases, especially if the program is coded in a language optimized to access external data, like RPG.
Of course, the comparison must be made between correct ways to do the database access either in SQL or from within a program.
If there is keyed index in the system, it also exists, as far as AS/400 or iSeries is concerned, or UDB2 for that matter, a keyed access path. And you would use that access path as a logical file from within a program.
SQL performace improves when the number of records handled increase; for large amounts of data SQL maybe the correct choice. Reading one record at a time, however, is much faster when made by a file declared in a program.
<i>Reading one record at a time, however, is much faster when made by a file declared in a program.</i>
If reading is random one-record-at-a-time, that’s probably true. SQL is not intended for random retrieval. But if it is sequential, then appropriate SQL will most likely outperform program declared files and I/O. It’s been a few releases since SQL couldn’t outperform native when sets of records are involved — records that can can be defined as a set in an SQL SELECT statement.