Cómo numerar eficientemente las filas con Row_Number()

Desde el SQL Server 2005 la función ROW_NUMBER() permite numerar las filas de manera muy eficiente sin necesidad de recurrir a cursores o subqueries. Vamos a probar y comparar varias estrategias, analizar el rendimiento y elegir la mejor alternativa para numerar las filas de una query.

 

Un poco de historia

Muchos utilizaban cursores para numerar las filas de una consulta SELECT. Las críticas respecto de su eficiencia y rendimiento hizo que otros utilicen y defiendan el uso de subqueries como alternativas más eficientes. Ya que no se procesaban las filas registro a registro, se pensaba que era más eficiente pero veces las consecuencias resultaron ser catastróficas cuando el volumen de registros crecía.

 

Generando los datos

Pueden descargar el código de este artículo desde:
https://github.com/JoseMarianoAlvarez/Blog/blob/master/SqlServer/Rendimiento/PruebaNumeradores.sql

Para generar los datos vamos a usar un pequeño script que crea una tabla y luego le agrega 1000 registros de prueba.

El modelo de la tabla es el siguiente:

CREATE TABLE [dbo].[PruebaNumeradores]
(
 [Nombre] [varchar](255) NULL,
 [FechaIngreso] [datetime] NULL,
 [Correo] [varchar](255) NULL,
 [Pais] [varchar](100) NULL
) ON [PRIMARY]

 

No estoy tomando en cuenta las consecuencias de que existan duplicados en la columna “Nombre” que es la columna que usamos en alguna de las alternativas para ordenarla y numerarla. Para un resultado 100% confiable se debe asegurar que las columnas usadas en los ORDER BY garanticen la unicidad.

Para aumentar la eficiencia vamos a crear un índice sobre la columna Nombre

CREATE NONCLUSTERED INDEX IX_PruebaNumeradores_Nombre ON PruebaNumeradores(Nombre)
GO

 

Agregando los registros

En el link que esta al final del artículo tienen  el script con  los 1000 INSERT para generar los datos de prueba. El script a continuación es el primero de los 1000 INSERT.

INSERT [dbo].[PruebaNumeradores]
(
    [Nombre],
    [FechaIngreso],
    [Correo],
    [Pais]
)
VALUES
(
	N'Alan Lucas', 
	CAST(N'2018-05-28T23:45:57.000' AS DateTime), 
	N'tincidunt@massaSuspendisse.ca', 
	N'New Caledonia'
)

 

Analicemos algunas alternativas para numerar filas

Para poder determinar los costos de cada alternativa, habilitamos en el Management Studio que obtenga el plan actual de ejecución presionando el botón correspondiente en la barra de herramientas y ejecutamos el siguiente script para que nos muestra las lecturas lógicas en cada ejecución:

set statistics io on
 go

 

 

Subquery como numerador

Es importante entender que para poder numerar las filas con una subquery es necesario ordenarlas. La subquery cuenta la cantidad de registros “anteriores” y por lo tanto requiere comparar los valores de manera ordenada. Una condición adicional es que se requiere que los valores a comparar sean únicos, porque si hay duplicados habrá también números duplicados en la cuenta.

Select
        (
            select COUNT(*)
            from PruebaNumeradores AS s
            where s.Nombre <= p.nombre
        ) as id,
        *
from 
        PruebaNumeradores p
order by 
        nombre;

IO

Table 'PruebaNumeradores'. Scan count 1001, logical reads 4511, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Plan

Como vemos esta alternativa tiene un costo de 0,7252 y realiza 4511 lecturas lógicas, las cuales en su mayoría se deben a la operación anidada que hace el subquery para realizar el cálculo de cantidad de registros previos. La cantidad de lecturas se correlaciona con la cantidad de veces que repite el acceso al índice para contar los registros previos. Esta alternativa se degrada rápidamente con el aumento del volumen aun teniendo un índice óptimo para hacer la cuenta como es en este caso.

 

Usando ROW_NUMBER() con ORDER BY para numerar

Select
        ROW_NUMBER() OVER (ORDER BY a.nombre ) as id,
        A.*
from
        PruebaNumeradores a;

IO

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'PruebaNumeradores'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Plan

La cantidad de lecturas lógicas ha disminuido a tan solo 10 que corresponden a la lectura de las páginas con datos. Como vemos esta alternativa tiene un costo de 0,0380569 el cual en su mayoría se debe a la lectura de registros y a la necesidad de ordenar los registros por nombre mediante el operador SORT. Por lo tanto, si bien hemos logrado reducir sensiblemente la cantidad de lecturas, aun tenemos una operación sort que es costosa. Esta alternativa es mucho más eficiente que la anterior y permite obtener los registros numerados y ordenados por nombre.

 

Evitar el ORDER BY en el ROW_NUMBER()

Es evidente que si queremos mejorar la única alternativa posible es evitar el SORT de la opción anterior ya que cualquiera sea la alternativa es necesario leer los registros para devolver el resultado. El problema radica en que si queremos eliminar el ORDER BY de la cláusula obtenemos un error.

Select
        ROW_NUMBER() OVER () as id,
        A.*
from
        PruebaNumeradores a;

Resultado con error porque el ORDER BY es obligatorio en la clausula ROW_NUMBER.

Msg 4112, Level 15, State 1, Line 1047
 The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

Solución

Pero Podemos mentirle usando un literal,  haciendo lo siguiente.

Select
        ROW_NUMBER() OVER (Select 1) as id,
        A.*
from
        PruebaNumeradores a;

IO

Table 'PruebaNumeradores'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Plan

Como vemos ha desaparecido el SORT y el costo del plan se ha reducido prácticamente al costo de leer los registros. El costo de la query es ahora de 0,0111487 que es el menor posible y mucho mas eficiente que las otras dos alternativas anteriores.

 

Conclusión

Si bien hay otras alternativas como usar la función IDENTITY() o la función RANK() o una secuencia SEQUENCE, la alternativa utilizando la función ROW_NUMBER() con un literal en la sentencia ORDER BY es la más eficiente para numerar las filas.