1

This is doing my headin, but I'm stuck on trying to get an Index Seek on the MAX aggregate within this query.

Here is the execution plan - and I'm wondering what I'm missing here, as currently as the query takes 3/4 seconds to complete which is too slow. I've indexed on StartDate, ClientID with no luck.

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
    C.ClientID, 
    C.FirstName,
    C.LastName,
    C.FullName, 
    C.DateOfBirth,
    G.GenderName, 
    G.GenderIcon, 
    C.VerificationCode,
    MAX(V.StartDate) as LastVisitDate
FROM 
    Client C
LEFT JOIN Visit V on 
    C.ClientID = V.ClientID
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
        (SELECT COUNT(*) FROM #SearchWords)=0 -- if we haven't any words
    )
    AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth)
GROUP BY
    C.ClientID, 
    C.FirstName,
    C.LastName,
    C.FullName, 
    C.DateOfBirth,
    G.GenderName, 
    G.GenderIcon, 
    C.VerificationCode 
3
  • Think I'd need some example data to dig into this... that said I can recommend a couple of improvements. Firstly; do you need all matches to the Visit table or just after the most recent (hence the MAX)? I'd also change your OR (SELECT COUNT(*) FROM #SearchWords)=0 to NOT EXISTS(SELECT 1 FROM #SearchWords). Or better yet, grab the @@ROWCOUNT value from your insert into #SearchWords and test that. Dec 8 '17 at 9:01
  • Thanks @KentChenery, how can I share sample data through stackexchange?
    – Philip
    Dec 8 '17 at 9:27
  • Couple of (popular ways) are: 1) Add some tables in your description with values...essentially formatted text 2) Create scripts in your question that populate exampes or 3) use dbfiddle.uk Dec 8 '17 at 9:29
1

You want your index to have ClientID as the leading key. E.g.:

CREATE INDEX IX_Visit_ClientID_StartDate ON Visit(ClientID, StartDate)

This is because you need to join on ClientID before finding the most recent StartDate. Therefore ClientID needs to appear first.

Along with that index change... Since you only want to find the most recent Visit.StartDate you can change your LEFT JOIN Visit to an OUTER APPLY that gets the most recent date.

Try this:

CREATE TABLE #SearchWords
(
    ID int IDENTITY(1,1),
    Word NVARCHAR(50)
)

DECLARE @SearchWordCount INTEGER;

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
    )
    AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth)
GROUP BY
    C.ClientID, 
    C.FirstName,
    C.LastName,
    C.FullName, 
    C.DateOfBirth,
    G.GenderName, 
    G.GenderIcon, 
    C.VerificationCode 

Note:

I've also made the @@ROWCOUNT improvement I suggested in my comment.

The GROUP BY is almost certainly unnecessary now too. Remove it if it is.

Off Topic:

One final last comment which is technically unrelated to your question. You seem to be doing something that Full Text Searching may be a better solution for.

5
  • Thanks very much Kent. That's a big improvement and getting a much better plan now - brentozar.com/pastetheplan/?id=HyPwaAvbM
    – Philip
    Dec 8 '17 at 9:38
  • If this solves your issue, please mark as the answer. Dec 8 '17 at 9:39
  • Also I think you missed the SELECT @SearchWordCount = @@ROWCOUNT; bit in your query in the "pastetheplan" link/example. Dec 8 '17 at 9:40
  • I think that might be a bug with Brent's script because it's in there. But thanks.
    – Philip
    Dec 8 '17 at 9:44
  • 1
    @James If you think you've found a bug, can you shoot an email to the help account about it? We'd like to get it fixed. Thanks! Dec 8 '17 at 11:22

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.