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


******************
*** (Eric DeLong)

I think, with indexes as you have described, that the join approach would be most efficient. Have
you tried both approaches to determine their relative performance? Visual explain could be helpful
in this respect...

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

(Larry Loen)

I assume by "the same results on Visual Explain" you mean that you got
the identical access plan for each?

This doesn't surprise me. In my "15 second" analysis of the two
statements, they look functionally identical, right down to where nulls
are allowed or not.

If you did, in fact, get identical access plans, that's a pretty good
clue that they are identical.

It would then boil down to whether the optimizer continued to recognize
this (and whether it continues to be true) as you moved from a single
field example to something more complicated. Continuing with Visual
Explain seems like a good way to check it out.

See also:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/4f662bf7-0766-4201-a772-dcab5b9a5f08

. . .for an example I googled up that looks a lot like yours.

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

(Charles Wilt)

Note that one of the capabilities of the SQL query engine is the
ability to re-write a statement into an equivilent but better
performing one. For example, converting from embedded selects into
JOINs

Since you say that you saw the same results for both statement in VE,
it's likely that the version with the embedded selects is being
re-written.

*****************
(Chuck Pence)

Presumably /query rewrite/ reduces both and thus recognizes both, as the /same query/ request for
which both are optimized the same.

If the result set should be small, I would prefer to write the query to be pretty versus
efficient :-) In that case, using a UDF for both the /state name/ and the /customer description
with the respective code as the input. Presumably I would have use for those same deterministic
UDFs in other situations, since there should only be one copy of the data against which to correlate
the encoding to its appropriate text string. In that case I would use [while also eliminating A.*
and arranging code fields with its text following]:

SELECT A.cName
, A.sc1, StateName(A.sc1)
, A.sc2, StateName(A.sc2)
, A.cc1, CustDesc(A.cc1)
, A.cc2, CustDesc(A.cc2)
FROM A.Cust

**********


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis Rodriguez
Sent: Thursday, May 14, 2009 11:09 AM
To: Midrange-L
Subject: SQL View - Embedded Select vs Join


Hi,

Let's suppose three files:

----------
File1:
CustomerName, State_Code1, State_Code2, Cust_Code1, Cust_Code2

File2:
State_Code, State_Name (Primary Key: State_Code)

File3:
Cust_Code, Cust_Description (Primary Key: Cust_Code)
----------

I've been asked to write a view that shows the name or description for each one of the FileA codes.

As I see it, I can write the view as sequence of join files:

-----------------
SELECT A.*, B.State_Name as State_Name1, C.State_Name as State_Name2,
D.Cust_Description as Cust1, E.Cust_Description as Cust2
FROM FileA as A

LEFT OUTER JOIN File1, File2 B on A.State_Code1 = B.State_Code
LEFT OUTER JOIN File1, File2 C on A.State_Code1 = C.State_Code
LEFT OUTER JOIN File1, File3 D on A. Cust_Code1= C. Cust _Code
LEFT OUTER JOIN File1, File3 E on A. Cust_Code1= E. Cust _Code
-----------------

OR, I can write it using a Select for each ânameâ field:

-----------------
Select A.*,
(Select State_Name from File2 b where A.State_Code1 = B.State_Code) As State_Name1,
(Select State_Name from File2 c where A.State_Code2 = C.State_Code) As State_Name2,
â
etc., etc,

FROM File1 as A
-----------------------

Which option would you think is more efficient? Interestingly enough, a small test I did with only one field yielded exactly the same results on Visual Explain.

Thanks in advance,


Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions