Re: Embedded SQL - performance question

Nice point, Joe.

Sadly, error-handling for things like this (rare situations which can be
very problematic on those occasions when they happen) is something that many
programmers (good and bad) leave to the end of the coding cycle, or just
don't cater for at all.

One of the reasons why many of my customers buy my software (CA 2E, aka
Synon/2E)* - because it generates code for pretty much every sort of error
situation, including the rare ones like this...

Say what you will about CASE tools and generated code, it's *very* rare to
get a runtime error, because of all the extra (ugly, boring,
difficult-to-read) code which checks for duplicate records, locked records,
external updates (by another user while you're editing the record), null
updates (updates where you didn't change anything) and all that other stuff
that programmers *should* put in their code but often don't.

Rory

* When I say "My software" I mean "The software that I, and a team of
others, develop", not "my software" like I wrote the whole thing, obviously
- I've only been developing it for 14 years, but it's been around for more
than 20 years...

On Sat, May 28, 2011 at 6:11 AM, Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>wrote:

On 5/27/2011 7:20 PM, Barbara Morris wrote:

On 5/27/2011 2:47 PM, Bryce Martin wrote:

essentially... the record would need to be added within the amount of

time

that it takes to do an "if %found" check...

It's not just the time taken to do the "if %found"; its the time between
when database determined there was no record to the time that your WRITE
request reaches database.

Say some other job was starting a WRITE at around the same time as your
CHAIN, but when when database does the check for the record for your
CHAIN, the WRITE operation in the other job hasn't reached database yet.
When your job finds that the record is not there, it will start its own
WRITE operation, but that other job had a head start, and would have got
the record in first.

This is a great point and it really brings up a larger discussion about
application architecture. How you handle this situation truly depends
on the application. If, for example, this is a dedicated work file that
no other job has access to, clearly you can forego the constraint
check. You can even allow a hard halt, since it's obviously some sort
of program error (although your willingness to deal with hard halts is a
matter of preference).

On the other end of the spectrum, if multiple jobs can reasonably add
the same key, then you absolutely need monitor (or error extender), and
you may wish to go the belt and suspenders approach of also doing a
check prior to the write, although that seems a bit of overkill to me.
You then, of course, need extra logic to determine what to do in the
case of a duplicate, which will depend on the application.

Many programs, though, fall somewhere in the middle. Let's take, say,
order entry, and let's further say you have a control structure (data
area, file, whatever) that controls the next order number. It would
almost always be a programming error for two different people to be
adding the same order at the same time. Customer master maintenance is
a little different, in that it would be a procedural error if two
different users were entering the same customer. In either case, you
should certainly monitor for the error, but does it make more sense to
monitor the error at the point of write or globally? Even with order
entry, it could be an online order entry program or a batch EDI
processor; the error handling might be different. Error at the point of
occurence gives you more control at the cost of extra coding, while
global monitoring gives you complete coverage but less granularity.

Error handling is always a complex topic. I'm glad RPG gives us so many
ways to handle errors; it allows for a robust architecture no matter the
situation.

Joe