SQL Server Performance

Temp Tables and Indexes

Discussion in 'Performance Tuning for DBAs' started by Unsure, Aug 10, 2008.

  1. Unsure New Member

    Procedure 'Proc_Level3' calls procedure 'Proc_Level4'. Procedure 'Proc_Level4' populates a temp table.
    I created an index on the temp table. My 'create index' statement is in 'Proc_Level3'. The index keys are based on the columns used in the join statement (in Proc_Level3) that uses the temp table. However, the index is not being used. So, a table scan of the temp table is taking place.
    I tried creating the index in 'Proc_Level4' instead of 'Proc_Level3', still wasn't used. The number of rows in the temp table range between 25,000 - 50,000.
    Any suggestions?
    Thank you.
  2. Adriaan New Member

    Depends on if you're filtering on the indexed column in the temp table. If you're not filtering, then all rows need to be read anyway.
  3. satya Moderator

    Try creating filtered indexes in this case and use that hint in order to force usage, check the execution plan too.
  4. FrankKalis Moderator

    Can you please post the relevant parts of your code?
  5. Unsure New Member

    Thank you all for your replies.
    The temp table is only being used in join statements. There are no filters specified in the query.
    I did use the index hint. The optimizer uses an Index Scan. Since the number of rows in the temp table is 25,000 and above, thought by creating an index on the columns used in the join statements, an Index Seek would be used.
    from #temp_proc_level4 T1 with (index(idx_temp_proc_level4))
    inner join Table2 T2
    on T1.reg_id = T2.reg_id
    inner join Table3 T3
    on T1.emp_id = T3.emp_id
    left outer join Table4 T4
    on T4.emp_id = T3.emp_id and T4.start_date = T1.start_date
    left outer join Table5 T5
    on T1.emp_id = T5.emp_id
  6. Adriaan New Member

    If all rows from the temp table are used in the join, with no filtering, then there is no point in doing a seek - all rows must be read to make up the join.
  7. Unsure New Member

    Yes, that is true. The Index Scan that the optimizer is using is the best option in this case. Thank you.
    I have created indexes on all the temp tables used in join statements, however, in some cases I get "Missing Statistics" displayed in the (graphical) query plan. Why is that the case?
    Thank you.

Share This Page