CODEWITHSUNDEEP
sqlsql-serverquery-optimizationssms
The Recruit
The Recruit

Reputation: 863

Optimize SQL Query containing Like Operator

Trying to make the following query neat and run faster. Any insights are helpful. I have to use like Operator since need to search for the pattern anywhere in the field. 

Select Col1,Col2,Col3 from TableName where
   (Subject like '%Maths%' OR
    Subject like '%Physics%' OR
    Subject like '%Chemistry%' OR
    Subject like '%English%')
    AND
    (Description like '%Maths%' OR
    Description like '%Physics%' OR
    Description like '%Chemistry%' OR
    DESCRIPTION like '%English%')
    AND
    (Extra like '%Maths%' OR
    Extra like '%Physics%' OR
    Extra like '%Chemistry%' OR
    Extra like '%English%') AND Created Date > 2017-01-01

Upvotes: 0

Views: 77

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Basically, you can't optimize this query using basic SQL. If the strings you are searching for are words in the texts, then you can use a full text string. The place to start in learning about this is the documentation.

If you happen to know that you will be searching for these four strings, you can set up computed columns and then build indexes on the computed columns. That would be fast. But you would be limited to exactly those strings.

All is not lost. Technically, there are other solutions, such as those based on n-grams or by converting to XML/JSON and indexing that. However, these are either not supported in SQL Server or non-trivial to implement.

Upvotes: 1

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

You can try this using CTE and charindex

compare execution plan from your OP.

;with mycte as (   --- building words to look for
 select * from
  (values ('English'),
    ('Math'),
    ('Physics'),
    ('English'),
    ('Chemistry')
  ) t (words)
)
select t.* from
tablename t 
inner join mycte c
 on charindex(c.words,t.subject) > 0    --- check if there is a match
 and charindex(c.words,t.description) > 0  --- check if there is a match
 and charindex(c.words,t.extra) > 0   --- check if there is a match
where
t.createddate > '2017-01-01'

Upvotes: 0

Related Questions