CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2008indexing
Rocky Singh
Rocky Singh

Reputation: 15440

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

Upvotes: 0

Views: 553

Answers (4)

Ryk
Ryk

Reputation: 3102

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

Upvotes: 0

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

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.

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300769

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

Upvotes: 0

gbn
gbn

Reputation: 432657

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:

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

Upvotes: 3

Related Questions