Performance

Escopo: SQL. Visão transversal: shared/platform/performance.md.

Quase toda query lenta é lenta pelo mesmo motivo: o banco está lendo linhas demais. Ou o índice que serviria àquele filtro não existe, ou a query foi escrita de um jeito que impede o banco de usar o índice que existe. O componente do banco que decide como resolver a query, o query optimizer (otimizador), escolhe o melhor caminho entre os que estão disponíveis, e cabe a você deixar o caminho bom disponível.

Esta página reúne os erros que mais aparecem, cada um com o motivo pelo qual ele custa caro.

O SELECT * traz três problemas. Ele puxa pela rede colunas que ninguém vai usar, e uma coluna de texto longo pesa. Ele impede o covering index (índice que já carrega todas as colunas pedidas), porque o índice nunca cobre todas as colunas da tabela, então o banco precisa voltar à tabela para buscar o resto. E ele prende a query ao schema: no dia em que alguém acrescentar uma coluna, sua query passa a trazê-la sem que você tenha pedido.

SELECT
  *
FROM
  Players
WHERE
  Players.IsActive = 1;

SELECT
  Players.Id,
  Players.Name,
  Players.Position,
  Players.SquadNumber
FROM
  Players
WHERE
  Players.IsActive = 1 -- active
ORDER BY
  Players.SquadNumber;

O índice de JoinedAt guarda as datas ordenadas, e é isso que permite ao banco pular direto para o trecho que interessa. Ao escrever YEAR(Players.JoinedAt) = 2022, você pede o ano, e o índice não guarda anos: ele guarda datas. O banco então calcula YEAR(...) para cada linha da tabela para descobrir quais valem, e a leitura vira uma varredura completa.

A saída é filtrar pela coluna crua, com um intervalo de datas. JoinedAt >= '2022-01-01' AND JoinedAt < '2023-01-01' seleciona o mesmo conjunto de linhas e o índice atende direto.

SELECT
  Players.Name,
  Players.JoinedAt
FROM
  Players
WHERE
  YEAR(Players.JoinedAt) = 2022;

SELECT
  Players.Name,
  Players.JoinedAt
FROM
  Players
WHERE
  Players.JoinedAt >= '2022-01-01' AND
  Players.JoinedAt < '2023-01-01'
ORDER BY
  Players.JoinedAt;

O CAST e o CONVERT em volta da coluna fazem o mesmo estrago que qualquer outra função no WHERE: o banco converte linha a linha e abandona o índice.

O caso difícil de achar é a conversão que você não escreveu. Quando o tipo do parâmetro difere do tipo da coluna, o banco converte a coluna por conta própria para poder comparar. Nenhum erro aparece, nenhum aviso aparece, a query devolve o resultado certo, e o índice ficou de fora. A query que rodava em 20 ms passa a rodar em 4 segundos, e o código-fonte da query está igual ao que sempre esteve.

A correção é sempre a mesma: converta o parâmetro e deixe a coluna intacta.

-- SquadNumber é INT; comparação como texto força conversão de cada linha
SELECT
  Players.Name,
  Players.SquadNumber,
  Players.Position
FROM
  Players
WHERE
  CAST(Players.SquadNumber AS NVARCHAR) = @SquadParam AND
  Players.IsActive = 1;

-- @SquadNumber declarado como INT na aplicação; zero conversão no banco
SELECT
  Players.Name,
  Players.SquadNumber,
  Players.Position
FROM
  Players
WHERE
  Players.SquadNumber = @SquadNumber AND
  Players.IsActive = 1 -- active
ORDER BY
  Players.SquadNumber;

Um detalhe do SQL Server pega muita gente: o literal 'Alice Smith', sem prefixo, é um VARCHAR. Se a coluna Players.Name é NVARCHAR, os tipos diferem, e o banco converte o Name de cada linha antes de comparar. O prefixo N no literal (N'Alice Smith') resolve, porque aí os dois lados já são do mesmo tipo.

