Ejemplos de desnormalización para mejorar el rendimiento

 Por Miguel Egea.
Microsoft SQL-SERVER MVP

Introducción

No hay casi nada que produzca más dolores de cabeza que una base de datos desnormalizada, generalmente es garantía de problemas, y además de los que los usuarios no pueden entender. Si la información está en más de un sitio, es una realidad que por errores en nuestro código, por manipulaciones de los datos fuera de nuestras aplicaciones y por otro sin fin de circunstancias acabamos enfadados con los esquemas que no cumplen la 3FN al menos.

Entonces .... ¿Desnormalizamos?

Sin embargo hay veces que no queda más remedio que desnormalizar, de hecho, es una de las fórmulas más eficaces para aumentar el rendimiento. No me malinterpretéis, las bases de datos hay que diseñarlas, pensarlas y desarrollarlas en 3FN al menos. Sin embargo, una vez terminado el proceso de diseño, pueden establecerse estrategias de desnormalización que eviten hacer uniones de demasiadas tablas solamente para obtener un dato que necesitamos.

Pongamos un ejemplo: Si implementamos una aplicación de almacén, para obtener las ventas en un periodo de unos artículos, normalmente necesitaremos usar dos tablas. Considerando el ejemplo de la figura que tenéis al margen, para obtener esa información necesitaremos ejecutar una instrucción como la siguiente :

SELECT month(Fecha) Mes,idArticulo,sum(unidades) 
FROM Albaran a inner join [lineas Albaran] la on
a.idAlbaran=la.idalbaran
group by month(fecha),idarticulo

Esta instrucción supondrá SIEMPRE recorrerse ambas tablas todos sus registros, incluso si establecemos una cláusula WHERE del tipo Month(Fecha)=2, para buscar las del mes de febrero.

En estas circunstancias, si esperamos por ejemplo centenares de miles de albaranes y millones de líneas una estrategia de desnormalización que guarde en la tabla de líneas el mes de la cabecera será una estrategia más que adecuada, por varias razones. La primera de ellas, evidentemente es que si tenemos el dato que buscamos en una sola tabla no necesito unir dos tablas, por tanto tendré que filtrar en una solamente. Además de esa circunstancia hay otra, al grabar exactamente el dato que necesito (el mes en este caso) si realizo comparaciones con el dato o creo índices estos sí podrán ser usados y por tanto podremos obtener ventajas adicionales en cuanto al rendimiento.

Veamos las rutinas necesarias para implementar esta desnormalización, lo primero vamos a considerar este script, para crear las tablas tal y como vimos.

if not objectproperty(object_id('Lineas Albaran'),'IsTable') is null
    drop table [Lineas albaran]
go
if not objectproperty(object_id('Albaran'),'IsTable') is null
    drop table [albaran]
go
Create table [albaran] (idAlbaran int identity(1,1) primary key,
                        serie char(4) not null,
                        Numero int not null,
                        Fecha datetime not null default getdate(),
                        idcliente int)
go
create table [Lineas Albaran] (idAlbaran int not null,
                        idOrden int not null,
                        idArticulo int not null,
                        unidades int not null,
                        fecha datetime ,
                        constraint pk_lineas primary key (idalbaran,idorden),
                        constraint fk_linea_cab foreign key (idAlbaran) references Albaran(idAlbaran) )
go
  

Con este esquema tenemos el hueco para grabar la fecha, pero no tenemos garantía de que esa fecha sea la fecha correcta, por tanto, tendremos que asegurar esta circunstancia de alguna forma, por ejemplo mediante un trigger como el que sigue a continuación.

create trigger trg_LineasAlbaran on [lineas Albaran] instead of insert
as
    begin
    insert into [lineas albaran] 
                select i.idalbaran,i.idorden,i.idarticulo,i.unidades,a.fecha
                            from inserted i inner join albaran a on i.idalbaran=a.idalbaran
end

Con este trigger garantizamos que todas las lineas que se inserten van a tener la fecha que tiene el albaran, pero no tenemos garantizado que si la cabecera cambia, este cambio se refleje en nuestra línea. Para solucionar este inconveniente crearemos un trigger de actualización en la tabla de cabecera. El código podría ser el siguiente :

create trigger trg_Albaran_ActualizaFecha on albaran for update
    as
        begin
            if update(Fecha)
                    begin
                    update l set l.fecha=i.fecha 
                                    from inserted i inner join 
                                    [lineas Albaran] l 
                                    on i.idalbaran=l.idalbaran
                    end        
       end

El trigger solo actuará cuando se actualice la fecha, evitando así dispararse innecesariamente ante otras actualizaciones

Hemos preparado un pequeño script también para introducir datos y poder hacer las pruebas pertinentes.

