× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



On 27-May-2011 16:38 , Joe Pluta wrote:
On 26-May-2011 14:12 , Joe Pluta wrote:
On 5/26/2011 3:16 PM, CRPence wrote:
I expect that SQL should be much faster even on a poorly
performing system; almost as fast as the RLA, esp. if the
same index is utilized for both the SQL and RLA.

I've never found a single-record fetch to be anywhere near
as fast as RLA, and I did exhaustive tests; SQL doesn't catch
up until the block size is upped to about 100 records. I
could rerun all those tests, but until someone shows me some
evidence SQL has caught up, I have no reason to repudiate the
old data. <<SNIP>>

It seemed as though you were responding to the OP. But since you
quoted my comment rather than the OP, I wasn't sure as to your point,
hence my question.

I was responding to your comment, but using the scenario of the OP, because I mistakenly inferred your statement was meant also to suggest justification of the results seen by the OP, rather than your comment having been made only as a general observation. :-!

<<SNIP>> why I made my point: for single record fetches, RLA
outperforms SQL significantly. For me, native was six times faster,
but that was quite a few years ago, and even so your results aren't
that much different.

And I just wanted to ensure readers and the OP were not concluding that the SQL was effectively unusable in the scenario presented by the OP. While I discourage moving from RLA to SQL where there is little or no benefit to do so, I also do not want to encourage anyone to avoid using SQL when there is little or no benefit to do so. That there is a choice is IMO one of the primary reasons that the DB2 for i is a better RDBMS; regardless that some might try to argue that the RLA somehow either debases or removes the ability to claim "Relational".

Had my results been getting more data and a greater percentage of "hits", the six versus four might still hold.? My tests could hardly be considered legitimate as actual performance metrics. FWiW the testing was performed on a V5R3 system.

Your original comment about the RLA being faster than the SQL for single-row fetch is in my experience both accurate and a legitimate concern for anyone wanting to maximize the performance for an application doing that kind of work. Aside from implementations using the "index access only" method and\or "encoded vector index" which are both available to the SQL [both query engines] but [AFaIK still] not available via any RLA methods, I do not see how the SQL could ever surpass the speed of RLA for single-row lookup or fetch.

Regards, Chuck

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.