Question regarding index

Question

I have the following query:

            SELECT 
                  M.Col7,   
                  M.Col8,   
                  M.Col9,   
                      M.Col10   

                FROM    [MyTable] M                    

        WHERE   M.Col1 = COALESCE(@Col1, M.Col1)              
                AND M.Col2 = COALESCE(@Col2, M.Col2)              
                AND M.Col3 = COALESCE(@Col3,              
                                                M.Col3)              
                AND M.Col4 = COALESCE(@Col4,              
                                                 M.Col4)              
                AND M.Col5 = COALESCE(@Col5,              
                                                    M.Col5)              
                AND M.Col6 LIKE COALESCE(@Col6, M.Col6) +'%'

I have a combined non clustered index on col7,8,9,10 columns. The query is running fine if I remove the where clause. But as soon as I put the where clause the query is taking long time to execute. My table has 200 K rows. Now I am thinking to put a single non clustered index with columns in this order Col1,2,3,4,5,6. Am I doing right to make it fast or what should be the best option for this?

Those `COALESCE`s are likely to screw up any use of indexes. — Damien_The_Unbeliever, May 31 '11 at 10:18
How would I make use of index then? I am getting @Col1,@Col2,@Col3 from outside and they can be null. — Rocky Singh, May 31 '11 at 10:29

score 3 · Accepted Answer · edited May 23 '17 at 12:12

3

Try this to have a covering index

CREATE INDEX IX_foo ON MyTable
   (Col1,Col2,Col3,Col4,Col5,Col6)
   INCLUDE (Col7,Col8,Col9,Col10)

Other thoughts:

ISNULL is better then COALESCE because of how datatypes are handled
See Why IsNull is twice slow as coalesce (same query)?
The (ISNULL OR ..) pattern has been optimised (to a point, YMMV)
See Why IsNull is twice slow as coalesce (same query)? again
The column order should be most selective first (WHERE order doesn't matter)

Other:

What are the clustered index and primary keys?

Edit, to explain the WHERE comments

WHERE
    M.Col1 = ISNULL(@Col1, M.Col1)
    AND
    ...

OR

WHERE
    (@Col1 IS NULL OR M.Col1 = @Col1
    AND
    ...

edited May 23 '17 at 12:12

Community

1
1

answered May 31 '11 at 10:18

gbn

422,506
82
585
676

You mean to say M.Col1 = ISNULL(@Col1, M.Col1) will use index and M.Col1 = COALESCE(@Col1, M.Col1) not? – Rocky Singh May 31 '11 at 10:28
For me ISNULL(@Col1, M.Col1), M.Col1 = COALESCE(@Col1, M.Col1) and (@Col1 IS NULL OR M.Col1 = @Col1 all are doing index scan instead of seek – Rocky Singh May 31 '11 at 10:42
@Rocky Singh: I said "try it". Did you add a new index as I suggested? Did you change column order to match selectivity? – gbn May 31 '11 at 11:05
I have a query reagrding how you decided which columns to keep in included and which to not and in which order? – Rocky Singh May 31 '11 at 12:46
@Rocky Singh: Quite simple. Key columns = filtered, JOINed and ORDERing. INCLUDE = SELECT clause only. – gbn May 31 '11 at 12:50

Mitch Wheat · Answer 2 · 2011-05-31T10:27:56.057

0

As well as creating a covering index,

CREATE INDEX NC_Col1Col2Col3Col4Col5Col6_I_Col7Col8Col9Col10
   ON MyTable(Col1, Col2, Col3, Col4, Col5, Col6)   
      INCLUDE (Col7, Col8, Col9, Col10)

I would benchmark using ISNULL() rather than COALESCE() and put WHERE clause in a SARG'able form so that an index can be used, e.g.

WHERE  (@Col1 IS NULL OR M.Col1 = @Col1)
       AND   -- etc...

edited May 31 '11 at 10:27

answered May 31 '11 at 10:22

Mitch Wheat

295,962
43
465
541

"and put WHERE clause in a SARG'able form so that an index can be used" can you please show me the query which I should make so as to make ir sargable? – Rocky Singh May 31 '11 at 10:24

score 0 · Answer 3 · edited May 23 '17 at 12:26

0

Covering index for query like @gbn suggested probably is the best idea.

From the other hand covering index for a lot of columns is not a good idea...

I would try (if possible) cluster index on most selective column in WHERE clause.

This minimalize size of index and give direct access to all other columns -- maybe this will be enough to speed your query up.

edited May 23 '17 at 12:26

Community

1
1

answered May 31 '11 at 10:30

Grzegorz Gierlik

11,112
4
47
55

score 0 · Answer 4 · answered May 31 '11 at 11:17

Rocky,

All the answers given so far will help. But I have to say that I believe the issue is deeper than a missing index.

Firstly, pure guessing, but the fact that you already have 200k rows in the table suggest to me that there are plenty of inserts happening, even if not, to run a function over 200K rows will be slow, and slow down the more rows you get.

I suggest using CASE statements in your where clause. This will eliminate the FUNCTION call multiple times for every row it scans. Also it will produce a much better query execution plan for SQL Server, or rather allow SQL Server to pick a more optimal plan in my experience.

So here is the modified query:

SELECT M.Col7, M.Col8, M.Col9, M.Col10   
FROM [MyTable] M                    
WHERE M.Col1 = CASE WHEN @Col1 IS NULL THEN M.Col1 ELSE @Col1 END
    AND M.Col2 = CASE WHEN @Col2 IS NULL THEN M.Col2 ELSE @Col2 END           
    AND M.Col3 = CASE WHEN @Col3 IS NULL THEN M.Col3 ELSE @Col3 END             
    AND M.Col4 = CASE WHEN @Col4 IS NULL THEN M.Col4 ELSE @Col4 END              
    AND M.Col5 = CASE WHEN @Col5 IS NULL THEN M.Col5 ELSE @Col5 END              
    AND M.Col6 LIKE CASE WHEN @Col6 IS NULL THEN M.Col6 ELSE @Col6 END +'%'

Hope this helps. Then add @gbn's index (+1).

Try it and look at the query execution plans. Also another possibility is to do dynamic SQL and then execute it using sp_executesql, or a using if-else statements and then only have the where conditions that matches your inputs. — Ryk, May 31 '11 at 23:05

Question regarding index

4 Answers4

Linked