SQL_SERV

Práctica 4. Optimización de una consulta compleja

Objetivo de la práctica:

Duración aproximada:

Insumos necesarios:

Para esta práctica es recomendable trabajar con una base de datos que contenga un gran volumen de información (varios miles o millones de registros) para observar diferencias significativas en el rendimiento antes y después de la optimización.
Puedes utilizar:

Preparación del entorno:

Asegúrate de estar trabajando en una base de datos con gran volumen de datos para observar los efectos de la optimización. Si no tienes una base de datos grande, utiliza la siguiente consulta para generar datos de prueba:

CREATE TABLE Ventas (
    VentaID INT PRIMARY KEY,
    Cliente NVARCHAR(100),
    NumeroPedido NVARCHAR(50),
    Precio DECIMAL(10, 2),
    FechaVenta DATE
);

-- Declarar las variables necesarias antes del ciclo
DECLARE @i INT;
DECLARE @randomCliente NVARCHAR(100);
DECLARE @randomPrecio DECIMAL(10,2);
DECLARE @randomFechaVenta DATE;
DECLARE @randomPedido NVARCHAR(50);

-- Inicializar la variable contador
SET @i = 1;

-- Iniciar el ciclo para insertar 1 millón de registros
WHILE @i <= 1000000
BEGIN
    -- Generar un cliente aleatorio entre 1 y 1000
    SET @randomCliente = 'Cliente' + CAST(FLOOR(RAND() * 1000) + 1 AS NVARCHAR(100));

    -- Generar un precio aleatorio entre 10.00 y 1000.00
    SET @randomPrecio = CAST((RAND() * (1000 - 10) + 10) AS DECIMAL(10, 2));

    -- Generar una fecha de venta aleatoria en los últimos 2 años
    SET @randomFechaVenta = DATEADD(DAY, -FLOOR(RAND() * 730), GETDATE());

    -- Generar un número de pedido aleatorio
    SET @randomPedido = 'Pedido' + CAST(FLOOR(RAND() * 10000) + 1 AS NVARCHAR(50));

    -- Insertar el registro en la tabla
    INSERT INTO Ventas (VentaID, Cliente, NumeroPedido, Precio, FechaVenta)
    VALUES 
    (
        @i, 
        @randomCliente,         -- Cliente aleatorio
        @randomPedido,          -- Pedido aleatorio
        @randomPrecio,          -- Precio
        @randomFechaVenta       -- Fecha de venta
    );

    -- Incrementar el contador
    SET @i = @i + 1;
END;

Identificación de una consulta ineficiente:

  -- Consulta no optimizada con problemas
SELECT *
FROM Ventas
WHERE YEAR(FechaVenta) = 2023  -- Uso de una función que deshabilita el uso de índices
  AND Cliente LIKE '%50%'      -- Búsqueda con un comodín al inicio que también evita índices
ORDER BY Precio DESC;           -- Ordenación de una columna sin índice

Para analizar el rendimiento de la consulta, utilizar la opción de “Mostrar plan de ejecución estimado” en SQL Server Management Studio (SSMS) o ejecutar la consulta con SET STATISTICS TIME ON; y SET STATISTICS IO ON; para obtener información detallada sobre el tiempo de ejecución y el uso de recursos.

Análisis del plan de ejecución:

Observar el plan de ejecución para identificar cuellos de botella, como:

Optimización de la consulta:

  1. Creación de índices: Si el plan de ejecución muestra que se está haciendo un Table Scan sobre la Tabla Ventas, es posible que no haya un índice adecuado para la consulta. Crear un índice sobre las columnas FechaVenta y Monto para mejorar el rendimiento de las consultas con filtros y agregaciones: ```sql – Crear un índice en FechaVenta CREATE INDEX IDX_Ventas_FechaVenta ON Ventas(FechaVenta);

– Crear un índice en Cliente CREATE INDEX IDX_Ventas_Cliente ON Ventas(Cliente);

– Crear un índice en Precio CREATE INDEX IDX_Ventas_Precio ON Ventas(Precio);

2. Reestructuración de la consulta:
Optimizar la consulta aprovechando índices y evitando subconsultas o funciones que ralenticen la ejecución. Si tu consulta contiene subconsultas innecesarias, reescribirla para simplificarla.
```sql
-- Consulta optimizada
SELECT VentaID, Cliente, NumeroPedido, Precio, FechaVenta
FROM Ventas
WHERE FechaVenta BETWEEN '2023-01-01' AND '2023-12-31'  -- Evitar el uso de funciones
  AND Cliente LIKE 'Cliente50%'  -- Búsqueda optimizada usando el índice en Cliente
ORDER BY Precio DESC;            -- Ordenación optimizada gracias al índice en Precio
  1. Utilización de Hints: En algunos casos, puedes usar hints para optimizar aún más. Por ejemplo, si sabes que los índices deberían estar siendo usados, puedes forzarlos con INDEX o FORCESEEK.

Reevaluación del rendimiento:

Ejecutar nuevamente la consulta optimizada y comparar los tiempos de ejecución y uso de recursos con los de la consulta original.
Revisar nuevamente el plan de ejecución para verificar si ahora se utilizan los índices creados, y si los escaneos completos han sido reemplazados por búsquedas eficientes (Index Seek).

Análisis y recomendaciones:

Discutir cómo los cambios realizados mejoraron el rendimiento. ¿Hubo una reducción significativa en el tiempo de ejecución? ¿Se optimizó el uso de memoria y CPU?
Mencionar otras estrategias que podrían aplicarse en diferentes situaciones, como la partición de tablas, materialización de vistas o uso de columnas calculadas.
Realizar diferentes consultas que sean ineficientes sin uso de índices y luego realizar la creación de índices para verificar los rendimientos. A su vez, realizar un escrito sobre que significa los parallelism, sort, clustered index scan, entre otros que pueden aparecer en el execution plan.

Resultado esperado

Al finalizar la práctica, los estudiantes deben ser capaces de