Grimpi IT Blog

enero 25, 2011

Lo nuevo de SQL Server 2011: COLUMN STORAGE INDEX

Filed under: Engine, SQL Server, SQL Server 2011 — grimpi @ 4:28 pm

Una de las características más importantes que promete SQL Server 2011, son los índices por columna.

Introducción
A partir del 2007, salieron a la luz distintas bases de datos llamadas “Column-oriented DBMS”, que implementaron el concepto de índice por columna, el cual prometía revolucionar el mercado, especialmente el de datawharehousing. Uno de los motores mas conocidos es Vertica (y su versión gratis, C-Store), también MonetDB, que es open source. Incluso Sybase lanzó un producto llamado Sybase IQ. Por supuesto, hubo otras empresas que lanzaron productos similares.
El problema de estos productos es que son “Column-oriented DBMS” exclusivamente. O sea, no almacenan la información de la misma manera que la almacena una típica base de datos relacional tradicional como Oracle, MySql o SQL Server. Esto significa que el mercado de estos productos está muy limitado a escenarios específicos.

Por el contrario, SQL Server 2011 promete ser la primera de bases de datos relacional “tradicional” en incorporar como una feature mas a su motor, el índice por columna, pudiendo ser combinado su uso con la tradicional estructura “row-store”.
 

¿Ahora bien, que es un índice por columna?
En todos los motores relacionales, los índices se almacenan por filas (rows). Esto significa que la estructura del índice en su última instancia, va a tener un puntero a un registro de una tabla. Existen distintas maneras de construir un índice (bitmap, b-tree, etc), pero todos estos métodos en definitiva hacen lo mismo: acelerar el acceso a un registro.
El índice por columna por el contrario, invierte la filosofía. Ya no busca registros, busca columnas. En una tabla “normal”, los registros son almacenados en páginas físicas de disco. Cada página contiene una determinada cantidad de registros (que varia según el motor y los registros. En SQL Server cada página de datos tiene un tamaño fijo de 8K). Un índice por columna, es al revés. En cada página de datos, se guardan columnas, no filas.

¿Bárbaro, ahora que ventajas tiene esto?
La respuesta es simple. Performance. Y por 2 razones.

1) Al estar las páginas de disco ordenadas por columnas, cuando hago una consulta, el motor puede leer solo las columnas que necesitamos para nuestra query, lo cual evita lecturas de disco innecesarias.
2) Es muchísimo mas probable que exista redundancia de información entre los valores de una misma columna en distintos registros, que entre los valores de las distintas columnas en un mismo registro, por lo tanto, es mucho mas fácil y eficiente la compresión de datos, lo que significaría mas información en memoria y menos acceso al disco. Ejemplo:
Si yo tengo 4 registros de 4 columnas cada uno:

  Col1 Col2 Col3 Col4
Row 1 01 Jose 01/01/2000 4
Row 2 02 Eduardo 02/02/2004 5
Row 3 03 Ramon 03/02/2004 5
Row 4 04 Susana 02/02/2004 5

Si yo comprimo esta información por columna, mi grado de compresión seria mucho mayor a que si yo comprimo por registro. Porque el grado de homogenización de valores dentro de una columna es mucho mayor. En cambio, en un registro, como tengo distintos tipos de datos y valores posibles entre ellos muy disímiles, la compresión no es tan efectiva. No es lo mismo comprimir 100 valores de un mismo tipo de datos, que comprimir 100 valores de distintos tipos de datos.
Por otro lado, existen diferentes técnicas de compresión que funcionan mejor con un tipo de datos que con otro (ejemplo: hay métodos que son más eficientes para números o binarios, y otros métodos que son más eficientes para texto). Por lo tanto, el motor elige una forma de compresión diferente dependiendo del tipo de dato de la columna, algo que es imposible de hacer en una compresión por row, lo que incrementa aun más el nivel de compresión.

Un gran ratio de compresión me permite tener más información en el cache de datos, incrementar fuertemente los aciertos del buffer y evitar el acceso a I/O, que es la operación más costosa en una base de datos.
En las pruebas hechas por los laboratorios de Microsoft sobre datos reales y haciendo la comparativa con SQL Server 2008 R2, en algunos casos se obtuvo un incremento de hasta 60 veces la performance de una consulta. Lo cual resulta un número bastante impresionante.

¿Como se usa un índice por columna?
Si bien todavía la documentación oficial en SQL Server 2011 no está disponible porque el producto no fue liberado, si podemos ver la sintaxis si entramos a la web del sitio.
Para crear un índice por columna simplemente deberemos anteponer la palabra clave COLUMNSTORE.
Ejemplo:

 
CREATE COLUMNSTORE INDEX NuevoIndice ON Personas
(
FechaDeVenta,
FechaDeEntrega,
Producto,
Precio,
Cantidad
)
 