-- Players.Name é NVARCHAR; literal sem prefixo N é VARCHAR
-- SQL Server converte cada Name para VARCHAR antes de comparar
SELECT
  Players.Id,
  Players.Name,
  Players.Position
FROM
  Players
WHERE
  Players.Name = 'Alice Smith'; -- VARCHAR literal, índice ignorado

SELECT
  Players.Id,
  Players.Name,
  Players.Position
FROM
  Players
WHERE
  Players.Name = N'Alice Smith'; -- NVARCHAR literal, índice aproveitado

Quando as duas colunas do JOIN têm tipos diferentes, uma das duas vai ser convertida linha a linha. Você escolhe qual. Convertendo a coluna que tem índice, o índice se perde; convertendo a que não tem, ele sobrevive. A saída melhor é alinhar os tipos no schema e não converter nada.

-- FootballTeams.Id é INT; ExternalTeams.TeamReference é NVARCHAR
-- CAST em FootballTeams.Id descarta o índice em Id
SELECT
  FootballTeams.Name,
  ExternalTeams.LeagueName
FROM
  FootballTeams
JOIN
  ExternalTeams ON CAST(FootballTeams.Id AS NVARCHAR) = ExternalTeams.TeamReference;

-- opção 1: converter o lado não indexado, índice em FootballTeams.Id preservado
SELECT
  FootballTeams.Name,
  ExternalTeams.LeagueName
FROM
  FootballTeams
JOIN
  ExternalTeams ON FootballTeams.Id = CAST(ExternalTeams.TeamReference AS INT);

-- opção 2 (ideal): alinhar os tipos no schema e eliminar a conversão
-- ALTER TABLE ExternalTeams ALTER COLUMN TeamReference INT NOT NULL;

A data guardada em VARCHAR obriga um CONVERT em toda query que filtra por período, e o CONVERT em volta da coluna já derruba o índice. Ela traz um problema pior junto: como o banco aceita qualquer texto, a mesma coluna acaba guardando 2024-01-15, 15/01/2024 e 2024/01/15, e nenhuma comparação funciona direito.

A correção mora no schema. Declare a coluna como DATE ou DATETIME2 e converta na aplicação antes do INSERT.

-- JoinedAt definido como VARCHAR(10): '2024-01-15', '15/01/2024', '2024/01/15'
SELECT
  Players.Name,
  Players.JoinedAt
FROM
  Players
WHERE
  CONVERT(DATE, Players.JoinedAt) >= '2024-01-01' AND
  CONVERT(DATE, Players.JoinedAt) < '2025-01-01';

-- schema correto: JoinedAt DATE NOT NULL
-- aplicação envia Date, não string; zero conversão no banco
SELECT
  Players.Name,
  Players.JoinedAt
FROM
  Players
WHERE
  Players.JoinedAt >= '2024-01-01' AND
  Players.JoinedAt < '2025-01-01'
ORDER BY
  Players.JoinedAt;

A subquery que aparece na lista de colunas é executada para cada linha que a query devolve. Se a consulta traz mil times, aquele COUNT de jogadores roda mil vezes. Levando a contagem para um LEFT JOIN com GROUP BY, o banco faz a conta uma vez para todos os times de uma vez.

SELECT
  FootballTeams.Name,
  (
    SELECT COUNT(Players.Id)
    FROM Players
    WHERE
      Players.TeamId = FootballTeams.Id AND
      Players.IsActive = 1
  ) AS TotalPlayers
FROM
  FootballTeams
WHERE
  FootballTeams.IsActive = 1;

WITH ActivePlayerCountCTE AS
(
  SELECT
    Players.TeamId,
    COUNT(Players.Id) AS TotalPlayers
  FROM
    Players
  WHERE
    Players.IsActive = 1 -- active
  GROUP BY
    Players.TeamId
)

SELECT
  FootballTeams.Name,
  ActivePlayerCountCTE.TotalPlayers
