Re: Embedded SQL - performance question

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. You're in a function, mine was comparing native I/O to embedded SQL. 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.

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?

Joe