× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



My 2 cents:

I strongly suggest sticking with an explicit JOIN syntax and not depending on the query optimizer rewrite of your embedded selects. This will guarantee your intent and make it easier to maintain long term. I also suspect that in your bigger test (12 files) you may find that the JOIN approach performs better (provided join columns are properly indexed).

I don't think this particular scenario would benefit from a UDF approach, since the result of a sub-select is non-DETERMINISTIC in nature.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: SQL View - Embedded Select vs Join



Thanks to all who responded. My comments follow:

ERIC:
I concur that JOINs should be more efficient (with caching and all that). As I wrote, VE showed the same results with a Test view of one "code" field (repeated twice). If I have the time I'll rewrite the whole thing (there are 24 fields, with a table repeated 12 times!!) in the two modes and, if worth it, will post the results here.

LARRY:
Thanks for the link. Yes, it seems that the optimizer is interpreting the sentences the same way. All the thing being equal, I think I would use the "embedded Select" option, as it would be easier to read (and understand) for my coworkers.

CHARLES:
I agree with your assesment. As I wrote Eric, if I can I'll try to do the full works with both options and check the results.

CHUCK:
I HAD thought of using UDFs BUT, I have had a difficult time just getting people here to use SQL, so I think that writing an UDF would present some maintenance problems. And yes, I also agree that using A.* is not pretty, but this thing here has 57 fields (not counting the join results), and I would hate rewriting the View every time a new field comes up...

Just for the heck of it, maybe I'll write the view using UDFs. Sometimes I like to annoy my coworkers and force them to think a little outside the box a little (and, for most of them, outside the box means things like ILE RPG, /Free, etc.)



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.