RE: Embedded SQL - performance question

I never done the tests such as those done by Joe and Chuck. But a year or
so back I needed to write an order inquiry. For the most part I used SQL
Selects, and it worked reasonably well (i.e., sub-second response). Part of
the inquiry, though, was to potentially (if the user selected it) display
the details for a billed order in the history. Maybe I was just
inefficient, but that puppy took forever (i.e., > 15 minutes) to return the
records I needed. The history details was around 1,000,000 records at the
time (and growing). I changed it to RLA, and got back the sub-second
response. The index used for the RLA, which was equivalent to the SELECT,
existed before I started writing the program.

Usually. I think (again, no empirical evidence), when dealing with a group
of records, SQL is better or, at least equivalent. Another program used SQL
to simply return a switch that indicated whether or not a history record
existed for an item#. Never had a complaint about waiting. Sometimes
(ofttimes) I'm just puzzled by results .

Jerry C. Adams
IBM i Programmer/Analyst
"I've just learned about his illness. Let's hope it's nothing trivial."
-Irvin S. Cobb
--
A&K Wholesale
Murfreesboro, TN
615-867-5070


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Alan Campin
Sent: Friday, May 27, 2011 3:23 PM
To: RPG programming on the IBM i / System i
Subject: Re: Embedded SQL - performance question

I am just not sure about all this SQL performance arguments. There is a
difference mostly occurring I think because IBM continues to use a call
model instead of ILE call model but I wonder how much difference it really
makes.

We have about 70 trigger service programs in use daily written 100% in
ILE/RPG using 100% SQL for I/O being fired millions of times everyday but
our users have not reported any difference in performance since we turned
then on.

Everyone of these service program is making at least 2 or 3 different single
record fetches with every call in addition to insert and updates. (We are
taking data from a unnormalized DDS described system and maintaining a
normalized DDL defined database).

The one big issue that we continue to have is that IBM will not update an
access plan if a service program is in use so the system recalculates an
access plan for an object but can't get it updated because of others using
the service program. This is a royal pain.

The other issue we continue to struggle with is locks on objects being
referenced by Referential Integrity but performance is not issue.

On Fri, May 27, 2011 at 10:09 AM, Joe Pluta
<joepluta@xxxxxxxxxxxxxxxxx>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. 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
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.