Optimiza Con T-SQL 2 Y Cláusula OVER

T-SQL Server 2012 supone un gran salto para los desarrolladores que quieren ir un paso mas allá en la resolución de problemas con conjuntos de datos. En esta edición de SQL Server, existe un antes y un después a la hora de trabajar con los conjuntos de datos mediante T-SQL 2012, y como puede correrse el riesgo de seguir utilizando T-SQL como “hasta ahora”, me he propuesto escribir este post para conseguir transmitir al lector, las importantes mejoras que aparecen y que pueden parecer insípidas si no se enfatizan, en el operador OVER.

[box type=”info”] NOTA: Artículo realizado sobre versión preliminar de SQL Server 2012. [/box]

Para entender mejor y darle todo el valor que merece la que para mi puede ser una de las mas importantes mejoras en en T-SQL 2012, creo conveniente recordar un aspecto clave en todo motor relacional: “Trabaja con conjuntos de datos”.

Bien, pero ¿qué implica esa afirmación?

Bueno, tal como lo veo esa afirmación nos lleva a que siempre debemos plantear nuestras soluciones a problemas, utilizando conjuntos. Eso es obvio…¿o no?.

Pensemos en un problema clásico como puede ser el cálculo de valores acumulados. Este problema, es ampliamente conocido en el mundo de la banca y todo aquel software que tenga que lidiar con transacciones, albaranes, pedidos,…en definitiva cualquiera que deba realizar un análisis de como ha ido el flujo de caja.

Por poner un ejemplo, quisiéramos conseguir ver un listado como el siguiente:

Cliente	Transaccion	ValorOperacion	Balance
1	1	5	5
1	2	-3	2
1	3	12	14
2	1	6	6
2	2	11	17
…	…	…	…

Generalmente además, solemos almacenar la información de forma que no se elimine nada. Es decir, que disponemos de una tabla que incluye las columnas “cliente”, “Transaccion” y “ValorOperacion”. De esta forma, si un cliente inserta dinero, el valor de operación aparece en positivo, pero si se gasta dinero de la cuenta, el valor de la operación aparece en negativo. Si nos fijamos bien, se trata de un problema clásico que aparece en multitud de escenarios de negocio.

Aquí tenéis el código para crear la tabla de prerrequisitos con la que probar las consultas:

Pre-requisitos

— GetNums function
IF OBJECT_ID(‘dbo.GetNums’) IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
    L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
    L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
    L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
    L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
    L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
  SELECT TOP(@n) n FROM Nums ORDER BY n;
GO
— IngresosCuenta table
IF OBJECT_ID(‘dbo.IngresosCuenta’, ‘U’) IS NOT NULL DROP TABLE dbo.IngresosCuenta;
CREATE TABLE dbo.IngresosCuenta
(
  id_cliente  INT   NOT NULL,
  transaccion INT   NOT NULL,
  ValorOperacion    MONEY NOT NULL
    DEFAULT (1 + ABS(CHECKSUM(NEWID())%5))
);
GO
— Code to Populate IngresosCuenta with Sample Data (change inputs as needed)
DECLARE
  @num_partitions     AS INT = 1,
  @rows_per_partition AS INT = 10000;
TRUNCATE TABLE dbo.IngresosCuenta;
INSERT INTO dbo.IngresosCuenta WITH (TABLOCK) (id_cliente, transaccion)
  SELECT NP.n, RPP.n
  FROM dbo.GetNums(@num_partitions) AS NP
    CROSS JOIN dbo.GetNums(@rows_per_partition) AS RPP;
GO
IF OBJECT_ID(‘dbo.Huecos’, ‘U’) IS NOT NULL DROP TABLE dbo.Huecos;
GO
CREATE TABLE dbo.Huecos
(
  col1 INT NOT NULL
    CONSTRAINT PK_Huecos PRIMARY KEY
);
INSERT INTO dbo.Huecos(col1) VALUES(2);
INSERT INTO dbo.Huecos(col1) VALUES(3);
INSERT INTO dbo.Huecos(col1) VALUES(4);
INSERT INTO dbo.Huecos(col1) VALUES(7);
INSERT INTO dbo.Huecos(col1) VALUES(8);
INSERT INTO dbo.Huecos(col1) VALUES(15);
INSERT INTO dbo.Huecos(col1) VALUES(21);
INSERT INTO dbo.Huecos(col1) VALUES(22);
INSERT INTO dbo.Huecos(col1) VALUES(23);
INSERT INTO dbo.Huecos(col1) VALUES(30);
GO

