Search code examples
sqlsql-serverdatabase-performance

SQL server two indexed columns have different speed performance

I have a table which have two columns:

CREATE TABLE #history(
    Today        INT    NOT NULL,
    LastDay      INT    NOT NULL
           PRIMARY KEY(Today, LastDay))

I also created a nonclustered index:

 CREATE NONCLUSTERED INDEX #history_IX1 ON #history(LastDay)

However, when I join table using column Today, the performance would be way more faster than using column LastDay, like 20s vs 4s.

I think the problem may be caused by the column LastDay hasn't properly indexed. I also use SQL hint to force indexing, the results are same.

Here is an example:

INSERT INTO anytable(
       anycolumn)
SELECT m.anycolumn
FROM   AMillionTable m
       INNER JOIN #history h
               ON h.Today  = m.day

m.day also indexed.
Same query, when change h.Today to h.LastDay, the process speed significantly drops.

Any solutions for this problem?

Solution

  • That is normal, because they are 2 different indexes CLUSTERED vs NONCLUSTERED. When you join using the Today column, the CLUSTERED index will be used. Extra work is needed for the NONCLUSTERED index.

    You can find more information here Link