SQL Server Performance

JOIN on derived table

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

  1. atulgoswami New Member

    I just came across one scenario, where JOIN was on derived table columns and two columns from this derived table query has index on it. This piece of code is causing a huge row scan under hash match operation.

    I am wondering if i put the data from derived table into a temp table (with proper NC index), then would it make any performance improvement.
    In other word, JOIN on derived table Vs JOIN on temp table (which is filled with derived table data) .
    Thanks in advance
    atulgoswami, Mar 22, 2010
    #2

  2. FrankKalis Moderator

    Before using a temp table I would check whether the index is actually used or not and if not, find out why not. Maybe you can imporve the index by modifying it. In that regard it may be worth looking at the INCLUDE clause for indexes.
    FrankKalis, Mar 22, 2010
    #3

  3. satya Moderator

    Good point by Frank (by default [:)]).
    Are you getting exact rows after the execution or estimated rows?
    Also try to run WHERE condition like '<Value>%' this one would not perevent
    the optimizer for using index.
    satya, Mar 24, 2010
    #4

Share This Page