Why is my view slow when I have indexed the columns?

Question

In SQL Server I have put a clustered index on a view to eliminate the inefficiency of a join using LIKE statements:

   CREATE VIEW KeywordCount WITH SCHEMABINDING
    AS
    SELECT 
        K.ID AS KeywordID
        ,COUNT_BIG(*) AS KeywordCount
    FROM dbo.Grants G
    INNER JOIN dbo.GrantStatuses GS2 ON GS2.ID = G.StatusID AND GS2.Status NOT IN ('Pre-Submission', 'Awaiting Signatory Approval', 'Modifying', 'Closed')
    INNER JOIN dbo.Keywords K
        ON G.Keywords LIKE '%' + K.Word + '%'                                           --It's one of the grant's keywords
        OR G.Title LIKE '%' + K.Word + '%'                                              --Word appears in the title
        OR Replace(G.Title, '-', ' ') LIKE '%' + Replace(K.Word, '-', ' ') + '%'        --Word with hyphens replaced appears in the title
        OR G.Synopsis LIKE '%' + K.Word  + '%'                                          --Word appears in the Synopsis
        OR Replace(G.Synopsis, '-', ' ') LIKE '%' + Replace(K.Word, '-', ' ')+ '%'      --Word with hyphens replaced appears in the synopsis
    GROUP BY K.ID
    GO

    CREATE UNIQUE CLUSTERED INDEX IX_KeywordCount 
        ON dbo.KeywordCount (KeywordID)
    GO

Then I added another index on the KeywordCount column:

    CREATE INDEX IX_KeywordCount_Count 
        ON dbo.KeywordCount (KeywordCount)
    GO

So why does the following query take 7 minutes to run? Shouldn't the index give me much better performance?

    SELECT TOP 10 * FROM KeywordCount ORDER BY KeywordCount DESC

EDIT Thanks everyone, but I know that LIKE statements and REPLACE will make this view inefficient. That's why I added the clustered index. I thought that putting a clustered index onto the view would materialize the data into a table so that the database would not have to do the joins. The query plan does say that it is doing the joins. Why is that?

Solution 1

I found the solution in this article: http://technet.microsoft.com/en-us/library/cc917715.aspx

SELECT TOP 10 * FROM KeywordCount WITH (NOEXPAND) ORDER BY KeywordCount DESC

For some reason the query plan wasn't using the index, but I added the WITH (NOEXPAND) hint and my query ran instantly - many thanks to Quassnoi for pointing out the correct thing to do.

Solution 2

Please note that WITH (NOEXPAND) means that the view should only look at the indexes, and not the table data. That means if the indexes are not up to date the view will not be either.