Re: Embedded SQL - performance question

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.

You're in a function, mine was comparing native I/O to embedded SQL.

A procedure actually, but a routine all the same. However my stored procedure was embedded SQL, just LANGUAGE SQL, which was compiled as embedded SQL in C. The point was to make the SQL get "called" just as the OP had done, though the OP had coded the embedded SQL in a *SRVPGM and mine was a *PGM.

But even back then on my little model 270, I could perform 10,000
individual (successful) CHAINs on a file with 6 fields in 600
milliseconds. The equivalent operation with SQL SELECT INTO took
3.79 seconds.

I do not question the results. I showed the SQL as implemented by the OP, probably due to Activation Group, could clearly have done much better; possibly even coming close to matching the results noted for the RLA. Very probably the RLA code was not ideal for performance either, perhaps again per an activation group issue; i.e. that the RLA was noted to have taken more than sub-second for only 2000 inquiries seemed amiss.

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.

Regards, Chuck