Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2003
    Location
    here
    Posts
    19

    Unanswered: Attention all DBA's JOINS VS SUB QUERIES

    Fan of Sub's and I feel more comfortable doing them, I've been told they aren't as efficient as JOIN's by another developer. Is this true?

    Also, does one or the other perform better on SQL Server vs. Oracle? I'm more seasoned as an Oracle developer and have learned to avoid JOIN's when ever possible. So I guess my real question is have I been led astray?



    Thanks in advance, DBA's Rule!
    Program, that's what I do.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Most sub-queries get optimized into joins anyway. I wouldn't worry about it much. Just FYI, SQL Server does not have anywhere close to the number of problems that Oracle has with joins, joins are quite safe and efficient in SQL Server.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm just getting my feet wet with Oracle. I had no idea JOINs were not efficient in Oracle. Why is that? (I mean why are they ineffecient, not why didn't I have any idea.)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    If you can do all the logic in the SELECT of the main query, a JOIN is faster.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    I'm just getting my feet wet with Oracle. I had no idea JOINs were not efficient in Oracle. Why is that? (I mean why are they ineffecient, not why didn't I have any idea.)
    The answer gets really complicated, but it centers around the way that Oracle in general, and PL/SQL in specific has developed over time.

    Oracle has evolved over time to address the needs of certain key segments of the market, with the assumption that if those needs are met, then the rest of the market will follow those key segments.

    One segment that Oracle has addressed well is the TPC testing community. Oracle as a company has traditionally been willing to go to almost any lengths to cooperate with/accomodate the TPC testing groups. Other vendors might be willing to provide similar access, but as far as I know Oracle is unique in this respect right now.

    Oracle has also built a strong relationship with many *nix DBAs over the years. They've expanded their toolset (including PL/SQL) to address the needs of the DBA, even when those added features pose significant problems for query optimization. The net effect is to trade performance/scalability off in order to get DBA loyalty.

    These decisions over time have strengthened Oracle's position in the market. Until recently, they had a defacto strangle hold on the *nix market. Oracle still has a practical lock on the TPC market.

    The net result is that Oracle has made some shrewd decisions over time that have bought them market share, but makes the database engine a bit unstable if you aren't aware of the potential problems. This is why there is a relatively strong market for people that can rescue a crippled Oracle installation after inexperienced DBAs render it partially or completely non-functional.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    some subqueries(correlated) usually mimic a outer join and can be more efficiently written that way
    the performance hit you take is usually attributed to the referencing of the outer query in the inner query this creates a co-relation between the two and forces the query engine to retrive one input from the inner query and pass it to the outer query for evaluation.
    this happens for each row in the initial input.
    you can optimize these by using the EXISTS argument in the where clause of the outer query.
    this does become a more dificult process if the subquery is located in the FROM clause as a derived table.

    Joins are optimized more efficiently and you have a greater level of flexibility with the joins and the types of joins and ultimately the execution plans due in part to the optimizers ability to decide based on cost.
    subqueries appear to be evaluated as independent operations and suffer because of it

    i use whatever works at the time and if it appears correlated i will try to rewrite it using a join.
    i try to limit my subquery use to utility issues like complex update and delete statements.
    Last edited by Ruprect; 07-10-04 at 04:34.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Check this discussion in respect to SUBs vs. JOINs.

    As per Horacle, - it's strength is in the muscles of the HW it runs on. And as Pat mentioned in regards to *nix admins that ironically became Horacle DBA's, - it made an extra effort throughout the releases to adapt to the *nix admin frame of reference, - this is why cursors in Horacle really fly, because they made sure they are faster than even ANSI joins. Why? Because *nix admins (not all, but a few!) have hard time grasping the concept of set-based approach
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dare I cross-post this to the Oracle forum?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Uh-oh, I think I need a body guard...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, I think you'd need a leash and a muzzle, and they'd need a shark-cage.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jul 2003
    Location
    here
    Posts
    19
    Looks like my little query got the reponse of two veteran DBA's. Thanks you for the very useful replies. In a some what related thread I found this example useful too:

    http://asktom.oracle.com/pls/ask/f?p...953229842074,Y

    I'm an Oracle guy at heart learning the SQL Server way.

    Thanks again for the replies.
    Program, that's what I do.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I have NEVER heard so much drivel in my life...well except for last night when I was talking to a Yankee fan....

    An example that FLYs (Now where did I put the complicated ones....)

    Code:
    Set Pagesize 0
    Set Linesize 2000
    Set Serveroutput on
    
    Spool c:\Temp\DisabledChild.csv
    
    Select 	  '"Empl Id","Employee Name","Dpnd Id","Dependant Name",'
    	||'"Medical (10)",'
    	||'"Dental (11)",'
    	||'"Vision Care (14)",'
    	||'"Domestic Partner Medical (15)",'
    	||'"Domestic Partner Dental (16)",'
    	||'"Domestic Partner Vision Care (17)",'
    	||'"LegalCare (1Z)",'
    	||'"Basic Group Life (20)",'
    	||'"Basic AD'||Chr(38)||'D (22)",'
    	||'"Health Care Reimbursement (60)",'
    	||'"Dependent Care Reimbursement (61)"' 
      As Unload_String
    From Dual
    Union All
    select 	'"'||x.emplid||'","'||Empl_Name||'","'||x.dependent_benef||'","'||x.name
    	||'","'||PT10_Ind||'","'||PT11_Ind||'","'||PT14_Ind
    	||'","'||PT15_Ind||'","'||PT16_Ind||'","'||PT17_Ind
    	||'","'||PT1Z_Ind||'","'||PT20_Ind||'","'||PT22_Ind
    	||'","'||PT60_Ind||'","'||PT61_Ind||'"'
    from	
    	(select a.emplid, a.dependent_benef, a.name
    	from 	enr_dpnd_benef a
    	where disabled = 'Y'
    	and	lastupdveh = 'W'
    	and 	relationship in ('D','GC','OC','S','SC')
    	and 	exists
    			((select 1
    	 		from 	enr_dpnd_benef_h h
    	 		where (disabled = 'N'
    	 		or 	disabled is Null)
    			and	h.lastupdveh = 'C'
    	 		and 	a.emplid = h.emplid
    	 		and 	a.dependent_benef = h.dependent_benef)
    	        union all
    			(select 1
    	 		from 	enr_dpnd_benef i
    	 		where i.new_rcd = 'Y'
    	 		and 	a.emplid = i.emplid
    	 		and 	a.dependent_benef = i.dependent_benef))) x
    	,(select name as Empl_Name, emplid
    	  from enr_employ_data) y
    	,(select 'Y' as PT10_Ind, emplid, dependent_benef 
    	  from enr_partic_dpnd b
    	  where b.plan_type = '10') pt10
    	,(select 'Y' as PT11_Ind, emplid, dependent_benef 
    	  from enr_partic_dpnd b
    	  where b.plan_type = '11') pt11
    	,(select 'Y' as PT14_Ind, emplid, dependent_benef 
    	  from enr_partic_dpnd b
    	  where b.plan_type = '14') pt14
    	,(select 'Y' as PT15_Ind, emplid, dependent_benef 
    	  from enr_partic_dpnd b
    	  where b.plan_type = '15') pt15
    	,(select 'Y' as PT16_Ind, emplid, dependent_benef 
    	  from enr_partic_dpnd b
    	  where b.plan_type = '16') pt16
    	,(select 'Y' as PT17_Ind, emplid, dependent_benef 
    	  from enr_partic_dpnd b
    	  where b.plan_type = '17') pt17
    	,(select 'Y' as PT1Z_Ind, emplid, dependent_benef 
    	  from enr_partic_dpnd b
    	  where b.plan_type = '1Z') pt1Z
    	,(select 'Y' as PT20_Ind, emplid, dependent_benef 
    	  from enr_partic_dpnd b
    	  where b.plan_type = '20') pt20
    	,(select 'Y' as PT22_Ind, emplid, dependent_benef 
    	  from enr_partic_dpnd b
    	  where b.plan_type = '22') pt22
    	,(select 'Y' as PT60_Ind, emplid, dependent_benef 
    	  from enr_partic_dpnd b
    	  where b.plan_type = '60') pt60
    	,(select 'Y' as PT61_Ind, emplid, dependent_benef 
    	  from enr_partic_dpnd b
    	  where b.plan_type = '61') pt61
    where 	x.emplid		= y.emplid(+)
    and	x.emplid 		= pt10.emplid(+)
    and	x.dependent_benef 	= pt10.dependent_benef(+)
    and	x.emplid 		= pt11.emplid(+)
    and	x.dependent_benef 	= pt11.dependent_benef(+)
    and	x.emplid 		= pt14.emplid(+)
    and	x.dependent_benef 	= pt14.dependent_benef(+)
    and	x.emplid 		= pt15.emplid(+)
    and	x.dependent_benef 	= pt15.dependent_benef(+)
    and	x.emplid 		= pt16.emplid(+)
    and	x.dependent_benef 	= pt16.dependent_benef(+)
    and	x.emplid 		= pt17.emplid(+)
    and	x.dependent_benef 	= pt17.dependent_benef(+)
    and	x.emplid 		= pt1Z.emplid(+)
    and	x.dependent_benef 	= pt1Z.dependent_benef(+)
    and	x.emplid 		= pt20.emplid(+)
    and	x.dependent_benef 	= pt20.dependent_benef(+)
    and	x.emplid 		= pt22.emplid(+)
    and	x.dependent_benef 	= pt22.dependent_benef(+)
    and	x.emplid 		= pt60.emplid(+)
    and	x.dependent_benef 	= pt60.dependent_benef(+)
    and	x.emplid 		= pt61.emplid(+)
    and	x.dependent_benef 	= pt61.dependent_benef(+)
    ;
    
    Spool Off;
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I DID quantify my statement, didn't it?!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •