SQL Server Performance

Case Statement in JOIN Condition

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Jan 6, 2010.

  1. atulgoswami New Member

    Can we re write below statement in any other different way? It is causing unnecessary Hash Match inspite of proper index.

    ON Tab1.CVID = Tab2.CVID
    AND Tab1.TYID = CASE When Tab1.IsMake = 1 Then Tab3.TYID ELSE Tab4.NBID END

    I thought of writing like

    From Tab1 T1
    ON T1.CVID = Tab3.TYID AND T1.IsMake = 1

    INNER JOIN Tab1 T2
    ON T2.CVID = Tab4.NBID
    AND T2.IsMake <> 1

    I think i am lost somewhere. Any idea/suggestion would be of great help
    Thanks in advance.

  2. Adriaan New Member

    Tab3 and Tab4 aren't even referenced as tables.[:p]
    If those two tables aren't required for the main query, you could do the filtering with subqueries on Tab3 and Tab4 in the WHERE clause.
    In any case, I doubt if it is really an unnecessary hash match, since you're asking the engine to do conditional matching ...
  3. GeriReshef New Member

    Select Tab1.Desc FromTab1INNER JOIN Tab2ON Tab1.CVID = Tab2.CVIDAnd ((Tab1.IsMake = 1 And Tab1.TYID=Tab3.TYID) Or (Tab1.IsMake <> 1 And Tab1.TYID=Tab4.NBID))
  4. atulgoswami New Member

    [quote user="GeriReshef"]Select Tab1.Desc From Tab1 INNER JOIN Tab2 ON Tab1.CVID = Tab2.CVID And ((Tab1.IsMake = 1 And Tab1.TYID=Tab3.TYID) Or (Tab1.IsMake <> 1 And Tab1.TYID=Tab4.NBID))[/quote]
    I tried but did not work. However, i have noticed one strange behavior

    This select query returns 1780 records if i dont add Tab1.IsMake column in select list but when i add this column in select list, then number of rows got increased to 1785.

    I am surprised as i did not change anything in condition or anywhere else. Couple of columns are already used in the select list from this table.
    I am just wondering to know if anyone encoutered this kind of strange behavior.

Share This Page