主页 / dba / 问题 / 192686
Accepted
Philip
Asked: 2017-12-09 00:43:31 +0800 CST

在聚合上获取 SEEK 而不是 SCAN

  • 772

这是我的头，但我坚持尝试在此查询中的 MAX 聚合上获取索引查找。

这是执行计划——我想知道我在这里遗漏了什么，因为目前查询需要 3/4 秒才能完成，这太慢了。我已经在 StartDate, ClientID 上建立了索引，但没有成功。

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
sql-server azure-sql-database

1 个回答

  1. Best Answer

    您希望索引将 ClientID 作为前导键。例如：

    CREATE INDEX IX_Visit_ClientID_StartDate ON Visit(ClientID, StartDate)

    这是因为您需要在找到最近的 StartDate 之前加入 ClientID。因此 ClientID 需要首先出现。

    随着该索引的更改...由于您只想找到最新 Visit.StartDate的，您可以将您更改LEFT JOIN Visit为OUTER APPLY获取最新日期的日期。

    尝试这个：

    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

    笔记：

    我也做了@@ROWCOUNT我在评论中建议的改进。

    现在GROUP BY几乎肯定也没有必要了。如果是，请将其删除。

    无关：

    最后一条评论在技术上与您的问题无关。您似乎正在做一些全文搜索可能是更好的解决方案。

    • 1

相关问题