3

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.

3
  • 3
    The LIKE '%word%' you have are a killer. Check this: Trigram Wildcard String Search in SQL Server Jan 20 '18 at 10:47
  • The AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth) can also be improved, and more easily. I guess you mean AND (@LowerDate <= DateOfBirth OR @LowerDate IS NULL) AND (DateOfBirth <= @UpperDate OR @UpperDate IS NULL). Rewriting like that or using dynamic SQL to produce simpler SQL, depending on whether the parameters are null or not, would help, too. Jan 20 '18 at 10:48
  • It seems kind of odd to search across first name, last name, and visit reason at the same time. Perhaps separating the searches out to be more specific would also help. Jan 20 '18 at 13:14
4

There appears to be a problem with the server that will prevent you from getting good performance with that query, even when @SearchWordCount = 0. Consider the details for node id 15:

slow seek

This plan executes in row mode and this operator is at the end of the branch, so you can attribute all 20 ms of CPU time and 3962 ms of elapsed time to the index seek on [Visit].[IDX_Visit_ClientID]. When executing this query you spent almost four seconds waiting for something other than CPU work. Looking at the wait stats for the select operator provides a valuable clue:

enter image description here

Almost four seconds waiting on PAGEIOLATCH_SH. We can get more information about the IO that was actually done by looking at the RunTimeInformation in the XML:

<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="8155" ActualRowsRead="8155"
    Batches="0" ActualEndOfScans="211" ActualExecutions="211"
    ActualExecutionMode="Row" ActualElapsedms="3962" ActualCPUms="20"
    ActualScans="211" ActualLogicalReads="726" ActualPhysicalReads="42"
    ActualReadAheads="8" ActualLobLogicalReads="0"
    ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>

SQL Server only needed to do 50 physical reads for the index seek, yet it waited almost four seconds to do them. You're reading data at a rate of 100 KB per second. There could be a problem with the server configuration or perhaps the server is just overloaded. Paul Randal published detailed instructions here for determining the root cause of the problem and how to fix it. Good luck.

On the subject of the pattern matching, SQL Server took about a third of a second to do that with just a single row in the temp table. That part of the query could become a bottleneck as the number of rows increases in the temp table. But you won't get good performance by changing that part of the query without resolving the issue with PAGEIOLATCH_SH waits first.

5
  • Thanks very much Joe, really appreciate that detailed reply. I'm hosting the database on Azure SQL Database with a Standard (S1) package with 20 DTUs currently. Looks like upping the package is needed here, if no obvious improvements with the query itself?
    – Philip
    Jan 22 '18 at 7:24
  • @Philip It depends on how many rows you expect in #SearchWords.
    – Joe Obbish
    Jan 22 '18 at 15:21
  • 99% of the time it would just be 1 row but needs to allow for multiple.
    – Philip
    Jan 22 '18 at 20:26
  • @Philip What's the maximum number of words that you expect?
    – Joe Obbish
    Jan 23 '18 at 16:51
  • 3 words max it would be
    – Philip
    Jan 23 '18 at 20:35
1

Pretty minor

ALTER PROCEDURE [dbo].[spSearchClient]
    @SearchWords NVARCHAR(MAX) = NULL,
    @LowerDate DATE = NULL,
    @UpperDate DATE = NULL,
    @UserCreated nvarchar(450)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #SearchWords
    (
        Word NVARCHAR(50)
    )

    INSERT INTO #SearchWords
    ( Word )
    SELECT 
        DISTINCT (trim(value)) 
    FROM 
        STRING_SPLIT(@SearchWords, ' ')  
    WHERE 
        TRIM(value) <> ''

    DECLARE @SearchWordCount int = @@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)

    DROP TABLE #SearchWords
END

To consider
Remove words that contain a smaller word
There is no purpose to search on 'indent' if you have searched on 'in'

delete wSuper 
  from #SearchWords wSuper
  join #SearchWords wSub
    on len(wSub.word) < len(wSuper.word) 
   and wSuper.word like CONCAT('%', wSub.word, '%')
0
0

I'll cover very simple trial and error work, starting with:

Leading wildcard searches suck badly. If you really, really need this, then to a point you're going to need to accept that simple fact and see what else you can do, including changing the business requirement.

At minimum see if you can get the requirement changed to require the first letter be correct!

  • Why is there no unique index on #SearchWords.Word?
  • Why is #SearchWords.Word NVARCHAR - are character that do not fit in VARCHAR valid? If so, good choice! If not, if only VARCHAR permitted characters are valid, change - VARCHAR is half the size.
  • Why is there an ID IDENTITY on it - it doesn't seem to be actually used
  • Try
    • Assuming there aren't too many #SearchWords each time, take your #SearchWords table just after creation, and determine all possible first characters, and all possible second characters (and so on as far as you get measurable benefits in Profiler/Extended Events)
      • Use those lists to craft a LIKE clause that finds at only those names that MIGHT qualify, i.e. if Searchwords contains Tom, Dick, and Harry

c.FirstName LIKE '%[TDH][oia]%' ESCAPE '|'

  • pull data from Client into a #temp table
    • Index the fields you're using!!! The new LIKE clause can use indexes!
    • make sure collation matches for what you're joining on/searching/filtering
    • apply the date range filter right here
    • with no other joins
    • apply that LIKE clause you crafted above
    • Now you have less rows to work with!
  • Now do the full scale SearchWord filtering
  • Only look up latest visit and gender on the rows that passed the previous filtering
2
  • Why the downvote? Jan 21 '18 at 22:48
  • Wasn't me (the person asking the question). I've just up-voted your answer as I would have anyway to take it back to 0.
    – Philip
    Jan 22 '18 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.