Re: Embedded SQL - performance question

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