Início / dba / Perguntas / 77807
Accepted
John
Asked: 2014-09-27 13:15:32 +0800 CST

Agregação de execução lenta da consulta agregada

  • 772

Estou procurando o saldo total de todas as contas que tiveram um saldo negativo em um ponto específico no tempo. Eu já tenho o código abaixo e funciona, mas funciona horrivelmente.

A tabela de transações contém 20 milhões de linhas. A consulta abaixo leva cerca de 30 segundos, o que não é ruim, mas há uma segunda parte em que preciso repetir @report_date no primeiro dia de cada mês desde o início do banco de dados, o que aumenta o tempo de execução para aproximadamente 30 minutos.

Isso está no MSSQL2008 e não estou recebendo nenhum aviso de índices ausentes no plano de execução, mas suspeito que meu problema ainda esteja com os índices, então estou intencionalmente deixando-os fora daqui.

CREATE TABLE transactions(
    transaction_id int,
    account_id int,
    department_id int,
    location_id int,
    post_date date,
    amount money
    );

SELECT
    t2.department_id,
    t2.location_id,
    SUM(t2.credit_balances)
FROM
    (
    SELECT
        t1.department_id,
        t1.location_id,
        t1.account_id,
        SUM(t1.amount) as credit_balances
    FROM
        transactions t1
    WHERE
        t1.post_date < @report_date
    GROUP BY
        t1.department_id,
        t1.location_id,
        t1.account_id
    HAVING
        SUM(t1.amount) < 0
    ) t2
GROUP BY
    t2.department_id,
    t2.location_id;

A tabela contém 38 department_id,location_idcombinações distintas e 4,5 milhões de arquivos department_id,location_id,account_id.

sql-server performance

1 respostas

  1. Best Answer

    Como você repete essa consulta por vários meses, reagrega continuamente as mesmas linhas.

    Por exemplo, as linhas no primeiro mês sempre serão trazidas de volta pelos t1.post_date < @report_datecritérios, portanto, serão reprocessadas a cada mês.

    Para evitar isso, provavelmente consideraria trabalhar com isso de maneira iterativa, um mês de cada vez, desde o início. Dependendo da volatilidade dos dados históricos, também posso considerar armazenar os resultados pré-calculados no banco de dados, em vez de recalculá-los a cada mês.

    Para calcular isso em tempo de execução, você pode criar uma tabela temporária com a seguinte estrutura.

    CREATE TABLE #balance
  (
     department_id   INT NOT NULL,
     location_id     INT NOT NULL,
     account_id      INT NOT NULL,
     balance_to_date MONEY NOT NULL,
     PRIMARY KEY (department_id, location_id, account_id)
  );

    Você também pode considerar adicionar o seguinte índice em sua transactionstabela

    ALTER TABLE transactions
  ADD post_date_year_month AS (10000 * YEAR(post_date) + MONTH(post_date))

CREATE INDEX ix
  ON transactions(post_date_year_month, department_id, location_id, account_id)
  INCLUDE (amount)

    Em seguida, extraia um mês de cada vez transactionse mescle-o #balance(com um quando correspondido e depois incrementado, quando não correspondido inserir).

    A post_date_year_monthcoluna inicial significa que, desde que você escreva a consulta sargably, a extração de cada mês pode ser feita com eficiência e as linhas extraídas de um mês serão ordenadas, department_id, location_id, account_idtornando possível uma junção de mesclagem #balancesem uma classificação.

    Embora isso possa beneficiar essa consulta específica, você precisa avaliar a utilidade desse índice em relação à sua carga de trabalho geral.

    Em seguida, calcule os department_id, location_idtotais de #balance(pode aproveitar o pedido PK para evitar uma classificação) e armazene-os em algum lugar e passe para o próximo mês.

    (Ou, possivelmente, em vez de #balancevocê poderia usar uma tabela permanente "temporária" balancee criar uma exibição indexada nela para evitar a etapa de agregação explícita separada e apenas copiar os valores diretamente dela antes de prosseguir)

    • 1

relate perguntas