I have the following query:
ALTER PROCEDURE [dbo].[spSearchClient] @SearchWords NVARCHAR(MAX) = NULL, @LowerDate DATE = NULL, @UpperDate DATE = NULL, @UserCreated nvarchar(450) AS BEGIN SET NOCOUNT ON; DECLARE @UserAccountID smallint DECLARE @SearchWordCount int SELECT @UserAccountID = dbo.fnGetUserAccountID(@UserCreated) CREATE TABLE #SearchWords ( ID int IDENTITY(1,1), Word NVARCHAR(50) ) INSERT INTO #SearchWords ( Word ) SELECT value FROM STRING_SPLIT(@SearchWords, ' ') WHERE TRIM(value) <> '' SELECT @SearchWordCount = @@ROWCOUNT; SELECT C.ClientID, C.FirstName, C.LastName, C.FullName, C.DateOfBirth, G.GenderName, G.GenderIcon, C.VerificationCode, V.LastVisitDate FROM Client C OUTER APPLY ( SELECT MAX(StartDate) AS LastVisitDate FROM Visit AS V WHERE C.ClientID = V.ClientID ) AS V INNER JOIN LookUp.Gender G on C.GenderID = G.GenderID WHERE ( EXISTS( -- if we have words SELECT * FROM #SearchWords s WHERE (c.FirstName LIKE CONCAT('%',s.Word,'%')) OR (c.LastName LIKE CONCAT('%',s.Word,'%')) OR (c.VerificationCode LIKE CONCAT('%',s.Word,'%')) ) OR @SearchWordCount = 0 --if we don't have words ) AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth) INSERT INTO UserSearchLog ( SearchWords, LowerDate, UpperDate, SearchResultsCount, UserCreated ) VALUES ( @SearchWords, @LowerDate, @UpperDate, @@ROWCOUNT, @UserAccountID ) DROP TABLE #SearchWords END
The execution plan is https://www.brentozar.com/pastetheplan/?id=BkGzfUeHz
The query works how it should but takes a good 3-7 seconds to run and it seems to all be due to the following which has an execution for every row in the Client table:
WHERE ( EXISTS( -- if we have words SELECT * FROM #SearchWords s WHERE (c.FirstName LIKE CONCAT('%',s.Word,'%')) OR (c.LastName LIKE CONCAT('%',s.Word,'%')) OR (c.VerificationCode LIKE CONCAT('%',s.Word,'%')) ) OR @SearchWordCount = 0 --if we don't have words )
Wondering if anyone knew of a better more effective way of doing this that would be less time consuming?
If some sample data would be useful, please let me know.