Re: Working with tables, best method

I've never seen this brought up before so I thought I'll put this out
there. I can explain why but maybe someone else could. I've found that if
you join tables together that it's better that you put your where clause in
the join instead of the "where". Also putting the smaller table first
seems to help.

-- Normally you'd code it like.
SELECT * FROM VENDROMAS a
JOIN VENDORHDR b
ON a.VENDOR = b.VENDOR
WHERE a.VENDOR = :hostVendor

-- Without the where clause.
SELECT * FROM VENDROMAS a
JOIN VENDORHDR b
ON a.VENDOR = :hostVendor
AND a.VENDOR = b.VENDOR

This might actually be a poor example. But I have a programmer next to me
that actually saw the benefits of doing this with a more complicated query.
He had a web report that was timing out when he had vendor select in the
where clause. I told him to move it up to the join. The web report
responded back immediately when going over a large amount of data. The
good thing for me is that two project managers saw it too along with the
I/T head guy. :-) I guess I'm saying don't limit your join criteria
only to the field a to field b type of thing.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.

rpg400-l-bounces@xxxxxxxxxxxx wrote on 02/14/2008 06:42:37 PM:

Walden H. Leverich wrote:

There are certain classes of operations where native I/O (SETLL,

CHAIN,

etc.) is faster, often much faster, than SQL.

We've beaten this to death, and I won't disagree, with (of course) one
caveat. Yes, single-row IO is faster in native, but w/SQL you may have
many fewer IOs. Take, for example, loading an order-detail subfile. Say
you show line#, item#, ordered-quantity, item-description and price.

If you did the same process in SQL then the native IO would be clearly
faster. But, you shouldn't use the same process, you should rethink the
process and use a single statement to get it all:

Select OL.OrderLine, OL.Item#, I.Description,
(select top 1 IP.Price from ItemPrice IP
where IP.Item# = I.Item#
and IP.EffDate = O.OrderDate)

From OrderLine OL

Join Order O
on OL.Order# = O.Order#
Join Item I
on OL.Item# = I.Item#
Where OL.Order# = 12345

I don't want to fight. Really I don't. But this sort of generalization
gets my hackles up, for any number of reasons. Right off the bat I
already said it is single record functions such as those used in
transaction processing where indexed I/O outshines SQL. So what do you
use to argue? A subfile query. An order detail subfile is by
definition a query, and so there is a chance it will perform quicker.
In fact, your example falls squarely in the middle category I spelled
out -- because it may or may not be faster.

I stand by the fact that single record chains and updates still
outperform SQL.

But that's not the issue. I'm far more worried that you're doing what a
lot of SQL advocates do: you're attempting to show the "coolness" of SQL
-- that you can get records from multiple tables magically -- but
you're misapplying it. That's because you're skipping just about every
non-trivial portion of a business application, which is what SQL tends
to make people do. For example, your code above fails miserably if
there is no item master. And as we all know, item masters disappear.
So in the real world for that reason alone your code won't work. And in
fact, nearly every foreign key JOIN fails for the same issue. There are
dialectical ways to make sure that doesn't happen but the more you do it
the more your code starts to balloon, which is why you rarely see it in
an SQL example.

Then you do the ultimate no-no: you attempt to put some business logic
in your SQL. I assume your ItemPrice table is some sort of price file.
That sort of single-level dated file wouldn't work in any system I've
ever seen. Do you actually have a record in a file for every item/date
combination? Of course not. So you gave us some code that is
functionally useless in the real world. But that's because if you tried
to encode anything like real logic, with effective and discontinue
dates, pricing by customer class and item class, geographical costs,
deals and promotions or really anything a real world business would use,
well you'd end up with a real eyesore. That sort of code in an SQL
statement is like me in a bathing suit - it doesn't look good and
frankly it frightens the children.

So really the only valid SQL out of the whole thin is this: Select
OL.orderline, OL.item#, O.Ordetdate from OL join O on OL.order# =
O.Order#. Yeah, that might work better than two chains.

Of course, RPG programmers likely wouldn't be doing two chains. 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. Silly us! We actually optimize our own code! We don't
need an SQL engine to do it for us!

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. And the SQL refrain
"get it all in one operation" really isn't as simple to apply as some
might have you believe.

Really, Walden, I'm not anti-SQL. It's just not the right tool for
every business process.

Joe








--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.