¿Siempre va a convenir entonces usar este tipo de índices?
La respuesta es un rotundo no. Los índices por columna es un índice más que incrementa notablemente la performance en bases de datos muy grandes y en determinado tipo de consultas. Pero son índices pensados para escenarios de datawharehousing, especialmente para consultas del tipo star join. No en todos los casos es mas óptimo este tipo de índice ni tampoco están pensados para las típicas bases de datos transaccionales OLTP. Para una query normal, sigue siendo mas útil usar los índices b-tree clásicos. El motor de SQL Server en función de sus estadísticas, sabrá elegir cuando es más conveniente usar un índice por columna o un índice común.
Por otra parte, este tipo de índices en SQL Server tienen por el momento varias restricciones. No todos los tipos de datos son soportados y más importante aún, no se pueden hacer operaciones de INSERT, UPDATE, DELETE o MERGE sobre tablas con este tipo de índices. La manera de llenar una tabla entonces es deshabilitar el índice temporalmente, llenar la tabla y volver a habilitar el índice. Otra opción es crear una vista indexada con columnstore index sobre la tabla y dejar a esta tabla sin este tipo de índice.
Por ultimo, otro dato a tener en cuenta, que la reconstrucción de un column index lleva entre 2 y 3 veces mas tiempo, que un índice b-tree clásico.

Conclusión:
Lamentablemente la primera CTP de SQL Server 2011 que lanzó Microsoft todavía no incluye esta feature, por lo tanto, no la podemos probar y verificar si el aumento de su rendimiento es tan significativo como ellos dicen. Sin embargo, el índice por columna es una característica muy distintiva y que hasta el momento, ni Oracle ni DB2 pudieron incorporar nativamente en su producto relacional.
Seguramente con el correr de los tiempos y a medida que tengamos mas información, van a aparecer en blogs y en artículos, distintas comparaciones y escenarios en donde conviene utilizar índices por columnas o el clásico índice por filas.

Links interesantes:
ColumnStores vs. RowStores: How Different Are They Really?
Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0

enero 18, 2011

Lo nuevo en SQL Server 2011

Filed under: SQL Server, SQL Server 2011, T-SQL — grimpi @ 2:02 pm

Estuve investigando SQL Server 2011, llamada “Denali”. Actualmente está en estado beta y se puede bajar la CTP desde acá. Antes de bajarla, para poder instalar esta CTP se requiere como minimo Windows Vista SP2. No funciona ni en Windows XP ni en Windows 2003. De todas maneras parece bastante prometedora.
Que tiene de nuevo esta nueva versión? Obviamente muchas, pero hay 2 cosas que realmente me gustaron:

1) Objetos sequences: Quienes trabajen con Oracle o PostgreSQL, sabrán perfectamente que son. Para el que no lo sabe, un sequence es un objeto cuyo valor se autoincrementa cada vez que es consultado.
Es un excelente reemplazo de los identities, ya que es un objeto externo a la tabla.
Dentro de poco veremos las flamewars en blogs argumentando si es mejor o peor usar sequences o identities como campo PK de una tabla.
Sin embargo, la incorporación de este objeto facilitara enormemente la migración de Oracle y PostgreSQL a SQL Server.
Ejemplo:

CREATE SEQUENCE PrimerSequence
START WITH 1
INCREMENT BY 1;
GO

SELECT (NEXT VALUE FOR PrimerSequence)
GO

INSERT INTO Tabla (Id, Nombre)
SELECT NEXT VALUE FOR PrimerSequence, ‘Esteban’
GO

Link:
http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx

La única contra que le veo, es la forma en que SQL Server implementó la lectura de los sequences. Escribir NEXT VALUE FOR xxxx es demasiado texto para simplemente para leer un sequence.
En Oracle o en PostgreSQL resulta más legible y compacto hacer lo mismo.

2) Paginación: Ya no hay que inventar subqueries y cosas raras para paginar datos. Con SQL Server 2011 va a ser posible paginar nativamente una query declarando un OFFSET en la cláusula ORDER BY.
Habrá que investigar la performance de esto, pero parece prometedor.
Ejemplo:

SELECT Id, Campo1, Campo2 FROM Tabla
ORDER BY
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY
GO

Maravilloso. Lo único que no entiendo es porque decidieron poner en el ORDER BY la paginación y no en la instrucción TOP, que pareciera ser lo mas lógico. Después de todo se supone que el ORDER BY solo se limita a ordenar datos y no a limitar la cantidad de registros a devolver.
Además lo hicieron demasiado trabajoso. Demasiadas palabras claves para algo tan simple. Hay que escribir OFFSET, ROWS, FETCH, NEXT, ONLY, ROWS nuevamente solamente para limitar un resultado. MySql lo resuelve de manera mucho mas elegante y practica con su cláusula LIMIT (0,10). Simple, claro y elegante.

Link:
http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx

ACTUALIZACION: Me acabo de enterar que la manera en que SQL Server implementó la paginacion corresponde a la especificación ANSI SQL:2008.

Hay otras cosas interesantes en SQL Server 2011.  Se agregó un nuevo tipo de indice, llamado “columnstore”, pensado especialmente para escenarios de datawharehousing. Se mejoró notablemente el Full Text Search, la IDE cambió, se agregaron snippets, varias mejoras en BI y SSIS.
Seguramente con el correr de los meses, se irán incorporando nuevas características. Pero ya el hecho de tener paginación nativa y sequences, representa un gran avance para los desarrolladores y en mi opinion, el nuevo indice por columnas en algunos escenarios, va a ser imprescindible. Pero esto lo voy a explicar en otro post.

Crea un blog o un sitio web gratuitos con WordPress.com.