This has been analyzed in depth over 11 years ago, by non other than IBM themselves.
Cliff notes: SQL is much faster. And that was 11 years ago. If they re-ran the benchmarks today with 11 years of query engine enhancements the benchmarks would probably be even more dramatic.
http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_Performance_DDS_SQL.pdf
-----Original Message-----
From: D*B [mailto:dieter.bender@xxxxxxxxxxxx]
Sent: Friday, September 15, 2017 1:26 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Database I/O Modernization
<Nathan>
In regard to the performance of SQL vs. RLA, perhaps the most relevant fact is that the SQL Query Engine uses RLA under the covers.
</Nathan>
You missed the point in every direction!!!
Using RLA, the programmer is telling how the data should be retrieved. Using SQL (in an appropriate way) the programmer tells the query engine what data he/she wants to have/manipulate and the query engine makes the needed decisions how to do its best, based on the available indexes and some estimates about the balance of the needed Indexes. There are some very important diffrences:
- adding an index could support SQL, but in case of RLA several programms have to be rewritten.
- the bigger the granularity of your SQL, the better the SQL optimizer could use prefetching resulting in more asynchronous I/O and less synchronous I/O with less response time, sometimes paid by more CPU
- in some cases the handmade acces plan of a programmer might be better, but in most cases the query engine finds a better access plan (have alook at some postings in this discussion, meaning 10.000 records would be a lot - did you know, that a blockfetch of 1000 records is as fast as reading one record per SQL? Did you know, that a full table scan beats an index sequential access, if more than less than 20% are needed? Did you know that making setll, reading sequential, chaining some related tables is outperformed by far just telling the query engine how the final resultset should be, by one sql statement and let the query engine do its work?
Stay with RLA, if you feel happy with it, but be aware, that you are one of the persons digging the grave for AS/400. RLA is not the cure, its the illness.
D*B
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
