Re: SQL Insert - performance

On 24-Jul-2015 06:34 -0600, Charles Wilt wrote:

On Fri, Jul 24, 2015 at 4:30 AM, Kevin Passey wrote:

Is it any more efficient to add records in an RPG program using
embedded SQL Insert, rather than opening a file in RPG the
traditional way and writing records..

Yep it depends...

Main issue, if you use SQL and insert one record at a time...then RPG
RLA will win.

But if use SQL to insert blocks of records at once, then SQL wins.

Even which of those methods will /win/ in those scenarios, still can only be answered with the "it depends" reply. Given sufficient number of single-row inserts and the ability of SQL to use pseudo-closed as contrasted with how the non-SQL opens and closes to effect the same, the SQL could be spectacularly faster or nearly on par. So again, it depends. The SQL inherently has more /overhead/, but that can be overcome by the SQL features designed specifically for that intent.

The choice of which to use should be according to what is efficient for coding and reviewing, and is robust for enabling changes when they may [and likely eventually will] be needed; that last one is often a reason many will cite for SQL, but without good planning, the ability to avoid a recompile of SQL programs is often little better than with Row-Level-Access (RLA, or native, or /traditional/ per the OP). But if eventually the implementation is deemed best, such that the file would use a new data type supported only by the SQL, having moved already to use the SQL vs RLA would avoid the /requirement/ to change to SQL then versus the /choice/ to use SQL now.