Como evitar antipadrões do SQL

As práticas recomendadas a seguir apresentam uma orientação sobre como evitar os antipadrões de consulta que afetam o desempenho no BigQuery.

União automática

Prática recomendada: evite as uniões automáticas. Em vez disso, use uma função de janela.

Em geral, as junções automáticas são usadas para calcular relacionamentos dependentes de linha. O resultado disso é uma possível duplicação do número de linhas de saída. Esse aumento nos dados de saída pode prejudicar o desempenho.

Em vez de usar uma união automática, use uma função de janela (analítica) para reduzir o número de bytes adicionais gerados pela consulta.

Desvio de dados

Prática recomendada: se a sua consulta processa chaves que estão muito inclinadas para alguns valores, filtre os dados o quanto antes.

O desvio de partição, às vezes chamado de desvio de dados, ocorre quando os dados são divididos em partições de tamanhos muito desiguais. Isso cria um desequilíbrio na quantidade de dados enviados entre slots. Não é possível compartilhar partições entre slots, portanto, se uma partição for especialmente grande, ela pode diminuir a velocidade, ou até mesmo interromper o slot que processa a partição com tamanho muito grande.

As partições tornam-se grandes quando sua chave de partição tem um valor que ocorre com mais frequência do que qualquer outro valor. Por exemplo, agrupar por um campo user_id onde há muitas entradas para guest ou NULL.

Quando os recursos de um slot estão sobrecarregados, ocorre um erro resources exceeded. Alcançar o limite aleatório para um slot (2 TB na memória compactada) também faz com que essa aleatoriedade grave no disco e cause mais impactos ao desempenho. Os clientes com preços baseados em taxa fixa podem aumentar o número de slots alocados.

Se você examinar a explicação do plano de consulta e identificar uma diferença significativa entre os tempos de computação médio e máximo, saberá que seus dados podem estar distorcidos.

Para evitar problemas de desempenho resultantes da distorção de dados, siga estas recomendações:

  • Use uma função agregada aproximada, como APPROX_TOP_COUNT para determinar se os dados estão distorcidos.
  • Filtre seus dados o mais cedo possível.

Uniões desequilibradas

O desvio de dados também pode aparecer quando você usa as cláusulas JOIN. Como o BigQuery mistura os dados em cada lado da união, todos os dados com a mesma chave de união vão para o mesmo fragmento. Essa mistura pode sobrecarregar o slot.

Para evitar problemas de desempenho resultantes de uniões desequilibradas:

  • Filtre previamente as linhas da tabela com a chave desequilibrada.
  • Se possível, divida a consulta em duas.

Uniões cruzadas (produto cartesiano)

Prática recomendada: evite uniões que gerem mais saídas do que entradas. Quando for necessário usar CROSS JOIN, agregue seus dados previamente.

As uniões cruzadas são consultas em que cada linha da primeira tabela é unida a cada linha na segunda tabela (há chaves não únicas nos dois lados). O pior caso de saída é o número de linhas na tabela esquerda multiplicado pelo número de linhas na tabela direita. Em casos extremos, a consulta pode não terminar.

Se o trabalho de consulta for concluído, a explicação do plano de consulta mostrará linhas de saída versus linhas de entrada. É possível confirmar um produto cartesiano com a modificação da consulta, de maneira a imprimir o número de linhas em cada lado da cláusula JOIN, agrupada pela chave de junção.

Para evitar problemas de desempenho resultantes de uniões que geram mais saídas do que entradas:

  • Use uma cláusula GROUP BY para agregar dados previamente.
  • Use uma função de janela. As funções de janela são muitas vezes mais eficientes que a utilização de uma união cruzada. Para saber mais informações, consulte as funções analíticas.

Declarações DML que atualizam ou inserem linhas individuais

Prática recomendada: evite declarações DML de pontos específicos com a atualização ou inserção de uma linha por vez. Agrupe suas atualizações e inserções.

O uso de declarações DML específicas de pontos é uma tentativa de tratar o BigQuery como um processamento de transações on-line (OLTP, na sigla em inglês). O BigQuery se concentra no processamento analítico on-line (OLAP, na sigla em inglês) usando varreduras de tabelas e não pesquisas de pontos. Se você precisa de comportamento semelhante ao do OLTP (atualizações ou inserções de linha única), considere um banco de dados projetado para suportar casos de uso de OLTP, como o Cloud SQL.

As declarações DML do BigQuery destinam-se a atualizações em massa. As declarações DML UPDATE e DELETE no BigQuery são orientadas para reescrever seus dados periodicamente, e não para mutações de uma única linha. A declaração DML INSERT deve ser utilizada com moderação. As inserções consomem as mesmas quotas de modificação que os jobs de carregamento. Se seu caso de uso envolver inserções frequentes de uma única linha, considere transmitir seus dados.

Se o agrupamento de suas declarações UPDATE resultar em muitas tuplas em consultas muito longas, você pode abordar o limite de comprimento da consulta de 256 KB. Para contornar esse limite, considere se as atualizações podem ser tratadas com base em um critério lógico em vez de uma série de substituições de tuplas diretas.

Por exemplo, você pode carregar seu conjunto de registros de substituição em outra tabela e, em seguida, escrever a declaração DML para atualizar todos os valores na tabela original se as colunas não atualizadas corresponderem. Por exemplo, se os dados originais estivessem na tabela t e as atualizações estivessem na tabela u, a consulta teria a seguinte aparência:

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key
Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.