Re: Working with tables, best method

Walden H. Leverich wrote:

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.

My point? I don't think so. You have to prove it to me. You didn't with this for sure.

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?

Because we don't all have perfect databases, Walden. Sorry, I live in the real world. We import data from other sources. We merge companies. We use legacy databases. If you can count on perfect data integrity, then you may have a point, but a whole lot of people on this list will tell you their data isn't perfect and they don't have the time to fix 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.

No argument that it's icky, but it happens. At least in the real world. In an HLL you get an exception. In yours, the record is skipped. That's ugly.

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. :)

Right, but it's only one pricing methodology. You have worked on order entry systems, right? So you know that there are dozens of possible pricing strategies, and the one to use usually depends on a flag in a completely different table, typically an item class or customer class table, or both.

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.

Yeah, but it returns it over and over! In fact, one of the things I absolutely cannot stand about joins, especially in multi-tiered environments, is that duplicate header information is sent ON EVERY ROW! This to me is perhaps the most egregious act of anti-programming in the entire SQL pantheon. The only thing worse is when the programmer in a fit of absolute laziness does a SELECT * on the join statement.

Of course proper SQL requires you to name every field on every I/O operation. It's definitely an opinion call as to whether that's a plus or minus.

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.

Your SQL was ten lines!!!!

And it didn't handle errors in the database, and the pricing was broken! And the NATIVE I/O GUY saw the problem before the SQL GUY!

<laughing>

Anyway, the point is this. I got disgruntled because you insinuated that SQL is somehow better than native, and you started down the path that "proper SQL will make you rethink your approach and you will be a better programmer" (I'm paraphrasing, but you get the gist) and the truth is that simply isn't the case. Good programmers think correctly regardless of the tools. Our job is to select the right tool, and the SQL hammer does not fit every database nail.

Joe