SQL Server: how to optimize "like" queries?
- by duke84
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.
