Indexing issue in sql server

Question

hey guys, i have a query in sql server which takes atleast 10-15 seconds to execute, and when this is called in asp.net, it is more worst there, it just throws request timeout error.

Below is the query i am using.

SELECT C.Id,
       C.Summary,
       C.Title,
       C.Author,
       CONVERT(VARCHAR(12), C.PublishDate, 104)
       AS 'DATE',
'/Article/' + SUBSTRING(dbo.RemoveSpecialChars(C.Title), 0, 10) + '/' + CAST(CA.CategoryId AS VARCHAR(MAX)) + '/' + CAST(C.Id AS VARCHAR(MAX)) +
'.aspx' AS
'URL'
FROM   CrossArticle_Article C
       INNER JOIN CrossArticle_ArticleToCategory CA
         ON C.Id = CA.ArticleId
WHERE  C.Title LIKE '%' + @KEYWORD + '%'
        OR C.Summary LIKE '%' + @KEYWORD + '%'
        OR C.Article LIKE '%' + @KEYWORD + '%'  


    SELECT @@ROWCOUNT

Below are the Fields Specification.

Id int Primary Key
Summary nvarchar(1000)
Title nvarchar(200)
Author nvarchar(200)
PublishDate DateTime
CategoryId int PrimaryKey

i think this can be resolved by using Indexing on these columns using include.. i checked over net, but didnt find any solution..

i would appreciate if i could get help for the same. Thanks and Regards Abbas Electricwala

Answer 1

Ordinary column indexing most likely cannot help your query, unfortunately. LIKE conditions can only be assisted by indexes when they are in the form of value% (meaning that you can only have a wildcard on the end of the expression; the prefix must be static).

I am assuming that you already have an index on CrossArticle_Article.Id and CrossArticle_ArticleToCategory.ArticleId. If not, you should add those.