Re: Embedded SQL - performance question

On 5/27/2011 5:34 PM, CRPence wrote:

On 27-May-2011 09:09 , Joe Pluta wrote:

On 5/26/2011 10:36 PM, CRPence 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.

2000 single-row retrieval attempts with 39 actual values found
took ~1.13sec; function resolution had already been activated in my
job, and ~.2sec longer in a new job. The SELECT INTO was defined in
a LANGUAGE SQL stored procedure [ACTGRP(*CALLER)] where the SQL
TABLE was created with a PRIMARY KEY CONSTRAINT index on the
cCusNbr and cEmlTyp columns, returning the EmlAdr varchar(45)
result or the NULL value indicator when the key value was not
located.

If I read this correctly Chuck you're providing an isolated instance
of performance of SQL. Thanks for taking the time to share this!

Even though it doesn't provide a lot of context re native RLA, what's
interesting is that it's similar to what I found several years ago:
in my tests, SQL executed 10,000 fetches in a little under four
seconds. That's within shouting distance of what you found here. Of
course, we're comparing apples to elephants. For example, most of
your attempts are misses, whereas I tested only successful fetches.

I was mimicking the original scenario which was noted to perform
poorly as SQL, but perform quickly when replaced by RLA. The claim was
that when coded as the SQL SELECT INTO the 2000 single-row lookups with
an 80% rate of not-found conditions took ~20 times longer to complete
than the equivalent RLA. My attempt was merely to show that the
scenario described should complete nearly 20 times faster than
presented. My results showed even better, although my CPU and I\O
characteristics may make up the differences between 19 and 21 times
better, they were unlikely any relationship to the first 18 times in
reduction.

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.

Perhaps if you told us how an equivalent run calling an RPG program
with native I/O performed, we might get a sense for how the two
compare in your environment?

The OP has apparently given up on the SQL and decided to use the RLA,
but the non-SQL code was not given. So I do not see much benefit in
generating an equivalent for native I\O when the point was to show that
the SQL results, properly implemented, should have been much closer to
the RLA results than the OP had achieved. However with CHAIN entirely
inline rather than in another module, the result for the identical
effects for equivalent processing [while using the same SQL only for the
start\end timestamps] showed 0.3sec or about consistently 4 times better
than the SQL and 10 times better than what the OP had shown.

This is consistent with what I found, and 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.

Joe