Declare @contador int
Declare @Contador2 int
Declare @FechaInicial datetime
set @contador=0
set @fechainicial =getdate()
while @contador<2000
begin
-- Insertamos una cabecera y nlineas (hasta 10 máximo
insert into albaran (serie,numero,fecha,idcliente)
values ('A',@contador,GETDATE()-365*rand(),1)
set @contador=@contador+1
set @contador2=10*rand()
while @contador2>0 
begin
insert into [lineas albaran] (idAlbaran,idOrden,idArticulo,unidades) 
values (@contador,@contador2,15*rand(),100*rand())
set @contador2=@contador2-1
end
end
select datediff(ms,@fechainicial,getdate())/1000. [Segundos transcurridos]
go
set statistics io off
go

Con este script acabamos de insertar 2000 líneas, podéis comprobar si os apetece a quitar el trigger y ponerlo y ver la diferencia de rendimiento que debemos esperar, no será demasiado grande.

Después podéis probar el trigger sobre la cabecera lanzando esta instrucción

update albaran set fecha=dateadd(day,-360,fecha)

Consideremos ahora estas instrucciones :

create index ix_LineasAlbaran_fecha on [lineas albaran] (fecha)
set statistics io on 
select * from [lineas albaran] where fecha between '20020420' and '20020421'
select * from [lineas albaran] with(index(ix_lineasalbaran_fecha)) where fecha between '20020420' and '20020421'

 

Podríamos pensar que el resultado en cuanto a tiempos y ejecución serán el mismo al fin y al cabo estamos buscando por un campo de tipo fecha sobre el que existe un índice y no estamos aplicando ninguna función sobre el argumento de búsqueda, parece claro que la estrategia óptima sería usar el índice, ¿verdad?...... La verdad es que no siempre es así, esa estrategia sería con seguridad la que elegiría el optimizador de consultas si no hubiesemos escrito 'Select * from', es decir, si que puede encontrar los registros que son muy rápidamente, pero una vez obtenidos los identificadores, tiene que ir al índice clustered a buscar esos datos, y tiene que ir una vez para cada registro, por eso el resultado del plan de ejecución de la instrucción a y b es el que podéis ver a continuación.

Si nos fijamos la primera instrucción ha hecho un barrido de toda la tabla a través de su índice clustered (clustered index scan', sin embargo esa acción le ha hecho realizar 43 lecturas lógicas (en el caso nuestro y con los datos que se han generado en mi máquina). Sin embargo, la segunda instrucción, al verse forzada a usar el índice ha tenido que leer un total de 134 páginas. Es decir, el resultado es peor con el índice que sin él. en nuestro caso estaba devolviendo 120 filas de un total 9034, es decir un poco más del 10% de las filas ya perjudica sustancialmente el rendimiento usar el índice que no hacerlo.Estas afirmaciones no son extrapolables sin embargo a los índices clustered

Alguien podría pensar que dada esta explicación, puede concluirse que la desnormalización en este caso no ofrece tantas ventajas y que quizá debiese plantearse mejor. Creo sinceramente que no es así, el ejemplo que acabamos de ver, si bien es una práctica habitual de muchos desarrolladores, no es ni mucho menos el objetivo de esta desnormalización (que es obtener ventas por mes de la forma más ágil posible).

Obteniendo ventas mensuales

Lo que hemos obtenido con esta desnormalización es la posibilidad de evitar consultar la tabla de cabecera para obtener la fecha. Sin embargo, las preguntas habituales serán 'Cuantas unidades del artículo X hemos vendido en Mayo'. Veamos las instrucciones que responden a esta pregunta.

select idArticulo,count(*) Cuantas
from [lineas albaran]
where fecha between '20030501' and '20030601'
group by idArticulo

y el plan de ejecución que obtendremos será :

Este plan se está recorriendo la tabla de albaranes, agrupando por la cláusula que le hemos indicado y contando los registros para devolverlos.

Hemos necesitado 43 lecturas lógicas para obtener el resultado.

Sin embargo esta consulta no parece muy óptima, parece manifiestamente mejorable su rendimiento, si tenemos esta sospecha, lo mejor que podemos hacer es usar el 'Asistente para la optimización de índices' o 'Index tunning wizard' en la versión inglesa.

Este es el resultado que nos propone el asistente :

/* Created by: Index Tuning Wizard */
/* Fecha: 13/09/2003 */
/* Hora: 18:20:18 */
/* Nombre de servidor: PORTATIL */
/* Nombre de la base de datos: ACUMULADOS */
USE [ACUMULADOS] 
go
SET QUOTED_IDENTIFIER ON 
SET ARITHABORT ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_NULLS ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
SET NUMERIC_ROUNDABORT OFF 
go
DECLARE @bErrors as bit
BEGIN TRANSACTION
SET @bErrors = 0
CREATE NONCLUSTERED INDEX [Lineas Albaran3] ON [dbo].[Lineas Albaran] 
        ([fecha] ASC, [idArticulo] ASC )
IF( @@error <> 0 ) SET @bErrors = 1
IF( @bErrors = 0 )
    COMMIT TRANSACTION
ELSE
    ROLLBACK TRANSACTION

Si lo lanzamos y lo ejecutamos y comprobamos el plan de ejecución y el número de lecturas lógicas será:

En este caso hace un seek en el índice recien creado, el que sugirio el asistente y el número de lecturas lógicas es de 4, obteniendo una mejora casi del 75%.

En resumen,

La mezcla de técnicas, como la desnormalización y el uso de los recursos que nos proporciona SqlServer, unidos pueden hacer que el rendimiento de nuestras bases de datos mejore sustancialmente. Si a esto le añadimos un buen control del crecimiento y uso, y unas buenas prácticas de programación tenemos el 99% del éxito asegurado.

---

Miguel Egea Gómez es Ingeniero Técnico en Informática y trabaja como Consultor en Sinergia Tecnológica dentro del Grupo I.T. Deusto, es MVP en SQL-Server mantiene el site http://www.portalsql.com y colabora ocasionalmente en Encuentros Técnicos y charlas organizados por Microsoft.