Evita antipatrones de SQL

Las siguientes recomendaciones proporcionan una guía para evitar los antipatrones de consulta que afectan el rendimiento en BigQuery.

Uniones de tablas con sí mismas

Práctica recomendada: evita las uniones de tablas con sí mismas. En su lugar, usa una función analítica.

Por lo general, las uniones de tablas con sí mismas se usan para procesar relaciones que dependen de las filas. El uso de una unión de tabla con sí misma podría duplicar la cantidad de filas de salida. Este aumento en los datos de salida puede provocar un rendimiento bajo.

En lugar de usar una unión de tabla con sí misma, usa una función analítica para reducir la cantidad de bytes adicionales que genera la consulta.

Sesgo de datos

Práctica recomendada: si tu consulta procesa claves que están muy sesgadas a unos pocos valores, filtra tus datos lo antes posible.

El sesgo de partición, a veces llamado sesgo de datos, ocurre cuando los datos se dividen en particiones de tamaño muy desigual. Esto crea un desequilibrio en la cantidad de datos que se envían entre las ranuras. No puedes compartir particiones entre las ranuras, por lo que, si una partición es demasiado grande, puede ralentizarse o incluso provocar una falla en la ranura que procesa la partición de gran tamaño.

Las particiones aumentan de tamaño cuando tu clave de partición tiene un valor que ocurre con más frecuencia que cualquier otro valor. Por ejemplo, cuando se agrupa por un campo user_id en el que hay muchas entradas de guest o NULL.

Cuando los recursos de una ranura están sobrecargados, se produce un error resources exceeded. Alcanzar el límite de redistribución para una ranura (2 TB en la memoria comprimida) también provoca que la redistribución escriba en el disco y afecte aún más el rendimiento. Los clientes con precios de tasa fija pueden aumentar la cantidad de ranuras asignadas.

Si examinas el plan explicativo de la consulta y ves una diferencia significativa entre los tiempos de procesamiento del promedio y el máximo, es probable que tus datos estén sesgados.

Para evitar problemas de rendimiento provocados por el sesgo de datos:

  • Usa una función agregada aproximada como APPROX_TOP_COUNT para determinar si los datos están sesgados.
  • Filtra tus datos lo antes posible.

Uniones desequilibradas

El sesgo de datos también se puede generar cuando usas cláusulas JOIN. Dado que BigQuery redistribuye datos en cada lado de la unión, todos los datos con la misma clave de unión van a la misma fragmentación. Esta redistribución puede sobrecargar la ranura.

Para evitar problemas de rendimiento asociados con uniones desequilibradas:

  • Realiza un filtro previo a las filas de la tabla con la clave desequilibrada.
  • Si es posible, divide la consulta en dos consultas.

Uniones cruzadas (producto cartesiano)

Práctica recomendada: evita las uniones que generan más salidas que entradas. Cuando se requiere una CROSS JOIN, agrega previamente tus datos.

Las uniones cruzadas son consultas en las que cada fila de la primera tabla se une a cada fila de la segunda tabla (hay claves que no son únicas en ambos lados). En el peor caso, el resultado es el número de filas en la tabla de la izquierda multiplicado por el número de filas en la tabla de la derecha. En casos extremos, la consulta podría no finalizarse.

Si el trabajo de consulta se completa, la explicación del plan de consulta mostrará las filas de salida frente a las filas de entrada. Puedes confirmar un producto cartesiano si modificas la consulta para que copie la cantidad de filas a cada lado de la cláusula JOIN y las agrupe por la clave de unión.

Para evitar problemas de rendimiento asociados con uniones que generan más salidas que entradas:

  • Usa una cláusula GROUP BY para agregar previamente los datos.
  • Usa una función analítica. A menudo, las funciones analíticas son más eficientes que las uniones cruzadas. Para obtener más información, consulta funciones analíticas.

Declaraciones DML que actualizan o insertan filas individuales

Práctica recomendada: evita las declaraciones DML para un punto específico (actualiza o inserta 1 fila a la vez). Carga tus inserciones y actualizaciones por lotes.

El uso de declaraciones DML para un punto específico es un intento de tratar a BigQuery como un sistema de procesamiento de transacciones en línea (OLTP). BigQuery se centra en el procesamiento analítico en línea (OLAP) mediante el uso de análisis de tablas y no de búsquedas de puntos. Si necesitas obtener un comportamiento similar al del OLTP (actualizaciones o inserciones de filas individuales), considera usar una base de datos diseñada para admitir casos prácticos de OLTP como Cloud SQL.

Las declaraciones DML de BigQuery están diseñadas para realizar actualizaciones en forma masiva. Las declaraciones DML UPDATE y DELETE en BigQuery están destinadas a la ejecución de reemplazos periódicos de tus datos, no a las mutaciones de filas individuales. La declaración DML INSERT se diseñó para usarse con moderación. Las inserciones consumen las mismas cuotas de modificación que los trabajos de carga. Si tu caso práctico implica la ejecución de inserciones frecuentes en filas individuales, considera transmitir tus datos en su lugar.

Si el procesamiento por lotes de tus declaraciones UPDATE produce muchas tuplas en las consultas que son muy largas, podrías acercarte al límite de longitud de 256 KB de la consulta. Para evitar exceder el límite de longitud de la consulta, considera si tus actualizaciones se pueden controlar en función de criterios lógicos en lugar de una serie de reemplazos directos de tuplas.

Por ejemplo, podrías cargar tu conjunto de registros de reemplazo en otra tabla y, luego, escribir la declaración DML para actualizar todos los valores de la tabla original si las columnas no actualizadas coinciden. Por ejemplo, si los datos originales se encuentran en la tabla t y las actualizaciones están habilitadas por etapas en la tabla u, la consulta se verá de la siguiente manera:

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key