主页 / dba / 问题 / 195844
Accepted
Philip
Asked: 2018-01-21 02:03:07 +0800 CST

解决长时间运行的查询，执行次数问题

  • 772

我有以下查询：

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

执行计划是https://www.brentozar.com/pastetheplan/?id=BkGzfUeHz

该查询按应有的方式工作，但需要 3-7 秒才能运行，这似乎都是由于以下原因，它对 Client 表中的每一行都有一个执行：

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
    )

想知道是否有人知道一种更好更有效的方法，而且耗时更少？

如果一些样本数据有用，请告诉我。

sql-server t-sql

3 个回答

  1. Best Answer

    服务器似乎存在问题，即使在@SearchWordCount = 0. 考虑节点 ID 15 的详细信息：

    慢速寻找

    该计划以行模式执行，并且该运算符位于分支的末尾，因此您可以将所有 20 毫秒的 CPU 时间和 3962 毫秒的耗用时间归因于索引查找[Visit].[IDX_Visit_ClientID]。执行此查询时，您花了将近四秒钟的时间等待 CPU 工作以外的事情。查看 select 运算符的等待统计信息提供了一个有价值的线索：

    在此处输入图像描述

    在PAGEIOLATCH_SH上等待将近四秒钟。通过查看 XML 中的 RunTimeInformation，我们可以获得有关实际完成的 IO 的更多信息：

    <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 只需要为索引查找执行 50 次物理读取，但它等了将近四秒钟才完成。您正在以每秒 100 KB 的速率读取数据。服务器配置可能有问题，或者服务器可能只是超载了。Paul Randal在此处发布了详细说明，用于确定问题的根本原因以及如何解决问题。祝你好运。

    关于模式匹配的主题，SQL Server 仅用临时表中的一行就完成了大约三分之一秒。随着临时表中行数的增加，这部分查询可能成为瓶颈。但是，如果不PAGEIOLATCH_SH先解决等待问题，就通过更改查询的那部分来获得良好的性能。

    • 4

  2. 相当轻微 

    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

    考虑
    删除包含较小单词的单词
    如果您在“in”上搜索过，则没有必要在“缩进”上搜索

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

  3. 我将介绍非常简单的试错工作，首先是：

    领先的通配符搜索很糟糕。如果你真的、真的需要这个，那么在某种程度上你将需要接受这个简单的事实并看看你还能做些什么，包括改变业务需求。

    至少看看您是否可以将要求更改为要求第一个字母正确！

    • 为什么#SearchWords.Word 上没有唯一索引？
    • 为什么#SearchWords.Word NVARCHAR - 不适合 VARCHAR 的字符是否有效？如果是这样，不错的选择！如果不是，如果只有 VARCHAR 允许的字符有效，则更改 - VARCHAR 是大小的一半。
    • 为什么上面有一个ID IDENTITY——好像没有实际使用过
    • 尝试
      • 假设每次都没有太多#SearchWords，在创建后立即获取#SearchWords 表，并确定所有可能的第一个字符和所有可能的第二个字符（等等，只要您在 Profiler/Extended Events 中获得可衡量的好处）
        • 使用这些列表来制作一个 LIKE 子句，该子句仅查找那些可能符合条件的名称，即如果 Searchwords 包含 Tom、Dick 和 Harry

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

    • 将数据从客户端拉入#temp 表
      • 索引您正在使用的字段！！！新的 LIKE 子句可以使用索引！
      • 确保排序规则与您加入/搜索/过滤的内容相匹配
      • 在此处应用日期范围过滤器
      • 没有其他连接
      • 应用您在上面制作的 LIKE 子句
      • 现在您可以处理的行更少了！
    • 现在进行全面的 SearchWord 过滤
    • 仅在通过先前过滤的行上查找最新访问和性别
    • 0

相关问题