FROM
  FootballTeams
JOIN
  ActivePlayerCountCTE ON FootballTeams.Id = ActivePlayerCountCTE.TeamId
WHERE
  FootballTeams.IsActive = 1 -- active
ORDER BY
  ActivePlayerCountCTE.TotalPlayers DESC;

Sem índice em TeamId, o banco lê a tabela inteira de jogadores para achar os de um time. Com o índice, ele vai direto ao grupo de linhas daquele time. A regra prática: as colunas que aparecem no WHERE, no JOIN e no ORDER BY são candidatas a índice.

-- sem índice em TeamId: o banco lê todos os registros da tabela
SELECT
  Players.Name,
  Players.Position,
  Players.SquadNumber
FROM
  Players
WHERE
  Players.TeamId = @TeamId AND
  Players.IsActive = 1;

CREATE INDEX IX_Players_TeamId
  ON Players (TeamId);

Seletividade é quantos valores diferentes a coluna tem. IsActive guarda dois valores (0 e 1), então filtrar por ela deixa metade da tabela de fora, e olhe lá. TeamId guarda centenas de valores, e filtrar por ele deixa quase tudo de fora.

O índice composto ordena as linhas pela primeira coluna, depois pela segunda. Colocando TeamId primeiro, o banco chega ao punhado de linhas daquele time e usa IsActive para escolher dentro desse punhado. Na ordem inversa, ele começaria por metade da tabela.

-- IsActive tem apenas dois valores (0 / 1): índice ineficiente sozinho
CREATE INDEX IX_Players_IsActive
  ON Players (IsActive);

CREATE INDEX IX_Players_TeamId_IsActive
  ON Players (TeamId, IsActive);

O índice de TeamId guarda o TeamId e o endereço da linha. Quando a query pede Name, Position e SquadNumber, o banco encontra as linhas pelo índice e volta à tabela para buscar essas três colunas, uma linha de cada vez. Essa volta se chama key lookup, e com muitas linhas ela custa mais que a busca em si.

O INCLUDE guarda as três colunas dentro do próprio índice. A query se resolve sem tocar a tabela.

CREATE INDEX IX_Players_TeamId_IsActive
  ON Players (TeamId, IsActive);

-- esta query ainda acessa a tabela principal para buscar as colunas do SELECT
SELECT
  Players.Name,
  Players.Position,
  Players.SquadNumber
FROM
  Players
WHERE
  Players.TeamId = @TeamId AND
  Players.IsActive = 1;

CREATE INDEX IX_Players_TeamId_IsActive_Cover
  ON Players (TeamId, IsActive)
  INCLUDE (Name, Position, SquadNumber);

A FK (Foreign Key · chave estrangeira) declara que Players.TeamId aponta para um time. Antes de apagar ou atualizar um time, o banco precisa saber se algum jogador ainda aponta para ele, e vai procurar em Players.TeamId. Sem índice nessa coluna, cada DELETE na tabela de times varre a tabela de jogadores inteira.

Criar a FK não cria o índice: são duas declarações separadas, e a segunda é fácil de esquecer.

CREATE TABLE Players
(
  Id UNIQUEIDENTIFIER NOT NULL,
  TeamId UNIQUEIDENTIFIER NOT NULL,

  CONSTRAINT PK_Players PRIMARY KEY (Id),
  CONSTRAINT FK_Players_FootballTeams FOREIGN KEY (TeamId)
    REFERENCES FootballTeams (Id)
);

-- DELETE em FootballTeams faz full scan em Players para checar filhos

CREATE TABLE Players
(
  Id UNIQUEIDENTIFIER NOT NULL,
  TeamId UNIQUEIDENTIFIER NOT NULL,

  CONSTRAINT PK_Players PRIMARY KEY (Id),
  CONSTRAINT FK_Players_FootballTeams FOREIGN KEY (TeamId)
    REFERENCES FootballTeams (Id)
);

