How can query be optimized?

Question

I have a simple select query on a table, but with different values in LIKE operator. Query is as follows:

SELECT *
FROM BatchServices.dbo.TaskLog
WHERE EntryTime BETWEEN '20190407' AND '20190408' AND
      TaskGroup LIKE '%CSR%' AND
      (LogText LIKE '%error%' OR LogText LIKE '%fail%')

This above query is fine and returning me the expected results but I don't want to have multiple LIKE in a query, so I have already tried something like

SELECT *
from BatchServices.dbo.TaskLog
WHERE taskgroup = 'csr' AND
      LogText IN ( '%error%','%fail%') AND 
      EntryTime>'2019-04-07'
ORDER BY EntryTime ASC

This query is not giving me any results. I am expecting a query which looks smarter than the one I have which returns result. Any help?

possible duplicate of: https://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql — Astrogat, Apr 09 '19 at 10:53
You need to use multiple `LIKE` in SQL Server. Or a full-text index. — Gordon Linoff, Apr 09 '19 at 10:54
SQL Server supports full text searching which can search in multiple columns at once — Raymond Nijland, Apr 09 '19 at 10:57
"I don't want to have multiple ..." is not a reason for changing a working query. — SMor, Apr 09 '19 at 11:40
@SMor I know that's not the reason but I just want to know is there any way to reduce the lines of code. — shary.sharath, Jun 10 '19 at 09:10

score 1 · Accepted Answer · answered Apr 09 '19 at 10:53

1

use like operator with OR condition

SELECT * from BatchServices.dbo.TaskLog WHERE taskgroup ='csr' AND 
(LogText like '%error%' or LogText like '%fail%')
AND EntryTime>'2019-04-07' 
ORDER BY EntryTime ASC

answered Apr 09 '19 at 10:53

Fahmi

37,315
5
22
31

score 0 · Answer 2 · answered Apr 09 '19 at 14:20

The LIKE operators are not the problem. It's the leading wild cards. Unless you cant get rid of those, your optimization options are going to be limited to making sure you have a covering index on EntryTime... That and replacing the "*" with the specific columns you need.

How can query be optimized?

2 Answers2