SQL Server Performance

Column selection for nonclustered index on temp table

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Dec 15, 2009.

  1. atulgoswami New Member

    Suppose, a temp table (#tmpCR) is created in one stored procedure and this temp table has 10 columns.
    Now, this temp table is referenced multiple times (15 times with 15 different tables) on combination of columns (CLID, CVID) on following pattern
    1. JOIN on CLID and CVID column is used 10 times
    2. JOIN on CLID column is used 5 times

    The usage is like
    Table1.CL.ID = #tmpCR.CLID AND Table1.CVID = #tmpCR.CVID

    Table2.CL.ID = #tmpCR.CLID AND Table2.CVID = #tmpCR.CVID

    Table3.CL.ID = #tmpCR.CLID AND Table3.CVID = #tmpCR.CVID

    and similarly with others on different tables

    Now, what i am thinking, this as pattern and came up with a idea to put two non clustered index on this temp table

    First Non clustered Index will be with columns (CLID, CVID)
    Second Non Clustered Index will be on column (CLID) because of its usage

    My thinking is if two columns are used in JOIN operation with one table then put together in one Non Clustered Index and at the same time if any of the column (which was used in combination of the columns) is used multiple times then add one more non clustered index with that single column only for faster retrival of data .

    I am not sure whether it is a correct approach while deciding the columns for non clustered index or not and would like hear from experts on this.
    Thanks in advance

  2. Adriaan New Member

    I guess it depends on the selectivity of CLID and CVID.
    If you look at repeating values on each column individually, and one column has a significantly larger amount of repeating values than the other (say CLID is a client id, and CVID is an order id) then a single non-clustered index on (CLID, CVID) would do fine for both queries. It may even be sufficient to have a single non-clustered index on (CLID) alone ...
    If they are both equally selective, (a) with little or no repeating values, then indexes won't make much of a difference - although it would be nice to have at least a PK on the temp table, (b) with many repeating values, then a separate index for each column may be the best option.
    Finally, the overhead of building (an) index(es) may not be worth it if the temp table will hold just a handful of rows.
  3. atulgoswami New Member

    This SP is for reports but reports run on the same database, there is no separate report server.
    And this report runs for a single client or group of clients

    Let's assume this report runs for a single client then CLID is just single value (repeating value) but order id is different
    It means, What i was doing, is not correct, defining non clustered index on columns based on their usage in JOIN operation.

    I am still not clear as so far i have experienced quite different results in terms of performance with this approach.
    It would be great if you could give me some more details on this.
    Thanks a lot for your reply.
  4. Adriaan New Member

    Your idea of using indexes to support joins is fine in itself.
    Since the CLID is a single value, I would use it in the WHERE clause, and against the permanent table - no need to join on it. An index covering that column on the permanent table would definitely be a good idea!
    And finally an index on the temp table for (CVID).

Share This Page