When is SQL not better than native IO

[DeadManWalks]

A co-worker has reference some article stating that native IO is better than SQL.

I like SQL over native and find the performance fast if you have the correct index.

When is SQL not better than native (slower, performance issues...) than native IO? I am looking for evidence not preferences.

Thanks.

[GLS400]

Re: When is SQL not better than native IO

Hi DMW:

See page 37/38 for bench tests:

http://www.ocean400.org/assets/documents/2014/whymodernize.pdf

The pdf was produced in 2012 not sure what version of the operating system

It shows for 1 or 2 record accesses Native I/O is better, for 100+ sql is better.

Best of Luck
GLS

[tomliotta]

Re: When is SQL not better than native IO

Rats! Just a week or so ago, I tested a lengthy timeout in this forum and didn't lose my test post. Not so tonight. Sorry... my contribution is gone. (Blasted NFL draft!)

[B.Hauser]

Re: When is SQL not better than native IO

If you replace a CHAIN with the SQL equivalent, native I/O will win.
If you translate native I/O 1:1 into SQL, native I/O may win.
But an 1:1 translation is not the goal!

If you use (embedded) SQL correctly, it will in all other cases. Otherwise something is going wrong and must be analyzed.

1. When using (embedded) SQL you need to consider blocked reads and as few as possible full opens.
The most time consuming part of an full open is opening the data path (ODP)
2. Instead of 4 Cursors or 2 Cursor and 2 SELECT INTO Statements for reading the order header table, the address table, the order detail table and the item master table (at least 4 full opens must be performed), it is far better to join those 4 tables together and declare only a single cursor (only a single full open and ODP)
3. Never ever specify a logical file in an SELECT statement (rerouted to the old CQE before Release 7.1 TR 6?)
4. Do not change the compile option CLSSQLCSR to *ENDMOD. With this compile option the ODPs get deleted as soon as the module is left and the next run a full open must be performed.
5. When running your programs in activation group new, the ODPs get deleted as soon as the activation group is ended.
6. You need the right indexes (or access path in logical files) even though you cannot specify an index directly within a SQL statement.
... and there is much more that cannot be explained with a few sentences.

Birgitta

[tomliotta]

Re: When is SQL not better than native IO

"Ditto" for Birgitta's post. I had earlier tried to post comments that expanded the term "better" to go beyond just the simple interpretation of "(slower, performance issues...)" since advantages/disadvantages cover much more than 'faster or slower'. But perhaps everything other than speed in well-defined circumstances would be ignored anyway (even if they might be more important).

[Henrik Rutzou]

Re: When is SQL not better than native IO

As always it all depends.

I use a mix of RLA and SQL.

Embedded Dynamic SQL for REST/CRUD services where I have server side column sort and selections based on parameters passed from the Client (not SQL passed from the client), RLA when complicate BL and properties decides what to read from the tables.

Performance isn?t the big issue to me but simplicity is. I may process and XML document that has a XML element that needs referential data from a table. A Simple ?RLA CHAIN? in the middle of all the XML processing RPG code is far simpler and more readable than an embedded SQL syntax to process the same.

On the other hand embedded dynamic SQL when retrieving data sets is far more simple and powerful than trying to do the same in RLA.