Re: Embedded SQL - performance question

Reordering some of your questions...

On Fri, May 27, 2011 at 2:47 PM, Bryce Martin <BMartin@xxxxxxxxxxxx> wrote:

Is it more efficient to code a monitor than to do a chain/if found? I've
never run a test, nor had I thought to until now.

Yes, as no DB I/O is required to setup the exception handling via
monitor (you'll find some posts from Barbara about this) If the
record isn't expected to be found, ie. you're going to have a low
exception rate, then just doing the insert and handling the exception
will be quicker. Same thing apply to convert from numeric/char to
date...if you expect the data to be good, you'll save time with
exception handling via MONITOR or ERROR, instead of validating the
data before converting.

essentially... the record would need to be added within the amount of time
that it takes to do an "if %found" check...  If your machine is issuing
updates and writes in such a tight process then you would be better off
using commitment control instead of the wild west maverick database i/o
most of us IBM i programmers are used to.

True, it's unlikely if the chain, %found() and write are done right
after each other...but on a large, high volume, multiple user
system...it could happen :) Also if there's logic between the codes,
as is often the case, now you've extended the window.

chain;
if %found();
update;
else;
write;
endif;

Or it's equivalent is an issue for all DBs, do some googling on
"upsert" or "if exists update else insert" :)

Which, could be mitigated just
by adding the error check inside the "if %found" like so...

chain;
if %found;
 update;
 on-error;
   <uh oh...that was a crazy fast hijack>
else;
  write;
endif;

No the error handling needs to be on write, as I've shown...