De 26 minutos para 8 segundos: replicando um export legado

A planilha

A empresa de mídia com quem trabalho opera parte das vendas no sistema legado de um fornecedor. Esse sistema alimenta o financeiro com uma planilha que o time chama de "o pipeline": 62 colunas, uma linha por proposta, local e período, os meses do ano em colunas e um bloco de faturamento no fim (status, datas, número da nota, boleto, quem tratou). Ela é gerada toda noite à 01:10, e o time baixa de manhã. Existe uma variante com 82 colunas e uma completa, com 83, três vezes por dia.

Estávamos integrando o nosso sistema comercial com o sistema desse fornecedor. Em 14 de agosto, olhando a tabela de onde essas planilhas saem, fiz a pergunta óbvia na sessão com o agente: por que a gente mesmo não preenche isso? O plano que saiu dali foi replicar do jeito que está, talvez melhorar depois, e parar de depender deles para isso.

Passo 1: replicar a tabela de origem, e provar

As duas planilhas saem de uma tabela de 189 colunas que o legado preenche. O primeiro trabalho foi uma réplica do que a preenche, escrita no nosso código, lendo o banco deles e sem gravar nada. Depois, um backfill de validação: rodar a réplica para toda proposta desde maio e comparar com a tabela deles, campo a campo.

Essa rodada levou 32 segundos. Comparou 1.495 propostas, 4.689 linhas, 862.297 campos, e achou 0 divergentes.

O número de capa era 97,76% OK. Logo abaixo, o relatório imprime o número honesto: 95,82%. A diferença são 17.484 campos que nunca foram comparados, porque alguns grupos de linhas só existiam de um lado (45 só nossos, 48 só deles) e o número de capa só olhava os grupos que formaram par. Com o grão errado, um validador desses mostra 100% sem comparar uma linha sequer, então os dois números ficam no relatório.

Todo campo que não batia precisava de uma causa com nome e evidência, senão reprovava:

De forma simplificada, o classificador é assim, e a parte importante é o default no fim:

// toda divergência precisa de um veredito com nome e evidência; sem veredito, reprova
$veredito = match (true) {
    $nosso === $deles                         => 'OK',
    $this->bugConhecidoDoLegado($col, $row)   => 'BUG_DELES',
    $this->origemMudouDepois($row, $col)      => 'REAL_DEFASADO',
    $this->cadastroMudouDepois($row, $col)    => $this->temLogDeCadastro($row, $col)
                                                    ? 'CADASTRO_MUDOU'
                                                    : 'SEM_PROVA',
    default                                   => 'DIVERGENTE',
};

SEM_PROVA é a minha linha preferida dessa tabela. "Provavelmente o cadastro mudou" explica quase qualquer coisa, então, sem log de alteração, não conta como explicado. Vai para uma lista de investigação, que ficou com 37 propostas depois dessa rodada.

Passo 2: de onde vinham os 26 minutos

Com a tabela provada, as planilhas. Li o código de exportação do legado. Na diária, ele fazia umas 10 consultas por linha: repasse, nota fiscal, boleto, três buscas em logs, o responsável e mais três para um filtro de propriedade. 8.232 linhas vezes 10 dá umas 82 mil consultas, e o próprio log de geração deles dizia 01:10:03 a 01:36:01. A variante de 82 colunas era pior, 53 consultas por linha, porque cada uma das 24 colunas mensais de contabilização custava duas consultas: umas 138 mil consultas em 43 minutos.

Um N+1 clássico. A réplica junta primeiro os ids das propostas do recorte, depois roda uma consulta por tabela de apoio para cada lote de 3.000 ids e guarda os resultados em arrays por chave. O cursor principal lê as linhas em fluxo e só consulta esses arrays, então o número de consultas depende do número de tabelas e de lotes, nunca do número de linhas.

private const LOTE = 3000;

/** listas "1,2,3" de ids inteiros, LOTE por vez */
private function lotes(array $ids): array
{
    $ids = array_values(array_unique(array_map('intval', $ids)));
    return array_map(fn (array $c) => implode(',', $c), array_chunk($ids, self::LOTE));
}

/** uma consulta por lote, onde o legado fazia uma por linha */
private function prefetchArquivos(string $tabela, array $propostas): array
{
    $out = [];
    foreach ($this->lotes($propostas) as $in) {
        $rows = $this->db->query(
            "SELECT id_proposta, arquivo FROM {$tabela} WHERE id_proposta IN ({$in}) ORDER BY id ASC"
        )->getResultArray();

        foreach ($rows as $r) {
            $p = (int) $r['id_proposta'];
            // o legado monta arquivo & "," & acumulado, então a lista sai invertida. Mantemos.
            $out[$p] = isset($out[$p]) && $out[$p] !== '' ? $r['arquivo'] . ',' . $out[$p] : $r['arquivo'];
        }
    }
    return $out;
}

Os ids são convertidos para inteiro antes de entrar no IN, então a lista pode ser montada como texto com segurança. A planilha completa tem 69.783 linhas × 83 colunas, perto de 5,8 milhões de células, por isso passa por um gravador de XLSX em streaming, com memória constante.

Quando o agente me mostrou o primeiro número, respondi mais ou menos assim: "você tá falando sério, que levava 26 minutos e agora leva 8 segundos?". Só acreditei quando o diff contra o arquivo deles da 01:10 ficou verde.

Passo 3: o mesmo arquivo, célula a célula

Nossa ferramenta de diff lê os dois XLSX, alinha as linhas pela chave e compara célula a célula, incluindo tipo e formato. Em dia útil nunca dá 100%, porque o arquivo deles é da 01:10, o nosso é de horas depois, e o faturamento anda nesse intervalo. Em 4 de setembro a diária bateu 99,95%, e as 247 diferenças eram todas faturamentos concluídos ou reprovados naquele mesmo dia.

Duas coisas só o arquivo real mostrou. A coluna de nota fiscal aparece em todas as linhas de uma proposta: o código do legado tenta zerar a variável a cada linha, mas o teste de "a proposta mudou" não segura, e o valor se repete. E a variante de 82 colunas não seleciona uma coluna que as outras têm, porque a tabela deles não tem essa coluna. Replicamos as duas. O financeiro concilia contra esse arquivo, então mudar o que ele mostra é decisão de negócio, e os bugs vão para uma lista à parte, para corrigir com o ok deles.

O teste limpo veio no sábado, 5 de setembro, sem movimento de faturamento. O arquivo deles da 01:10 contra o nosso: 8.315 linhas, 515.530 células, 100% idênticas, 0 linhas sobrando de qualquer lado. A variante, 214.348 células, também 100%. A réplica está em produção desde 4 de setembro, e o plano é manter os jobs do legado rodando até o diff ficar limpo por 10 dias úteis seguidos, e só então desligar os deles.

O que eu faria igual

Replicar antes de melhorar, com o sistema antigo rodando ao lado, porque o antigo é o gabarito e também o rollback. Provar com a saída real deles, e não com a minha leitura do código deles: as duas esquisitices acima estavam no código, e eu só as vi no arquivo. Pôr o número honesto ao lado do bonito e dar nome a toda divergência. A velocidade veio quase de graça quando o N+1 ficou visível; a maior parte do trabalho estava na prova.

Arquivado pelo problema que resolve

• consultas n+1
• sistemas legados
• validação de dados
• migração
• php
• mysql
• relatórios