Início / dba / Perguntas / 326661
Accepted
MichaelD
Asked: 2023-05-02 21:30:10 +0800 CST

Existe uma opção ou dica possível para melhorar o desempenho da consulta com vários valores na cláusula "in"

  • 772

Temos uma tabela CustomerNote com 4 colunas ID, CustomerID, Note, Date

Existe um índice em CustomerID asc, Date desc

Quando a seguinte consulta é executada

select top 30 
    Date 
from CustomerNote
where CustomerID in (1,5)
order by Date desc

O índice é usado, mas ainda está buscando TODAS as CustomerNotes para os customerIDs 1 e 5, para então classificar/topar, causando muito uso da CPU. insira a descrição da imagem aqui

Isso ocorre devido aos vários valores na cláusula "in". Eu sei que a cláusula "in" nunca terá mais valores do que 10, então seria uma abordagem muito melhor se o sql server iterasse sobre os 10, buscasse pelo menos 30 por customerID e a mesclagem, classificações e tops. Existe uma dica de consulta ou opção para conseguir isso?

sql-server

3 respostas

  1. Provavelmente é melhor escrever a transformação que deseja manualmente, mas com o objetivo de encontrar algo que o otimizador possa fazer por você com alterações mínimas:

    SELECT TOP (30) 
    CN.[Date] 
FROM dbo.CustomerNote AS CN
WHERE 
    CN.CustomerID IN (SELECT 1 UNION SELECT 5)
ORDER BY 
    CN.[Date] DESC;

    Plano

    Sim, seria bom se o otimizador explorasse esse tipo de opção para você sem alterar a sintaxe.

    Exemplo de tabela e dados

    CREATE TABLE dbo.CustomerNote
(
    CustomerID integer NOT NULL,
    [Date] datetime NOT NULL
);

    WITH 
    N (n) AS
    (
        SELECT 
            SV.number 
        FROM master.dbo.spt_values AS SV
        WHERE
            SV.[type] = N'P'
            AND SV.number >= 1
    )
INSERT dbo.CustomerNote
    WITH (TABLOCKX)
(
    CustomerID, 
    [Date]
)
SELECT 
    C.n, 
    DATEADD
    (
        MINUTE, 
        -D.n * RAND(CHECKSUM(NEWID())) * 1000,
        GETDATE()
    )
FROM N AS C
CROSS JOIN N AS D
WHERE
    C.n BETWEEN 1 AND 9;

CREATE NONCLUSTERED INDEX 
    [IX dbo.CustomerNote CustomerID, Date-] 
ON dbo.CustomerNote
    (CustomerID, [Date] DESC);

    Isso carrega 2.047 valores de data aleatórios para dez clientes diferentes:

    SELECT CN.CustomerID, NumRows = COUNT_BIG(*) 
FROM dbo.CustomerNote AS CN
GROUP BY CN.CustomerID
ORDER BY CN.CustomerID;
    Identificação do Cliente NumRows
    1 2047
    2 2047
    3 2047
    4 2047
    5 2047
    6 2047
    7 2047
    8 2047
    9 2047

    A execução do código da solução produz um plano de pós-execução em que 14 linhas são lidas de uma busca de índice ordenada para o cliente 1 e 17 linhas de uma busca semelhante para o cliente 5:

    plano real

    db<> demonstração de violino

    O plano não lê todas as 2.047 linhas para cada cliente.

    Observe também que esta solução não requer um operador de classificação.

    A solução é bastante geral, embora existam algumas condições de classificação a serem atendidas para a Merge Concatenation .

    Se você deseja projetar uma coluna diferente, essa coluna precisaria fazer parte da chave de índice (não como uma inclusão) para atender ao requisito de classificação; ou você pode buscar apenas uma chave para a tabela e procurar a(s) coluna(s) extra(s) como uma etapa separada depois de encontrar o pequeno número necessário de chaves.

    Demonstrações adicionais com uma coluna extra:

    • 9
  2. Best Answer

    Parece que pode ser melhor se você juntar um parâmetro com valor de tabela, tabela temporária ou VALUEScláusula. Isso significa que cada um CustomerIDserá consultado individualmente de forma correlacionada APPLYe depois classificado.

    select top (30) 
  cn.Date 
from (values
    (1),
    (5)
) v(CustomerID)
cross apply (
    select top (30) cn.*
    from CustomerNote cn
    where v.CustomerID = cn.CustomerID
    order by
      cn.Date desc
) cn
order by
  cn.Date desc;
    • 7

  3. Os tope order bysão sempre aplicados por último. Você não pode esperar que o otimizador faça uma pré-filtragem de linhas com base na order bycláusula.

    Mas você pode reduzir o número de registros para classificação, usando algo como

    select top 30 
    Date 
from CustomerNote
where CustomerID in (1,5) and Date>datadd(day, -60, getdate())
order by Date desc

    Portanto, supondo que você tenha mais de 30 registros nos últimos sessenta dias, você obterá esses registros, os classificará e descartará os extras. O número real de dias, você teria que adivinhar, com base em seus dados.

    • -4

relate perguntas