CREATE INDEX IX_Players_TeamId
  ON Players (TeamId);

O tipo do identificador decide o tamanho do índice e a frequência com que o banco precisa reorganizar as páginas dele.

O índice fica guardado em páginas ordenadas. Quando o identificador cresce sempre (1, 2, 3), cada linha nova entra no fim do índice, e o banco só abre página nova quando a última encheu. O UUID (Universally Unique Identifier · Identificador Universalmente Único) v4 é aleatório, então a linha nova cai em qualquer ponto do índice, muitas vezes no meio de uma página cheia. O banco então parte a página em duas para abrir espaço, o que se chama page split. Em uma tabela que recebe muitas inserções, os splits se acumulam, o índice fica esparso e tanto a leitura quanto a escrita ficam mais lentas.

O UUID v7 resolve o impasse: ele começa pelo horário de criação e termina com bits aleatórios. Como o horário sempre cresce, os identificadores nascem em ordem e cada linha nova entra perto do fim do índice, do jeito que um BIGINT entraria, e ainda assim continuam únicos entre máquinas diferentes. Quem gera o UUID v7 é a aplicação, e o banco recebe o valor pronto.

CREATE TABLE Orders
(
  Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(), -- random: page splits constantes
  CustomerId UNIQUEIDENTIFIER NOT NULL,
  CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),

  CONSTRAINT PK_Orders PRIMARY KEY (Id)
);

CREATE TABLE Orders
(
  Id BIGINT NOT NULL IDENTITY(1, 1),
  CustomerId BIGINT NOT NULL,
  CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),

  CONSTRAINT PK_Orders PRIMARY KEY (Id)
);

-- o ID é gerado na aplicação antes do INSERT
-- Guid.CreateVersion7(): .NET 9+
-- uuidv7(): npm uuid
-- pg_uuidv7: extensão PostgreSQL
CREATE TABLE Orders
(
  Id UNIQUEIDENTIFIER NOT NULL, -- sem DEFAULT: valor vem da aplicação
  CustomerId UNIQUEIDENTIFIER NOT NULL,
  CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),

  CONSTRAINT PK_Orders PRIMARY KEY (Id)
);

NEWSEQUENTIALID() (SQL Server) é uma alternativa nativa sequencial, mas só funciona como DEFAULT: não é portável entre bancos e impede geração de ID antes do INSERT.

Trazer os cinquenta mil times para a memória da aplicação e mostrar vinte deles gasta rede, gasta memória e demora. O OFFSET ... FETCH NEXT diz ao banco quantas linhas pular e quantas devolver, e só as vinte atravessam a rede.

O ORDER BY é obrigatório aqui. Sem ele o banco não tem critério para decidir quem é a linha 21, e o mesmo registro pode aparecer em duas páginas seguidas.

SELECT
  FootballTeams.Id,
  FootballTeams.Name,
  FootballTeams.ChampionshipsWon
FROM
  FootballTeams
WHERE
  FootballTeams.IsActive = 1
ORDER BY
  FootballTeams.ChampionshipsWon DESC;
-- aplicação filtra a página no código

-- SQL Server
SELECT
  FootballTeams.Id,
  FootballTeams.Name,
  FootballTeams.ChampionshipsWon
FROM
  FootballTeams
WHERE
  FootballTeams.IsActive = 1
ORDER BY
  FootballTeams.ChampionshipsWon DESC
OFFSET @Page * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

-- PostgreSQL
SELECT
  FootballTeams.Id,
  FootballTeams.Name,
  FootballTeams.ChampionshipsWon
FROM
  FootballTeams
WHERE
  FootballTeams.IsActive = 1
ORDER BY
  FootballTeams.ChampionshipsWon DESC
LIMIT :pageSize OFFSET :page * :pageSize;

DoDocs v3.7.0 · Desenvolvido por @thiagocajadev · Baseado no trabalho de pmndrs/docs · Poimandres.