SQL Server: How to Optimize "Like" Queries

SQL Server: how to optimize like queries?

To do much for a LIKE where the pattern has the form '%XXX%', you want to look up SQL Server's full-text indexing capability, and use CONTAINS instead of LIKE. As-is, you're doing a full table scan, because a normal index won't help with a search for an item that starts with a wild card -- but a full-text index will.

/* ... */
 WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') 
   AND (contains([t0].[FIRSTNAME], 'John')) 
   AND (contains([t0].[LASTNAME], 'Smith')) 
   AND (contains([t0].[SSN], '123'))
   AND (contains([t0].[CLIENTNUMBER],'123')) 
   AND (contains([t0].[MDOCNUMBER], '123')) 
   AND ([t0].[CLIENTINDICATOR] = 'ON')

Does SQL Server optimize LIKE ('%%') query?

The short answer is - no
The long answer is - absolutely not

Does it optimize LIKE('%%') query since it means there is nothing to compare?

The statement is untrue, because there is something to compare. The following are equivalent

WHERE column LIKE '%%'
WHERE column IS NOT NULL

IS NOT NULL requires a table scan, unless there are very few non-null values in the column and it is well indexed.

EDIT

Resource on Dynamic Search procedures in SQL Server:

You simply must read this article by Erland Sommarskog, SQL Server MVP http://www.sommarskog.se/dyn-search.html (pick your version, or read both)

Otherwise if you need good performance on CONTAINS style searches, consider using SQL Server Fulltext engine.

How can I optimize/refactor a TSQL LIKE clause?

Use Full Text Search and CONTAINS. LIKE cannot be optimized when searching in the middle of the field, ie. when the LIKE expression starts with an '%', so it will always do a full table scan.

SQL Server Index - Any improvement for LIKE queries?

Only if you add full-text searching to those columns, and use the full-text query capabilities of SQL Server.

Otherwise, no, an index will not help.

Query optimization with LIKE statement

If you are treating the number as a character string, then store it that way. It sounds like the number is a code of some sort. Although consisting of numbers, it is really just the name of something (such as an account number).

If the number is fixed length, say 5, then you can do:

where a >= 32000 and a < 33000;

You could extend this idea for different lengths:

where a >= 32 and a < 33 or
      a >= 320 and a < 330 or
      a >= 3200 and a < 3300 or
      a >= 30000 and a < 33000 

How to optimize a query having multiple like conditions

I assume that search values comes dynamically and no way to predict of what going to be selected. Then, you cannot do a lot with constructions like:

WHERE df.filename like '%OA73030%'  or df.filename like '%OA73035%'
or df.FileName like '%OA77030%' or df.filename like '%OA77035%'

Since there is no way to rewrite it to:

WHERE df.filename like IN ('%OA73030%','%OA73035%','%OA77030%','%OA77035%')

However, you entire logic of your query can be very much optimized this way:

SELECT COUNT(*) AS totalProcessed
,       COUNT(CASE WHEN    di.CompletionDetail LIKE '%Not found%' OR di.CompletionDetail LIKE '%error%'  THEN 1 END) AS totalErrored
,       COUNT(CASE WHEN    di.CompletionDetail NOT LIKE '%error%' AND di.CompletionDetail NOT LIKE '%Not Found%'  THEN 1 END) AS totalErrored
,       COUNT(CASE WHEN    di.CompletionDetail NOT LIKE '%error%' AND di.CompletionDetail NOT LIKE '%Not Found%'  THEN 1 END) AS totalSuccess
FROM   DetailItem di
       INNER JOIN DownloadFile df ON di.DownloadFileID = df.DownLoadID
WHERE  di.CompletionTime between '2018-01-31' AND '2018-01-31 23:59:59'
       AND di.DetailItemName <> '99999999'
       AND df.CompanyID = 164
       AND (   df.filename LIKE '%OA73030%'
               OR df.filename LIKE '%OA73035%'
               OR df.FileName LIKE '%OA77030%'
               OR df.filename LIKE '%OA77035%' );

In this case the data engine have to search data only once for all aggregates instead of four times.

Next to that, your approach of trimming time is not SARGable:

( CONVERT(DATE, di.CompletionTime)) = '2018-01-31'

and it has to be rewritten to:

di.CompletionTime between '2018-01-31' AND '2018-01-31 23:59:59'

Otherwise possible index on CompletionTime will not be used

---

Related Topics