SQL Server: how to optimize "like" queries?

Posted by duke84 on Stack Overflow See other posts from Stack Overflow or by duke84
Published on 2010-03-28T06:09:51Z Indexed on 2010/03/28 6:23 UTC
Read the original article Hit count: 417

Filed under:
|
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server