Planteamiento de la solución

Hoy en día, podemos plantear soluciones basadas en:

Cursores.
Consultas agregadas.
Subconsultas.
Joins.
Mantenimiento manual.

El problema es que cada una de estos planteamientos, conlleva una serie de desventajas, que en no pocas ocasiones producen frustración, ya que normalmente los tiempos de ejecución son inviables.

Cursores

De sobra es conocido por todos la ineficiencia de las soluciones basadas en cursores. No creo necesario explicar en este post que NUNCA se debe llevar a la práctica una solución basada en cursores, ya que salvo muy contadas excepciones, el resultado es tremendamente desastroso para el rendimiento de la instancia, porque no solo la solución es ineficiente, sino que los recursos utilizados por los cursores son excesivos y suelen afectar al resto de consultas y aplicaciones que se ejecutan contra la instancia.

Como ejemplo, basta decir, que un core puede saturarse con un único cursor…y que si tenemos 8 cores, 8 simples cursores simultáneamente nos pueden saturar la CPU.

Subconsultas

Las subconsultas generalmente tienen el problema de ser bastante complejas de entender. Seguro que el lector se ha tenido que enfrentar a editar alguna consulta, y que se haya tenido que sentar calmadamente a evaluar qué esta realizando…y lo mejor es que en ocasiones es alguna consulta que el propio lector generó semanas, o meses atrás.

Sencillamente, programar subconsultas produce código complejo de entender y por tanto de mantener. Por ejemplo, el siguiente ejemplo es muestra de ello:

SELECT MIN(col1) AS rango_inicio, MAX(col1) AS rango_fin
FROM (SELECT col1,
        (SELECT MIN(B.col1)
         FROM dbo.Huecos AS B
         WHERE B.col1 >= A.col1
           AND NOT EXISTS
             (SELECT col1
              FROM dbo.Huecos AS C
              WHERE C.col1 = B.col1 + 1)) AS grp
      FROM dbo.Huecos AS A) AS D
GROUP BY grp;

Siendo su resultado el siguiente:

Mas adelante en este post, se dará un resultado utilizando la cláusula OVER a este problema.

Consultas agregadas

Generalmente soluciones basadas en agregaciones SUM, MAX, MIN,…tienen el problema de que se pierde el detalle.

Pensando en la solución que queremos obtener para el problema mencionado, podemos darnos cuenta como actualmente no nos es suficiente con una única consulta, puesto que si queremos hacer la suma de la columna “ValorOperacion”, no nos vale con la consulta sencilla de SUM(), ya que se perdería el detalle.

Agregacion

SELECT id_cliente,
SUM(ValorOperacion) balance
FROM dbo.IngresosCuenta
group by id_cliente

Y tampoco nos sirve aplicar la cláusula OVER en SQL Server 2008 R2, puesto que el resultado no es el deseado:

SELECT id_cliente, transaccion, ValorOperacion,
  SUM(ValorOperacion) OVER(PARTITION BY id_cliente) AS balance
FROM dbo.IngresosCuenta

En la consulta anterior, no se obtiene el valor deseado, sino la suma por partición…es decir por cada id_cliente, la suma de todas sus transacciones:

Como podemos ver, nos es obviamente imposible obtener el valor acumulado con cada operación realizada.

Joins

La otra vía que podemos utilizar es realizar una consulta del conjunto de datos consigo mismo (SELF-JOIN). Este tipo de soluciones generalmente producen consultas ineficientes

En el ejemplo, la consulta que daría el resultado esperado, seria el siguiente:

SELF-JOIN

SELECT S1.id_cliente, S1.transaccion, S1.ValorOperacion,
  SUM(S2.ValorOperacion) AS balance
FROM dbo.IngresosCuenta AS S1
  JOIN dbo.IngresosCuenta AS S2
    ON S2.id_cliente = S1.id_cliente
   AND S2.transaccion <= S1.transaccion
GROUP BY S1.id_cliente, S1.transaccion, S1.ValorOperacion
order by s1.id_cliente, s1.transaccion

Cuyo resultado es:

Aunque lo llamativo no es que la consulta sea mas o menos bonita, sino el excesivo tiempo que lleva resolverla. Téngase en cuenta que solo se trabaja con 10000 filas en la tabla IngresosCuenta, algo que es definitivamente un nº de filas irrisorio en un sistema real de producción (pensemos en la tabla de transacciones bancarias de cualquier entidad). Con estas premisas, el tiempo de ejecución ha sido el siguiente:

Puede parecer algo no muy desalentador para algunos…pero pongámoslo en contexto. ¿Por qué tardamos tanto en obtener el resultado?

Fijémonos en el plan de ejecución:

[box type=”info”] NOTA: De forma premeditada se ha evitado todo índice para no ver favorecida ninguna solución particular[/box]

Como puede observarse, es una interpretación literal de la consulta de forma que para cada fila de IngresosCuenta, se produce una lectura completa de IngresosCuenta (de nuevo) para ver qué fila tiene el mismo id_cliente y un valor de transacción menor (varias filas) y con eso realizar una suma parcial con la que se obtiene el valor de “balance”.

De esta forma, tenemos del detalle (id_cliente, transaccion y valoroperacion) y el agregado (balance).

Pero matemáticamente, esto produce uno de los costes que menos nos gusta ver a los informáticos…el cuadrático 🙂

Para cada fila “r”, de IngresosCuenta, se leerá la tabla completa, con lo cual…r+r^2 simplificando al máximo.

Esto quiere decir, que sin despeinarnos mucho, podemos sacar este sencillo cálculo:

Para 10.000 filas –> 50 segundos
Para 100.000 filas
- Pues no son 500 segundos, sino 5000 (recuerda que es complejidad cuadrática)
Para 1.000.000 filas
- 500000 segundos
Para 10.000.000 filas (que de nuevo insisto que es una cantidad irrisoria para un problema de banca)
- 50000000 segundos

Ahora, para verlo en perspectiva, para 10 millones de filas:

Si, lees bien…para que finalizase el cálculo tendríamos que esperar 48 años ? . Para entonces sobra decir que ya no nos interesará el resultado.

Mantenimiento manual

Obviamente, cuando uno se debía proponer dar solución al problema anterior, al final optaba por vías alternativas, como son la del almacenamiento en una columna extra vinculada a la tabla de clientes, de su “balance actual”. De esta forma, cada vez que se realizaba una operación por parte del cliente, además de insertar la fila pertinente en la tabla de cuentas, se actualizaba dicha columna para el cliente.

De sobra es conocido que este tipo de soluciones, pueden conllevar a inconsistencias de datos en el caso de que por cualquier razón la aplicación no actualizase correctamente la información.

Ahora que vemos las opciones actuales y cuales son sus limitaciones, podemos proceder a explicar como lo podremos solventar en T-SQL 2012.

Cláusula OVER

La cláusula OVER fue parcialmente implementada en el motor de SQL Server 2005 y así ha perdurado hasta SQL Server 2008 R2. En SQL Server 2012 se ha realizado al fin la implementación total de la misma. Basta con dar un vistazo a la cláusula en SQL Server 2008R2 y en SQL Server 2012:

En SQL Server 2008 R2:

Clausula OVER 2008 R2

Ranking Window Functions
< OVER_CLAUSE > :: =
    OVER ( [ PARTITION BY value_expression , … [ n ] ]
           <ORDER BY_Clause> )
Aggregate Window Functions
< OVER_CLAUSE > :: =
    OVER ( [ PARTITION BY value_expression , … [ n ] ] )

En SQL Server 2012:

Clausula OVER 2012

OVER (
       [ <PARTITION BY clause> ]
       [ <ORDER BY clause> ]
       [ <ROW or RANGE clause> ]
      )
<PARTITION BY clause> ::=
PARTITION BY value_expression , … [ n ]
<ORDER BY clause> ::=
ORDER BY order_by_expression
    [ COLLATE collation_name ]
    [ ASC | DESC ]
    [ ,…n ]
<ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>
<window frame extent> ::=
{   <window frame preceding>
  | <window frame between>
}
<window frame between> ::=
  BETWEEN <window frame bound> AND <window frame bound>
<window frame bound> ::=
{   <window frame preceding>
  | <window frame following>
}
<window frame preceding> ::=
{
    UNBOUNDED PRECEDING
  | <unsigned_value_specification> PRECEDING
  | CURRENT ROW
}
<window frame following> ::=
{
    UNBOUNDED FOLLOWING
  | <unsigned_value_specification> FOLLOWING
  | CURRENT ROW
}
<unsigned value specification> ::=
{  <unsigned integer literal> }

A simple vista, como poco nos llama la atención que la gramática es bastante mas extensa, pero no solo eso, sino que aparecen cláusulas nuevas como son ROW o RANGE. Además, debería llamarnos la atención que no hay distinción ya entre si es una cláusula OVER aplicada sobre funciones de Ranking o sobre funciones de agregación.

Nuevas construcciones basadas en conjuntos

Para comprender la cláusula OVER, creo conveniente publicar la definición oficial de lo que es un “conjunto”:

Definición teórica por Georg Cantor:

Un conjunto es una reunión de objetos que cumplen con cierta propiedad (llamados los elementos de ese conjunto) y que, por tanto, queda definido por tal propiedad.
Un conjunto es una sola entidad matemática, de modo que puede a su vez ser contenido por otro conjunto
Dos conjuntos que tengan los mismos elementos son iguales, luego un conjunto queda determinado por sus elementos

En definitiva, una reunión de objetos sin ningún orden y con una propiedad común.

En este caso, lo que aporta la cláusula OVER, es la posibilidad de operar con un conjunto resultado directamente sin intermediarios, produciendo un nuevo consulto resultado final.

Con la mejora de la cláusula OVER, podemos ahora trabajar con un conjunto de datos, operar con el generando otro conjunto de datos resultado, de una forma especialmente óptima. La cláusula OVER pues, es la que utilizaremos para definir la función de ventana deslizante.

Aquí vemos un ejemplo de aplicación práctica:

En esta consulta, se está mezclando como se aprecia, el famoso detalle y valor agregado que buscamos, debido a que si nos fijamos, se está devolviendo el detalle (id_cliente, transacción, ValorOperacion), pero también el valor de “balance”. Y…¿como calculamos este valor? Pues gracias a la expresividad de la nueva gramática de la cláusula OVER.

En ella estamos indicando que:

Queremos devolver un valor SUM de la columna ValorOperacion
Dicho valor, debe inicializarse y ser diferente para cada id_cliente (partición)
Dicho valor, va a ir aplicándose en el orden designado por “transacción”
Vamos a ir sumando el valor acumulado de las filas hasta el valor actual devuelto

El valor inicial queda especificado al decir “UNBOUNDED PRECEDING”
El valor final queda especificado al decir “CURRENT ROW”
Podríamos hacer combinatorias dispares como “empezar en fila actual y finalizar en última fila”, o en “2 filas atrás y 4 delante de la fila actual…”

Ahora viene la parte “dura”, que es comprender el funcionamiento de todo esto. Hay que realizar un cambio de forma de pensar tradicional basado en iteraciones. Vuelvo a insistir, que el proceso de resolución que sigue SQL Server es un proceso de soluciones basado en operaciones sobre conjuntos, y que por tanto no existe un sistema iterativo para ello…y nosotros no debemos asumirlo mentalmente.

Veamos este código simple, que utiliza una función de ventana deslizante con OVER, para ilustrarlo:

SELECT id_cliente, transaccion, valoroperacion,
rank() over(partition by id_cliente
            order by transaccion) rnk
FROM dbo.IngresosCuenta

La consulta anterior, devuelve los siguientes datos:

Pero cuidado, porque el valor de la columna RNK obtenida con la funcion RANK() no se obtiene de un proceso iterativo, donde una variable se incrementa en +1, por ejemplo, como se ilustra en la imagen siguiente:

Es necesario comprender, que ahora existen “n” ventanas, con límites inicio-fin que se pueden consultar (como veremos en otro futuro post).

De esta forma, la realidad es que la función OVER aplicada, genera “n” ventanas que conviven simultáneamente y que por tanto se tiene una especie de “persistencia” que viene perfectamente bien para escenarios como el que planeamos resolver.

Cada una de las flechas en la imagen anterior, quiere identificar una ventana que existe simultáneamente al resto. Existirán “n”, siendo “n” el número de elementos del conjunto (o filas, según se quiera).

Tomate tu tiempo para asimilarlo, porque puede parecer obvio, sencillo, o incluso extraño o “imposible”, pero cuanto antes pienses en “windows”, antes comenzarás a aprovechar todo el potencial de T-SQL 2012.

Como muestra, aquí te dejo dos ejemplos:

El primero es relativo a la ejecución del problema de complejidad cuadrático anterior…que ahora tiene complejidad lineal 🙂

Sumas acumuladas T-SQL 2012

SELECT id_cliente, transaccion, ValorOperacion,
  SUM(ValorOperacion) OVER(PARTITION BY id_cliente
                           ORDER BY transaccion
                           RANGE BETWEEN UNBOUNDED PRECEDING
                                AND CURRENT ROW) AS balance
FROM dbo.IngresosCuenta

Teniendo el siguiente tiempo de respuesta:

Si, menos de 1s, y la razón es muy sencilla: Únicamente leemos el conjunto de entrada 1 única vez…y como hablamos de tan solo 10.000 filas…el tiempo que invertimos en leerlas es irrisorio.

He aquí su plan de ejecución:

Como se puede ver, hay un único Table Scan, una operación Sort (que obviamente podríamos optimizar con indexación) y un nuevo operador “Window Spool”.En un futuro post hablaré de ellos, pero de momento basta para ver como la eficiencia es más que evidente.

La otra solución de ejemplo que quiero mostrar, es la nueva forma de expresarnos, que hace innecesario ya utilizar subconsultas.

Pasamos de esto:

Solucion con subconsultas

SELECT MIN(col1) AS rango_inicio, MAX(col1) AS rango_fin
FROM (SELECT col1,
        (SELECT MIN(B.col1)
         FROM dbo.Huecos AS B
         WHERE B.col1 >= A.col1
           AND NOT EXISTS
             (SELECT col1
              FROM dbo.Huecos AS C
              WHERE C.col1 = B.col1 + 1)) AS grp
      FROM dbo.Huecos AS A) AS D
GROUP BY grp;

A esto:

Solucion con OVER

with c as(
select col1, col1–rank() over(order by col1) g
from Huecos
)
select min(col1) rango_inicio , max(col1) rango_fin
from c
group by g

Como se puede apreciar, aunque el resultado es el mismo, un desarrollador que se enfrenta a comprender la consulta, tiene bastante menos tiempo de estudio en la solución OVER que en la solución basada en subconsultas.

[box type=”info”] NOTA: Estas dos últimas consultas funcionan en SQL Server 2008 R2 ya que la clausula OVER estaba implementada en funciones de ranking desde 2005.[/box]

Nuevas funciones de agregado

Todo el potencial puede ser extraído ahora tanto de funciones de ranking (ROW_NUMBER, RANK, DENSE_RANK…) como de agregado (SUM, AVG, MIN,..); pero además, ahora aparecen nuevas y excitantes funciones:

De offset
- LAG
- LEAD
- FIRST_VALUE
- LAST_VALUE

- De distribución
  - PERCENT_RANK
  - PERCENTILE_CONT
  - CUME_DIST

Para un siguiente post, queda comentar las nuevas funciones de análisis, así como los valores de definición de las mismas y límites de la cláusula OVER.

Conclusión

T-SQL 2012 nos provee de una expresividad bastante interesante apoyándonos en la cláusula OVER, que puede dar lugar a que optimicemos procesos actuales desde el punto de vista de la calidad y claridad del código, como desde el tan ansiado aumento de rendimiento.

Si os gustan nuestros posts, desde SolidQ os invitamos a visitar nuestro blog o a suscribiros a nuestra newsletter para recibir las últimas novedades del sector en vuestro correo 🙂

Optimiza con T-SQL 2012 y cláusula OVER

Planteamiento de la solución

Cursores

Subconsultas

Consultas agregadas

Joins

Mantenimiento manual

Cláusula OVER

Nuevas construcciones basadas en conjuntos

Nuevas funciones de agregado

Conclusión

Verne Team

1 comment

Deja una respuesta Cancelar la respuesta

Novedades de PowerPivot en Denali (VII): SEARCH and FIND

Novedades de PowerPivot en Denali (VIII): SWITCH

Cómo refrescar un dataset de Power BI al finalizar el proceso de ETL

Planificación de una migración de SQL Server 2000 a SQL Server 2008

Procesamiento incremental de dimensiones II

El lado oscuro de la inicialización instantánea de ficheros

Replicación híbrida y escalabilidad con SQL Server OnPremise y Azure (1/4)

Cálculo de antigüedad en stock

Optimiza con T-SQL 2012 y cláusula OVER

Planteamiento de la solución

Cursores

Subconsultas

Consultas agregadas

Joins

Mantenimiento manual

Cláusula OVER

Nuevas construcciones basadas en conjuntos

Nuevas funciones de agregado

Conclusión

Verne Team

1 comment

Deja una respuesta Cancelar la respuesta

Novedades de PowerPivot en Denali (VII): SEARCH and FIND

Novedades de PowerPivot en Denali (VIII): SWITCH

You May Also Like