SQL Server LIKE clause and indexes

Carlos_Felipe
Carlos_Felipe used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
the problem is the leading % for the LIKE. the leading % is what makes a regular index cannot be used.
with what the data seems like, full-text index won't help.

however a index on EventID + Source + EventLog + Localtime is what could help.
AneeshDatabase Consultant
Top Expert 2009

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

Author

Commented:
Finally, a full-text index would help or not?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.
AneeshDatabase Consultant
Top Expert 2009

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

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial