Supercarga tus consultas SQL para bases de datos de producción

Artem Yevtushenko | Builder Education |7 min read |April 24, 2019

SQL es una de las herramientas de manejo de datos más poderosas que existen. En SQL Superstar, te damos consejos prácticos para ayudarte a sacar el máximo partido a este versátil lenguaje y crear consultas bonitas y eficaces.

Si no dispone de un almacén de datos o de una base de datos analítica independiente para la elaboración de informes, es probable que la base de datos de producción sea su única fuente de datos actualizados. Al consultar una base de datos de producción, la optimización es clave. Una consulta ineficiente agotará los recursos de la base de datos de producción y provocará un rendimiento lento o la pérdida de servicio para otros usuarios si la consulta contiene errores. Es vital que optimice sus consultas para un impacto mínimo en el rendimiento de la base de datos.

Defina primero los requisitos del negocio

Hemos cubierto las mejores prácticas para definir los requisitos del negocio para el BI en otro lugar. Definitivamente, asegúrese de que está aplicando esas prácticas al optimizar las consultas SQL, incluyendo:

• Identificar las partes interesadas pertinentes. Asegúrese de que todas las partes necesarias están en la discusión para desarrollar su consulta. Al consultar las bases de datos de producción, asegúrese de que el equipo de DBA está incluido.
• Enfóquese en los resultados del negocio. Dé a la consulta un propósito definido y único. Gravar la base de datos de producción para informes exploratorios o duplicados es un riesgo innecesario.
• Enmarque la discusión para obtener requisitos óptimos. Defina la función y el alcance del informe identificando su público objetivo. Esto centrará la consulta en las tablas con el nivel de detalle correcto.
• Haga grandes preguntas. Siga las 5 W’s: ¿Quién? ¿Qué? ¿Dónde? ¿Cuándo? ¿Por qué?
• Escriba requisitos muy específicos y confírmelos con las partes interesadas. El rendimiento de la base de datos de producción es demasiado crítico para tener requisitos poco claros o ambiguos. Asegúrese de que los requisitos son lo más específicos posible y confirme los requisitos con todas las partes interesadas antes de ejecutar la consulta.

SELECCIONE los campos en lugar de utilizar SELECT *

Cuando se ejecutan consultas exploratorias, muchos desarrolladores de SQL utilizan SELECT * (leído como «select all») como una forma abreviada de consultar todos los datos disponibles de una tabla. Sin embargo, si una tabla tiene muchos campos y muchas filas, esto grava los recursos de la base de datos al consultar muchos datos innecesarios.

El uso de la sentencia SELECT indicará a la base de datos que consulte sólo los datos que necesita para cumplir con los requisitos del negocio. Este es un ejemplo en el que los requisitos del negocio solicitan las direcciones de correo de los clientes.

Ineficiente:

SELECT *
FROM Customers

Esta consulta puede tirar de otros datos también almacenados en la tabla de clientes, como números de teléfono, fechas de actividad y notas de ventas y servicio al cliente.

Eficiente:

SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers

Esta consulta es mucho más limpia y sólo extrae la información necesaria para las direcciones de correo.

Para mantener un índice de todas las tablas y nombres de campos, ejecute una consulta desde una tabla del sistema como INFORMATION_SCHEMA o ALL_TAB_COLUMNS (para MS SQL Server, lea esto).

Empieza hoy mismo con SQL:



Evita SELECT DISTINCT

SELECT DISTINCT es una forma práctica de eliminar duplicados de una consulta. SELECT DISTINCT funciona agrupando todos los campos de la consulta para crear resultados distintos. Sin embargo, para lograr este objetivo se requiere una gran cantidad de potencia de procesamiento. Además, los datos pueden agruparse hasta el punto de ser inexactos. Para evitar el uso de SELECT DISTINCT, seleccione más campos para crear resultados únicos.

Ineficiente e inexacto:

SELECT DISTINCT FirstName, LastName, State
FROM Customers

Esta consulta no tiene en cuenta que varias personas del mismo estado tengan el mismo nombre y apellido. Los nombres populares como David Smith o Diane Johnson se agruparán, lo que provocará un número inexacto de registros. En bases de datos más grandes, un gran número de David Smiths y Diane Johnsons hará que esta consulta se ejecute lentamente.

Eficiente y precisa:

SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers

Al añadir más campos, se han devuelto registros no duplicados sin utilizar SELECT DISTINCT. La base de datos no tiene que agrupar ningún campo y el número de registros es preciso.

Vea a Sisense en acción:



Explore el tablero de control

Cree uniones con INNER JOIN (no WHERE)

Algunos desarrolladores de SQL prefieren hacer uniones con cláusulas WHERE, como las siguientes:

SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID

Este tipo de join crea un Cartesian Join, también llamado Cartesian Product o CROSS JOIN.

En un Cartesian Join se crean todas las combinaciones posibles de las variables. En este ejemplo, si tuviéramos 1.000 clientes con 1.000 ventas totales, la consulta generaría primero 1.000.000 de resultados, y luego filtraría los 1.000 registros en los que CustomerID está correctamente unido. Esto es un uso ineficiente de los recursos de la base de datos, ya que la base de datos ha hecho 100 veces más trabajo del necesario. Las uniones cartesianas son especialmente problemáticas en bases de datos a gran escala, ya que una unión cartesiana de dos tablas grandes podría crear miles de millones o trillones de resultados.

Para evitar la creación de un Cartesian Join, utilice INNER JOIN en su lugar:

SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID

La base de datos sólo generaría los 1.000 registros deseados en los que CustomerID es igual.

Algunos sistemas DBMS son capaces de reconocer los WHERE joins y ejecutarlos automáticamente como INNER JOINs en su lugar. En esos sistemas SGBD, no habrá diferencia de rendimiento entre una unión WHERE y una INNER JOIN. Sin embargo, todos los sistemas SGBD reconocen el INNER JOIN. Su DBA le aconsejará cuál es el mejor en su entorno.

Utilice WHERE en lugar de HAVING para definir filtros

El objetivo de una consulta eficiente es extraer sólo los registros necesarios de la base de datos. Según el orden de operaciones de SQL, las sentencias HAVING se calculan después de las sentencias WHERE. Si la intención es filtrar una consulta basada en condiciones, una sentencia WHERE es más eficiente.

Por ejemplo, supongamos que se han realizado 200 ventas en el año 2016, y queremos consultar el número de ventas por cliente en 2016.

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.CustomerID, Customers.Name
HAVING Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#

Esta consulta sacaría 1.000 registros de ventas de la tabla Ventas, luego filtraría para los 200 registros generados en el año 2016, y finalmente contaría los registros en el conjunto de datos.

En comparación, las cláusulas WHERE limitan el número de registros extraídos:

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name

Esta consulta extraería los 200 registros del año 2016 y luego contaría los registros en el conjunto de datos. El primer paso de la cláusula HAVING se ha eliminado por completo.

La cláusula HAVING sólo debe utilizarse cuando se filtra en un campo agregado. En la consulta anterior, podríamos filtrar adicionalmente los clientes con más de 5 ventas utilizando una sentencia HAVING.

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
HAVING Count(Sales.SalesID) > 5

>>Free Starter Kit: Empiece hoy mismo con nuestro kit de inicio de SQL gratuito

Utilice comodines sólo al final de una frase

Cuando se buscan datos en texto plano, como ciudades o nombres, los comodines crean la búsqueda más amplia posible. Sin embargo, la búsqueda más amplia es también la más ineficiente.

Cuando se utiliza un comodín inicial, especialmente en combinación con un comodín final, la base de datos se encarga de buscar en todos los registros una coincidencia en cualquier parte del campo seleccionado.

Considere esta consulta para obtener las ciudades que comienzan con ‘Char’:

SELECT City FROM Customers
WHERE City LIKE ‘%Char%’

Esta consulta obtendrá los resultados esperados de Charleston, Charlotte y Charlton. Sin embargo, también obtendrá resultados inesperados, como Cape Charles, Crab Orchard y Richardson.

Una consulta más eficiente sería:

SELECT City FROM Customers
WHERE City LIKE ‘Char%’

Esta consulta sólo obtendrá los resultados esperados de Charleston, Charlotte y Charlton.

Utilizar LIMIT para muestrear los resultados de la consulta

Antes de ejecutar una consulta por primera vez, asegúrese de que los resultados serán deseables y significativos utilizando una sentencia LIMIT. (En algunos sistemas SGBD, la palabra TOP se utiliza indistintamente con LIMIT.) La sentencia LIMIT devuelve sólo el número de registros especificados. El uso de una sentencia LIMIT evita gravar la base de datos de producción con una consulta grande, sólo para descubrir que la consulta necesita ser editada o refinada.

En la consulta de ventas de 2016 de arriba, examinaremos un límite de 10 registros:

SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
LIMIT 10

Podemos ver por la muestra si tenemos un conjunto de datos utilizable o no.

Ejecute su consulta durante las horas de menor actividad

Para minimizar el impacto de sus consultas analíticas en la base de datos de producción, hable con un DBA para programar la consulta para que se ejecute en una hora de menor actividad. La consulta debe ejecutarse cuando los usuarios concurrentes estén en su número más bajo, que suele ser a mitad de la noche (de 3 a 5 de la mañana).

Cuantos más de los siguientes criterios tenga su consulta, más probable será que se ejecute por la noche:

• Seleccionar de tablas grandes (>1.000,000 registros)
• Juntas cartesianas o CROSS JOINs
• Instrucciones de bucle
• Instrucciones SELECT DISTINCT
• Subconsultas anidadas
• Búsquedas con caracteres comodín en campos de texto largo o memo campos
• Consultas de esquema múltiple

Consultas con confianza

Con estos consejos en mente (más algunos otros consejos y trucos de SQL en su bolsillo) debería ser capaz de construir consultas eficientes, consultas hermosas que se ejecutarán sin problemas y devolverán las ideas que cambian el juego que su equipo necesita.

Obtenga el kit de inicio de SQL gratuito aquí: