I stand by the fact that single record chains and updates still
outperform SQL.
Sorry if I chose a bad example, it was a quick one. I _agree_ with you
that comparing single-row to single-row, HLL/IO will be faster. My
(poorly made?) point was, if you rethink the process, you have much less
single-row IO then you think.
For example, your code above fails miserably if
there is no item master. And as we all know, item masters disappear.
What? And how would that happen? Seriously? _IF_ you had a well-designed
system that had the foreign-key references in place that told DB2 that
the order-detail-line table referred to the item-master table, how,
exactly, would an item go missing? Yes, 15 years ago no one used DRI,
but what's the excuse these days. We have a real database, why not use
it?
Also, if that did happen? How do you deal w/it in HLL/IO? It's an
exception no matter what. The customer ordered item 2211 and there is no
item 2211. Now what? Description: "*Not Found" Price: 0.00? Ewwww. Do
the right thing and tell the database to prevent the problem in the
first place.
Do you actually have a record in a file for every item/date
combination?
Of course not. You have a new row for each item when the price changes.
So for item 2211 you may have 3 rows across history:
Item EffDate Price
2211 2001-01-01 18.98
2211 2003-07-21 19.47
2211 2006-11-19 20.21
That is the point of the subselect, which I now see has a bug. :) The
subselect should have been:
(select top 1 IP.Price from ItemPrice IP
where IP.Item# = I.Item#
and IP.EffDate <= O.OrderDate order by EffDate Desc)
Hey, just proves the point that SQL can have bugs too. :)
That's because those of us who write ISAM code for a living would have
gotten
the order header when the user first selected the order, and we
wouldn't
read it again.
The point of joining to OrderHeader was to get the orderdate for
determining the price, not to select the order date for output, and it
wasn't in my output list. I agree, the order header would have been
selected earlier in the process. And in this case I would expect the
query engine to only access the order-header one time anyway.
So, while your example sort of shows that maybe SQL could get some
information a little quicker, it also shows that SQL has some
challenges
(and that's me being about as non-argumentative as I can be) when it
comes to writing real world business applications.
Can you (editorial use, not Joe specifically) always find an example
where HLL is better? Sure. But actually (my bug not withstanding) that
one statement gets real business information from three different
tables, and does it more efficiently (from a runtime, development-time
and maintenance-time point of view) than several dozen lines of RPG
setlls, chains and f-specs. And less we argue over efficiency, I'll just
say, that _I_ would prefer to maintain the SQL. Others may have
different opinions.
-Walden
midrange.com
