asked on

SQL Server LIKE clause and indexes

I'm using LIKE clause in a query and it's causing performance problems.
The query takes a long time to complete.
How to deal with this?
Shall I use full-text indexing?

Thanks,

Felipe

SELECT Getdate() -30 As DatePeriod, E.Localtime, S.Stringvalue, E.UserName, E.Eventtype, Case E.Eventtype WHEN '8' Then 'Success' WHEN '16' Then 'Failure' Else 'UNKNOWN' END As AuditStatus, E.Source, E.EventID, E.Category, S.StringIndex
FROM dbo.Events AS E INNER JOIN dbo.EventsStrings AS S ON S.EventID = E.ID 
AND S.SessionID = E.SessionID 
WHERE (E.EventID=560) AND (E.Source = 'Security') AND (E.EventLog = 'Security') AND S.StringValue like '%cognosprod\pp_rr%'
AND E.Localtime > Getdate() -30

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

If you want to perform a search like this " S.StringValue like '%cognosprod\pp_rr%' " it is preferred to have fulltext index

Carlos_Felipe

ASKER

Finally, a full-text index would help or not?

Guy Hengel [angelIII / a3]

I think your data in the field is folder names. I am not confident (but this would need to be tested) if full-text indexing can do that, actually, as that is rather used to search for words in sentences.

Aneesh

Not sure whether '/' is a noise word, in that case you have to remove that from the noiseword list

arnold

Take the query and run it through the tuning adviser and see what recommendation it provides.
An alternative could be to place the first part in a separate table with a full index. Get a query that just deals with the table where column like '%cognosprod' and use the resulting id columns in the query above.
The difference is that your like query will be run on a table with a fixed length versus the current one possibly runs on an ever growing number of rows.
Do you really have multiple entries