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.
