Cómo cargar Tablas Particionadas con SELECT INTO en SQL Server: ALTER TABLE SWITCH PARTITION
|
|
Este capítulo muestra como técnica de Carga Rápida en un Data Warehouse (u otros entornos, aunque para cargas incrementales de grandes tablas de hecho en un Data Warehouse resulta de especial interés por sus volúmenes de datos y la probabilidad de utilización del Modo de Recuperación Simple) la utilización de SELECT INTO para cargar Tablas Particionadas, mediante la creación de una tabla intermedia con SELECT INTO en una base de datos en Modo de Recuperación Simple, que asignaremos seguidamente como una Partición de la Tabla Particionada de destino a través de la sentencia ALTER TABLE SWITCH. Se explica el escenario, y se incluye código de ejemplo (CREATE PARTITION FUNCTION, CREATE PARTITION SCHEME, etc.). |
Una interesante técnica de Carga Rápida en entornos de Data Warehouse, consiste en la utilización de Tablas Particionadas como medio para la realización de Cargas Incrementales Rápidas con SELECT INTO en bases de datos configuradas con Modo de Recuperación Simple, especialmente útil para realizar cargas incrementales en grandes Tablas de Hechos (Fact Table) de un Data Warehouse en SQL Server (recordemos que con SELECT INTO la tabla de destino NO debe existir, por lo que a priori, dificil hacer una carga incremental sobre una tabla existente, verdad?). Además, el Particionamiento de Tablas e Índices en SQL Server tiene muchos detalles que deberemos tener en cuenta. Este escenario particular, se basa en las siguientes premisas:
- Utilizar el Modo de Recuperación Simple en la base de datos de destino, para aprovecharse de las mejoras de rendimiento de las Operaciones de Registro Mínimo, como es el caso de SELECT INTO.
- Utilizar una Tabla Particionada como tabla destino. En este caso, el diseño de las Particiones se debe basar en los conjuntos de filas que se desean cargar, es decir, si deseamos realizar una carga incremental cada mes podemos definir una partición para cada mes, como se mostrará más adelante en el ejemplo incluido en este capítulo. Así, utilizaremos la sentencia ALTER TABLE SWITCH para conseguir el efecto de carga de la partición deseada (como veremos más adelante en el ejemplo incluido), motivo por el cual, deben de existir las Particiones deseadas antes de la Carga, y además, dichas particiones deben estar vacías para poder ser destino de una sentencia ALTER TABLE SWITCH.
- Generar una tabla temporal con SELECT INTO, con exactamente la misma estructura que la tabla destino. Aquí el truco está en dos sitios: por un lado, generar dicha tabla temporal con los datos que se desean cargar en la partición de la tabla destino, mediante una operación SELECT INTO en una base de datos en Modo de Recuperación Simple, ofrece un rendimiento extremo al minimizar las escrituras en Log de forma más que sensible (esto ya lo vimos antes en este artículo, pero volveremos a recordarlo ahora después ;-). Por otro lado, es requisito que dicha tabla temporal tenga exactamente la misma estructura que la tabla de destino (es decir, campos, tipos de datos, admisión de nulos, intercalación, precisión, etc.) para poder ejecutar la sentencia ALTER TABLE SWITCH. También es requisito, que la tabla temporal de origen tenga un CHECK CONSTRAINT que garantice que su contenido corresponda a la partición de la tabla particionada de destino sobre la que se desea cargar / intercambiar a través de ALTER TABLE SWITCH.
Para entenderlo bien, no hay nada como utilizar un ejemplo. En el siguiente trozo de código, se crea una Función de Particionamiento (CREATE PARTITION FUNCTION), se crea un Esquema de Particionamiento (CREATE PARTITION SCHEMA), y se crea una Tabla Particionada. Aquí, el truco que hemos utilizado es crear de antemano (en la Función de Particionamiento) todas las particiones deseadas, tanto las que inicialmente contendrán datos, como las que contendrán datos en un futuro (es decir, inicialmente estarán vacías para poder ejecutar ALTER TABLE SWITCH con éxito). Este razonamiento podría generalizarse, de tal modo que en la Función de Particionamiento podríamos generar de antemano todas las particiones deseadas de forma exagerada (ej: hasta Diciembre de 2025). En el presente ejemplo, se utilizarán 7 Particiones, de las cuales, sólo insertaremos datos en las 3 primeras Particiones. Así mismo, el Esquema de Particionamiento utilizado, asociará todas las Particiones al mismo Grupo de Ficheros (Filegroup), lo cual resulta más que suficiente para el objetivo que deseamos cumplir.
-- Me posiciono en la base de datos de ejemplo (GuilleSQL)
-- Esta base de datos, tiene 500MB de Datos y 100MB de Log
USE GuilleSQL
GO
-- Crear la Función de Particionamiento.
-- Particion 1: < 200801
-- Particion 2: >= 200801 AND < 200802
-- Particion 3: >= 200802 AND < 200803
-- Particion 4: >= 200803 AND < 200804
-- Particion 5: >= 200804 AND < 200805
-- Particion 6: >= 200805 AND < 200806
-- Particion 7: >= 200806
CREATE PARTITION FUNCTION fn_ParticionadoPorMes(int)
AS RANGE RIGHT FOR VALUES(200801, 200802, 200803, 200804, 200805, 200806)
GO
-- Crear el Esquema de Particionamiento.
-- Me resulta suficiente con un único FileGroup, para mi objetivo
CREATE PARTITION SCHEME sch_ParticionadoPorMes
AS PARTITION fn_ParticionadoPorMes
ALL TO ( [PRIMARY] );
GO
-- Crear Tabla Particionada
CREATE TABLE GuilleSQL.dbo.tbl_ParticionadaPorMes
(
Nombre varchar(200) NOT NULL
,MesParticion int NOT NULL
) ON sch_ParticionadoPorMes(MesParticion)
GO
-- Rellenar las primeras cuatro particiones de la tabla
-- Las tres últimas particiones se dejan vacías
INSERT INTO GuilleSQL.dbo.tbl_ParticionadaPorMes (Nombre, MesParticion)
SELECT TOP 100 FirstName, 200712 FROM AdventureWorks.Person.Contact
INSERT INTO GuilleSQL.dbo.tbl_ParticionadaPorMes (Nombre, MesParticion)
SELECT TOP 100 FirstName, 200801 FROM AdventureWorks.Person.Contact
INSERT INTO GuilleSQL.dbo.tbl_ParticionadaPorMes (Nombre, MesParticion)
SELECT TOP 100 FirstName, 200802 FROM AdventureWorks.Person.Contact
INSERT INTO tbl_ParticionadaPorMes (Nombre, MesParticion)
SELECT TOP 100 FirstName, 200803 FROM AdventureWorks.Person.Contact
GO
|
Con esto, hemos construido nuestro punto de partida, es decir, la tabla particionada de destino sobre la cual deseamos realizar una carga, incluyendo como particiones vacías aquellas sobre las cuales se desea cargar datos. Para quién desee poder repertir este ejemplo, he utilizado la base de datos de Adventure Works (la que viene con SQL Server 2005) para generar los datos de esta prueba. También quería comentar, que para esta prueba, he utilizado una base de datos con 500MB de Datos y 100MB de Log, configurada en Modo de Recuperación Simple.
Explicado todo esto, vamos a realizar la prueba que realmente nos interesa. En el siguiente trozo de código Transact-SQL, se crea una tabla temporal con exactamente la misma estructura que la tabla destino y diez millones de filas. Para conseguirlo, he necesitado realizar un casting (con la función CAST) que garantice el tipo de dato y precisión de destino, así como utilizar la función ISNULL para garantizar que el campo se crea como NOT NULL, y utilizar la palabra reservada COLLATE para garantizar la misma intercalación que en la tabla destino. Por supuesto, como en la base de datos de Adventure Works no existe ninguna tabla con 10.000.000 de filas, he realizado un CROSS JOIN de una tabla con sí misma, para así conseguir el número de filas deseado (utilizando la expresión TOP). Seguidamente, he creado un CHECK CONSTRAINT que garantiza que el contenido de la tabla temporal creada con SELECT INTO, es el correspondiente al de la partición de destino que deseamos cargar. Finalmente, cargamos la tabla de destino ejecutando una sentencia ALTER TABLE SWITCH sobre la Partición deseada de la tabla (dicha partición debe estar vacía). A continuación se puede observar el código del ejemplo:
-- Crear Tabla Temporal de 10.000.000 de filas con SELECT INTO
-- Tiempo de Ejecución: 8 seg., y no crece el fichero de Log
SELECT TOP 10000000
ISNULL(CAST(C.FirstName AS VARCHAR(200)), 'Desconocido') COLLATE Modern_Spanish_CI_AS AS Nombre,
200804 AS MesParticion
INTO GuilleSQL.dbo.tbl_CargaRapida
FROM AdventureWorks.Person.Contact C, AdventureWorks.Person.Contact
-- Crear un Check Constraint en la Tabla Temporal para garantizar el dominio de los datos
-- es decir, garantizar que los datos de la tabla corresponden a la Partición en que se desea asignar.
-- Tiempo de Ejecución: 3 seg.
ALTER TABLE GuilleSQL.dbo.tbl_CargaRapida ADD CONSTRAINT CHK_CargaRapida CHECK ( MesParticion = 200804)
-- Asignar la Tabla Temporal como una de las Particiones vacías de la tabla destion: 0 seg.
ALTER TABLE GuilleSQL.dbo.tbl_CargaRapida
SWITCH TO GuilleSQL.dbo.tbl_ParticionadaPorMes PARTITION 5
|
Llegados a este punto, lo primero que quiero es decir que esto funciona, y seguidamente, quiero advertir que el tiempo empleado ha sido un total de 11 segundos (8 segundos del SELECT INTO de 10.000.000 de filas, 3 segundos de ALTER TABLE ADD CONSTRAINT, y 0 segundos en el intercambio de particiones con ALTER TABLE SWITCH). También, quiero comentar que el fichero de Log no ha crecido, manteniendo su tamaño inicial de 100MB.
Claro, que nunca se sabe lo que se tiene hasta que se pierde... jeje ;-) Por supuesto, he probado a realizar un INSERT INTO en lugar de SELECT INTO, como se muestra en el siguiente ejemplo, y se puede encontrar una diferencia abismal: tiempo de ejecución de 2 minutos con 26 segundos, que ha sido motivado a la enorme cantidad de escritura en Log, pues el fichero de Log ha aumentado hasta 4,5GB. Pues tenemos dos problemas: uno de rendimiento, y otro de almacenamiento !!
TRUNCATE TABLE GuilleSQL.dbo.tbl_CargaRapida
GO
INSERT INTO GuilleSQL.dbo.tbl_CargaRapida (Nombre, MesParticion)
SELECT TOP 10000000
ISNULL(CAST(C.FirstName AS VARCHAR(200)), 'Desconocido') COLLATE Modern_Spanish_CI_AS AS Nombre,
200804 AS MesParticion
FROM AdventureWorks.Person.Contact C, AdventureWorks.Person.Contact
GO
|
Creo que con todo esto, queda bastante evidenciada las mejoras de rendimiento y almacenamiento propias de la utilización de SELECT INTO frente a INSERT INTO, y en este caso, se evidencia también la ventaja de utilizar Tablas Particionadas y la sentencia ALTER TABLE SWITCH para realizar Cargas de Alto Rendimiento, como es el caso típico de las Tablas de Hechos (Fact Tables) de un Data Warehouse (DW) en SQL Server.
Aprovecho para recordar, que aunque este ejemplo ha sido con 10 millones de filas, bien es cierto que se trataba de filas con tan sólo dos campos. En entornos reales de producción, encontraríamos el mismo problema de rendimiento y almacenamiento con muchas menos filas. En consecuencia, en entornos reales de Producción con tablas que almacenan grandes volúmenes de datos, y estructuras de tabla más complejas (ej: con índices, etc.), las mejoras de rendimiento y almacenamiento pueden ser más que notables. Además, recordar que en entornos de producción existe concurrencia, es decir, que no estamos solos... y la máquina se tiene que repartir entre todas las cosas que la pidamos. Al final, aunque los entornos SQL Server empresariales, suelen utilizar Redes de Almacenamiento SAN por Fiber Channel, de Alto Rendimiento (y Alta Disponibilidad), de poco nos va a servir sin un diseño y desarrollo de base de datos optimizado.
Por supuesto, no siempre podremos aprovecharnos de SELECT INTO y ALTER TABLE SWITCH. Por ejemplo, si estamos realizando una carga de un fichero de Log (ej: el Log de un ISA Server), podemos realizar una carga incremental con SELECT INTO y ALTER TABLE SWITCH. Sin embargo, si estamos cargando siniestros de pólizas en una empresa de seguros, a lo mejor no podemos aprovecharnos de SELECT INTO y ALTER TABLE SWITCH, debido a que en muchos casos se insertan datos con caracter retroactivo, lo que implica que no podremos cargar sólo el mes vencido, ya que habrá modificaciones sobre multitud de las particiones anteriores, por lo que la cosa de complica (en un Log, dificil que nos inserten datos con efecto retroactivo, verdad? ;-).
Y hasta aquí, hemos llegado con este capítulo de SELECT INTO, Tablas Particionadas, y sentencias ALTER TABLE SWITCH en SQL Server. |
|
|
|
