RE: sql exec speed. was AS/400 STILL wearing the undeserved "closed system" moniker

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

I knew I was going to get flack for the "make me ill" phrase.  It was
thoughtless, and I apologize.  There are of course situations where
wholesale price changes occur.  However, when you're getting to the point of
making price changes by style/store, it begins to make more sense to drive
this by another table.

But let's think about that.  You could theoretically make a nice,
sophisticated SQL that would update the price table through a join with the
price change table - that would indeed be a really good use for SQL.  In
fact, any table-driven update of a file could be converted to an SQL
statement.

As I see more of these examples, I'm slowly becoming convinced that you
should divide your application processing into "set-based" and
"record-based" processing.  Set-based processing - normally batch updates of
one type or another - may well be more efficient when written in SQL.  There
are still the issues that come with SQL - it can be hard to read and
debugging a complex SQL statement can be quite problematic.  It's usually
easier to debug native I/O, because you can step through the logic, and
perhaps more importantly, you can comment individual lines.  No matter;
there are definitely situations where SQL has advantages.

By the same token, though, there are a lot of processes that do not benefit
from SQL, and attempting to use the SQL screwdriver to pound in a native I/O
nail is just as inappropriate as dismissing SQL out of hand.

So maybe what someone ought to do is take the time to broadly categorize I/O
requirements into the two camps: SQL and native.  Rather than use one tool
for everything, use the right tool for the right job.

Joe


> -----Original Message-----
> From: Jim Damato
>
> It depends what you mean by "item".
>
> When I worked for a retail apparel company I found that most of
> the business
> was style driven.  A style of blouse might encompass five colors and six
> sizes.  Prices were maintained at the style/color/size/store
> level (for us,
> around 150 stores).  Price review or markdown of an "item" might impact
> thousands of records.  Also temporary and permanent pricing was
> changed on a
> weekly basis.  The example that makes you ill would have happened
> many times
> each day, though the WHERE clause might be applied to style, style/color,
> style/store, or style/color/store.