Início / dba / Perguntas / 195844
Accepted
Philip
Asked: 2018-01-21 02:03:07 +0800 CST

Resolvendo o problema de consulta de longa duração, número de execuções

  • 772

Tenho a seguinte consulta:

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

O plano de execução é https://www.brentozar.com/pastetheplan/?id=BkGzfUeHz

A consulta funciona como deveria, mas leva de 3 a 7 segundos para ser executada e parece ser devido ao seguinte, que tem uma execução para cada linha na tabela 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
    )

Quer saber se alguém conhece uma maneira melhor e mais eficaz de fazer isso que consumiria menos tempo?

Se alguns dados de exemplo forem úteis, por favor me avise.

sql-server t-sql

3 respostas

  1. Best Answer

    Parece haver um problema com o servidor que impedirá que você obtenha um bom desempenho com essa consulta, mesmo quando @SearchWordCount = 0. Considere os detalhes do ID do nó 15:

    busca lenta

    Este plano é executado em modo de linha e este operador está no final da ramificação, então você pode atribuir todos os 20 ms de tempo de CPU e 3962 ms de tempo decorrido à busca de índice em [Visit].[IDX_Visit_ClientID]. Ao executar esta consulta, você gastou quase quatro segundos esperando por algo diferente do trabalho da CPU. Observar as estatísticas de espera para o operador de seleção fornece uma pista valiosa:

    insira a descrição da imagem aqui

    Quase quatro segundos esperando em PAGEIOLATCH_SH . Podemos obter mais informações sobre o IO que foi realmente feito observando o RunTimeInformation no 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>

    O SQL Server precisou fazer apenas 50 leituras físicas para a busca de índice, mas esperou quase quatro segundos para fazê-las. Você está lendo dados a uma taxa de 100 KB por segundo. Pode haver um problema com a configuração do servidor ou talvez o servidor esteja sobrecarregado. Paul Randal publicou instruções detalhadas aqui para determinar a causa raiz do problema e como corrigi-lo. Boa sorte.

    Sobre a correspondência de padrões, o SQL Server levou cerca de um terço de segundo para fazer isso com apenas uma única linha na tabela temporária. Essa parte da consulta pode se tornar um gargalo à medida que o número de linhas aumenta na tabela temporária. Mas você não obterá um bom desempenho alterando essa parte da consulta sem resolver o problema com PAGEIOLATCH_SHesperas primeiro.

    • 4

  2. Bem menor 

    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

    A considerar
    Remover palavras que contenham uma palavra menor
    Não há nenhum propósito em pesquisar por 'recuo' se você pesquisou por 'em'

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

  3. Abordarei um trabalho de tentativa e erro muito simples, começando com:

    As principais pesquisas de curingas são péssimas. Se você realmente precisa disso, então, até certo ponto, precisará aceitar esse simples fato e ver o que mais pode fazer, inclusive alterar o requisito de negócios.

    No mínimo, veja se você pode alterar o requisito para exigir que a primeira letra esteja correta!

    • Por que não há índice exclusivo em #SearchWords.Word?
    • Por que #SearchWords.Word NVARCHAR - são caracteres que não cabem em VARCHAR válidos? Se sim, boa escolha! Caso contrário, se apenas os caracteres permitidos VARCHAR forem válidos, altere - VARCHAR é metade do tamanho.
    • Por que há uma ID IDENTITY nele - não parece ser realmente usado
    • Tentar
      • Supondo que não haja muitos #SearchWords a cada vez, pegue sua tabela #SearchWords logo após a criação e determine todos os primeiros caracteres possíveis e todos os segundos caracteres possíveis (e assim por diante até obter benefícios mensuráveis ​​no Profiler/Extended Events)
        • Use essas listas para criar uma cláusula LIKE que encontre apenas os nomes que PODEM se qualificar, ou seja, se as palavras-chave contiverem Tom, Dick e Harry

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

    • extrair dados do cliente para uma tabela #temp
      • Indexe os campos que está usando!!! A nova cláusula LIKE pode usar índices!
      • verifique se o agrupamento corresponde ao que você está participando/pesquisando/filtrando
      • aplique o filtro de intervalo de datas aqui
      • sem outras junções
      • aplique a cláusula LIKE que você criou acima
      • Agora você tem menos linhas para trabalhar!
    • Agora faça a filtragem do SearchWord em escala completa
    • Procure apenas a última visita e sexo nas linhas que passaram na filtragem anterior
    • 0

relate perguntas