Início / dba / Perguntas / 298990
Accepted
Hasan Can Saral
Asked: 2021-09-03 20:42:40 +0800 CST

Como posso melhorar o desempenho da consulta com filtro na coluna datetime usando uma exibição indexada?

  • 772

Eu tenho essa tabela:

CREATE TABLE transactions
(
    id                                    NUMERIC(20, 0)              NOT NULL PRIMARY KEY,
    amount                                NUMERIC(18, 2) DEFAULT NULL NULL,
    -- Some 100 columns
    customer_msisdn                       VARCHAR(255)   DEFAULT NULL              NULL,
    customer_email                        VARCHAR(255)   DEFAULT NULL              NULL,
    payment_date                          DATETIME2                   NOT NULL
);

CREATE NONCLUSTERED INDEX msisdn_idx ON transactions (customer_msisdn, payment_date, id);
CREATE NONCLUSTERED INDEX email_idx ON transactions (customer_email, payment_date, id);

Estou indexando cerca de 1 milhão de linhas por mês. Com muita frequência, preciso selecionar as transações dos últimos 3 meses por customer_msisdnou por customer_email99% do tempo de 50 a 1.000 registros.

Aqui está minha consulta para um pouco mais de visão:

SELECT t.*
FROM transactions t
         JOIN (SELECT t.id
               FROM transactions t
                        WITH (FORCESEEK)
               WHERE t.customer_email = :customerEmail
                 AND t.payment_date >= :startDate
                 AND t.payment_date < :endDate
               UNION
               SELECT t.id
               FROM transactions t
                        WITH (FORCESEEK)
               WHERE t.customer_msisdn = :customerMsisdn
                 AND t.payment_date >= :startDate
                 AND t.payment_date < :endDate) AS filtered_transactions
              ON t.id= filtered_transactions.id
ORDER BY t.payment_date;

E eu sinto que desde que :endDateé sempre agora (e quando não, pode tolerar falhas) e :startDateé sempre três meses atrás, eu tenho algum espaço para melhorias. Aqui está o que eu pensei:

Crie uma exibição indexada com um filtro em payment_date:

CREATE VIEW [dbo].transactions_iv
    WITH SCHEMABINDING AS
SELECT [t].id,
       -- All the rows
       [t].customer_msisdn,
       [t].customer_phone,
       [t].payment_date
FROM [dbo].[transactions] [t]
WHERE [t].payment_date >= DATEADD(MONTH, -3, CURRENT_TIMESTAMP);

e meus índices:

CREATE NONCLUSTERED INDEX msisdn_iv_idx ON transactions_iv (customer_msisdn, id);
CREATE NONCLUSTERED INDEX phone_iv_idx ON transactions_iv (customer_phone, id);

e elimine as AND t.payment_date >= :startDate AND t.payment_date < :endDatecláusulas completamente da consulta. A consulta se torna:

SELECT t.*
FROM transactions_iv t
         JOIN (SELECT t.id
               FROM transactions_iv t
                        WITH (FORCESEEK)
               WHERE t.customer_email = :customerEmail
               UNION
               SELECT t.id
               FROM transactions_iv t
                        WITH (FORCESEEK)
               WHERE t.customer_msisdn = :customerMsisdn) AS filtered_transactions
              ON t.id= filtered_transactions.id
ORDER BY t.payment_date;

Como a exibição tem apenas as transações dos últimos 3 meses, estou assumindo que os índices também terão. Essa suposição está correta? O índice seria atualizado apenas para cobrir os registros dos últimos 3 meses e eu obteria meu aumento de desempenho?

Outra alternativa é:

  1. Crie outra tabela idêntica,
  2. Preenchê-lo com um gatilho na tabela principal
  3. Com um cron job, exclua registros com mais de 3 meses todas as noites.

Como essa opção se compara à anterior?

sql-server index

1 respostas

  1. Best Answer

    Sua exibição indexada não pode ser criada como escrita porque não é determinística. As linhas cairiam fora de vista com o passar do tempo.

    Sua consulta atual provavelmente está produzindo um plano de execução como:

    Plano atual

    Deixando de lado a questão de tabelas separadas e visualizações indexadas por um momento, experimente a seguinte reescrita menor (usando índices existentes):

    SELECT t.*
FROM transactions t
JOIN 
(
    SELECT t.id, t.payment_date
    FROM transactions t
    WHERE
        t.customer_email = @customerEmail
        AND t.payment_date >= @startDate
        AND t.payment_date < @endDate
    UNION
    SELECT t.id, t.payment_date
    FROM transactions t
    WHERE 
        t.customer_msisdn = @customerMsisdn
        AND t.payment_date >= @startDate
        AND t.payment_date < @endDate
) AS filtered_transactions
    ON filtered_transactions.id = t.id
ORDER BY 
    filtered_transactions.payment_date;

    Observe as colunas extras t.payment_dateno UNION, e o ORDER BYmudou para filtered_transactions.payment_date. Isso não é semanticamente diferente da sua consulta, mas ajudará o otimizador a encontrar o melhor plano.

    Você deve obter um plano de execução bastante eficiente, como:

    Forma de plano desejada

    O otimizador realmente deve selecionar essa forma de plano (ou talvez uma versão paralela), mas se não, uma ou mais dicas podem ser necessárias. Um exemplo extremo:

    SELECT t.*
FROM
(
    SELECT t.id, t.payment_date
    FROM transactions t
        WITH (FORCESEEK(email_idx(customer_email,payment_date)))
    WHERE
        t.customer_email = @customerEmail
        AND t.payment_date >= @startDate
        AND t.payment_date < @endDate
    UNION
    SELECT t.id, t.payment_date
    FROM transactions t
        WITH (FORCESEEK(msisdn_idx(customer_msisdn,payment_date)))
    WHERE 
        t.customer_msisdn = @customerMsisdn
        AND t.payment_date >= @startDate
        AND t.payment_date < @endDate
) AS filtered_transactions
JOIN dbo.transactions AS t
    ON t.id = filtered_transactions.id
ORDER BY 
    filtered_transactions.payment_date
OPTION (FORCE ORDER, LOOP JOIN, MERGE UNION);
    • 4

relate perguntas