RE: Green screen - it's time is over -- MIDRANGE-L

Joe,

1) That's why we've taken the approach of using SQL to gather the info and
stored-procedure RPG updates to change it. Works like a charm. Of course we
have been known to assemble a result set in RPG to pass back to the
web-server from time to time. Given that 80%[1,2] of systems are input and
20% are update I think SQL has it's place for sure.

2) Your logic holds ONLY for single row updates. As soon as you hit
multi-row updates, or updates based on the result of a select if it's all in
one statement, SQL will kick native-io's ass every time. Reason is simple,
native-io is driven from within the RPG (Cobol) program, you have to
traverse into MI for each update. However, SQL is already below the MI and
it stays there until the update is complete.

-Walden

[1] Did you know that 92.34% of all statistics are made up? <G>
[2] But think about it, Order entry updates 1 order header row and 3 order
detail for something with 3 lines. It reads, customer, item, price, quantity
discount, customer discount, item balance by location, customer address,
tax, shipping... And that is for a simple transaction.

------------
Walden H Leverich III
President
Tech Software
(516)627-3800 x11
WaldenL@TechSoftInc.com
http://www.TechSoftInc.com



-----Original Message-----
From: Joe Pluta [mailto:joepluta@PlutaBrothers.com]
Sent: Wednesday, November 14, 2001 8:43 AM
To: midrange-l@midrange.com
Subject: RE: Green screen - it's time is over


I get sucked into this conversation every once in a while because I just
can't stay away from it.  There are several ways to answer your question,
and, despite my reputation, I'll try to be concise and outline just two.

1. Benchmarks.  I've released many over the last of year or so that show SQL
to be anywhere from 50% to 500% slower than record level access, at least
for database update.  For queries, it can be faster.  Whenever you talk
about SQL performance, you really need to separate queries from database
update.

2. Thought exercise.  Let's use common sense, just for a moment.  SQL uses
all the same database routines as record level access.  The primary
difference is something called a "query optimizer", which is what makes
multi-file joins and some other set-based queries faster than traditional
HLL approaches.  For updates, especially record-at-a-time updates like we
normally do in transactions, SQL basically adds extra work (parsing the
statement, determining column positions and the like) prior to executing the
same database routines the HLL executes directly.  So, unless the SQL
overhead actually executes in negative computer cycles, it will take longer
than the HLL record level access.

There are a few exceptions.  Queries that require accessing different data
in different files based on the contents of a field are not particularly
fast in SQL (not to mention that they can be a bear to code).  But in most
cases it seems that the query optimizer does a good job of making data
access faster in SQL.  On the flip side, there are occasional situation
where SQL is better for database updates, usually in cases involving large
blocks of records being updated.

So, back to your situation, Rob.  Tell us a little more about the
application, rather than simply saying "SQL is FASTER than traditional
access", because the correct answer is (as usual in these things):
"sometimes".

Hey, that was reasonably concise!  I'm outtahere...

Joe Pluta
www.plutabrothers.com



> -----Original Message-----
> From: rob@dekko.com
>
> I don't buy your argument at all about SQL being slower.  A gentleman
> here
> - and no SQL fan for sure - started using SQL just because it's
> FASTER than
> traditional access.  And he writes 'real world' code for ERP.  He finally
> messed with some blocking factors (much easier in RPGLE) and went back to
> traditional access because now SQL was only FOUR times faster than
> traditional access and that difference he could live with.
>
> Rob Berendt

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.