RE: SQL View - Embedded Select vs Join

[Luis Rodriguez <luis.rodriguez2@xxxxxxxxx>]

Elvis,

Thanks for your comments. All the auxiliary tables (where the descriptions are located) have the search argument as their primary key, so I hope that helps in the view's response times. 

I find interesting that an UDF containing a sub-select cannot be defined as deterministic. I would have thought that DB2 would check the input parameter(s) first and give the results accordingly. Is there any way to check if an UDF is using a cached result or not? 


Thanks again,

Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions

****************************

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