Question regarding index

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)              
           开发者_如何学C     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?

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
    ...

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...

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.

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).