CODEWITHSUNDEEP
sql-serverperformanceindexingview
Colin
Colin

Reputation: 22595

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

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?

Upvotes: 3

Views: 13280

Answers (3)

Leif Hartung Midjord
Leif Hartung Midjord

Reputation: 41

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.

Upvotes: 1

Colin
Colin

Reputation: 22595

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.

Upvotes: 5

marc_s
marc_s

Reputation: 755411

A LIKE '%' + Replace(K.Word, '-', ' ') + '%' (% wildcards at the beginning of the search terms) will NEVER be able to use any indices. If you use that kind of statement, don't be surprised you'll have full table scans all the time.

If you really need that kind of searching, you need to either be okay with the speed you're getting, or maybe investigate full-text searching.

The other option would be to change your LIKE statements to: LIKE K.Word + '%'

If you use the % wildcard only at the end, SQL Server stands a chance to actually use an index on K.Word and thus speed up the lookup.

Upvotes: 0

Related Questions