Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: how to optimize "like" queries?

I have a query that searches for clients using "like" with wildcard. For example:

SELECT TOP (10) 
       [t0].[CLIENTNUMBER], 
       [t0].[FIRSTNAME], 
       [t0].[LASTNAME], 
       [t0].[MI], 
       [t0].[MDOCNUMBER]
  FROM [dbo].[CLIENT] AS [t0]
 WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') 
   AND ([t0].[FIRSTNAME] LIKE '%John%') 
   AND ([t0].[LASTNAME] LIKE '%Smith%') 
   AND ([t0].[SSN] LIKE '%123%') 
   AND ([t0].[CLIENTNUMBER] LIKE '%123%') 
   AND ([t0].[MDOCNUMBER] LIKE '%123%') 
   AND ([t0].[CLIENTINDICATOR] = 'ON')

It can also use less parameters in "where" clause, for example:

SELECT TOP (10) 
       [t0].[CLIENTNUMBER], 
       [t0].[FIRSTNAME], 
       [t0].[LASTNAME], 
       [t0].[MI], 
       [t0].[MDOCNUMBER]
  FROM [dbo].[CLIENT] AS [t0]
 WHERE (LTRIM(RTRIM([t0].[DOCREVNO])) = '0') 
   AND ([t0].[FIRSTNAME] LIKE '%John%') 
   AND ([t0].[CLIENTINDICATOR] = 'ON')

Can anybody tell what is the best way to optimize performance of such query? Maybe I need to create an index? This table can have up to 1000K records in production.

like image 993
andr111 Avatar asked Mar 28 '10 06:03

andr111


People also ask

How do you optimize like queries?

There is no way to optimise the query without making changes to the structure, a LIKE filter like that is always going to result in an index or table scan. You might get a small improvement with a index over just Order_Description though it would only be a very small difference.

How can I improve SQL query performance in SQL Server?

Avoid Multiple Joins Try to avoid writing a SQL query using multiple joins that includes outer joins, cross apply, outer apply . It reduce the speed of execution and reduces the choices for Optimizer to decide the join order and join type. We can use temp table or temp variables instead of Multiple Joins.

Why is SQL like slow?

The SQL LIKE operator very often causes unexpected performance behavior because some search terms prevent efficient index usage. That means that there are search terms that can be indexed very well, but others can not. It is the position of the wild card characters that makes all the difference.


1 Answers

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')
like image 144
Jerry Coffin Avatar answered Oct 02 '22 13:10

Jerry Coffin