Como numerar eficientemente las filas de una consulta select

Uno de las preguntas más frecuentes es cómo numerar la filas de una consulta. En SQL Server 2005 se introdujo ROW_NUMBER() para poder realizar esta operación fácilmente. Sin embargo en los casos en que no la podemos usar como en SQL Server 2000 existen creencias de que los subquery pueden ser alternativas eficientes y las consecuencias suelen ser catastróficas luego de que el volumen de registros crece.

Usando la tabla AdventureWorks2008.Sales.Customer vamos a probar cuatro estrategias y sus consecuencias.

 

Usando un subquery como habitualmente se sugiere en SQL Server 2000

Select  
    (
        select
            COUNT(*) + 1
        from AdventureWorks2008.Sales.Customer B 
        where b.AccountNumber  < a.AccountNumber
    ) as fila, 
    a.*
from AdventureWorks2008.Sales.Customer a
order by rowguid 

El resultado del plan muestra como se anida un SCAN de la tabla dentro de un NESTED LOOP lo cual genera un severo problema de rendimiento.

  |--Compute Scalar(DEFINE:([Expr1006]=[Expr1004]+(1)))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[CustomerID]))
            |--Compute Scalar(DEFINE:([a].[AccountNumber]=[AdventureWorks2008].[Sales].[Customer].[AccountNumber] as [a].[AccountNumber]))
            |    |--Compute Scalar(DEFINE:([a].[AccountNumber]=isnull('AW'+[AdventureWorks2008].[dbo].[ufnLeadingZeros]([AdventureWorks2008].[Sales].[Customer].[CustomerID] as [a].[CustomerID]),'')))
            |         |--Sort(ORDER BY:([a].[rowguid] ASC))
            |              |--Clustered Index Scan(OBJECT:([AdventureWorks2008].[Sales].[Customer].[PK_Customer_CustomerID] AS [a]))
            |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))
                 |--Stream Aggregate(DEFINE:([Expr1010]=Count(*)))
                      |--Index Spool(SEEK:([B].[AccountNumber] < isnull('AW'+[AdventureWorks2008].[dbo].[ufnLeadingZeros]([AdventureWorks2008].[Sales].[Customer].[CustomerID] as [a].[CustomerID]),'')))
                           |--Compute Scalar(DEFINE:([B].[AccountNumber]=isnull('AW'+[AdventureWorks2008].[dbo].[ufnLeadingZeros]([AdventureWorks2008].[Sales].[Customer].[CustomerID] as [B].[CustomerID]),'')))
                                |--Index Scan(OBJECT:([AdventureWorks2008].[Sales].[Customer].[IX_Customer_TerritoryID] AS [B]))

 

Si vemos a continuación los detalles de la cantidad de IO podemos ver claramente como la cantidad de lecturas lógicas es de 1103320,

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

Table 'Customer'.
    Scan count 2,
    logical reads 160,
    physical reads 0,
    read-ahead reads 0,
    lob logical reads 0,
    lob physical reads 0,
    lob read-ahead reads 0.

El resultado de la consulta tardo 1 minuto y 37 segundos

 

 

Usando la nueva sintaxis ROW_NUMBER() introducida en SQL Server 2005

Select
       ROW_NUMBER() OVER (ORDER BY a.AccountNumber ) as fila,
       A.* 
from 
       AdventureWorks2008.Sales.Customer a
order by 
       rowguid 

El resultado del plan muestra no se anida en este caso ningún SCAN sino que es un plan secuancial y muy eficiente.

  |--Sort(ORDER BY:([a].[rowguid] ASC))
       |--Sequence Project(DEFINE:([Expr1002]=row_number))
            |--Segment
                 |--Compute Scalar(DEFINE:([a].[AccountNumber]=[AdventureWorks2008].[Sales].[Customer].[AccountNumber] as [a].[AccountNumber]))
                      |--Sort(ORDER BY:([a].[AccountNumber] ASC))
                           |--Compute Scalar(DEFINE:([a].[AccountNumber]=isnull('AW'+[AdventureWorks2008].[dbo].[ufnLeadingZeros]([AdventureWorks2008].[Sales].[Customer].[CustomerID] as [a].[CustomerID]),'')))
                                |--Clustered Index Scan(OBJECT:([AdventureWorks2008].[Sales].[Customer].[PK_Customer_CustomerID] AS [a]))

 

Vemos como el resultado de aplicar la nueva función ha cambiado y solo se realizan 123 lecturas lógicas.

Table 'Customer'.
    Scan count 1,
    logical reads 123,
    physical reads 0,
    read-ahead reads 0,
    lob logical reads 0,
    lob physical reads 0,
    lob read-ahead reads 0.

 El resultado de la consulta tardo 1 segundo.

 

 

Usando la función IDENTITY() como en SQL Server 2000

En este caso se debe recurrir a una tabla temporal pero vemos que el beneficio es sustancial.

Debido a lo que ocurre con la función IDENTITY() y el ORDER BY se debe crear una tabla temporal para insertar los registros. Les dejo el Enlace a “The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause”.

http://support.microsoft.com/kb/273586

NOTA: Quiero agradecer a Alejandro Mesa quien me hizo el comentario del error en los foros respecto de este comportamiento que yo había omitido en el Post el cual ya se encuentra actualizado y corregido según la documentacióní.

Creamos la tabla temporal para ordenar los registros

CREATE TABLE [dbo].[#Clientes](
    [fila] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NULL,
    [PersonID] [int] NULL,
    [StoreID] [int] NULL,
    [TerritoryID] [int] NULL,
    [AccountNumber] [varchar](10) NOT NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

 

Esta es la parte de la consulta donde se generan los números de fila.

Insert into #Clientes
(
    CustomerID, 
    PersonID, 
    StoreID, 
    TerritoryID, 
    AccountNumber, 
    rowguid, 
    ModifiedDate
)
Select     
    cast(CustomerID as int) as CustomerID , 
    PersonID, 
    StoreID, 
    TerritoryID, 
    AccountNumber, 
    rowguid, 
    ModifiedDate
from AdventureWorks2008.Sales.Customer a
ORDER BY a.AccountNumber 

Ahora vemos nuevamente que no se anida un SCAN en el plan por lo que resulta mucho más eficiente que en el caso del subquery.

  |--Table Insert(OBJECT:([#Clientes]), SET:([#Clientes].[CustomerID] = [Expr1006],[#Clientes].[PersonID] = [AdventureWorks2008].[Sales].[Customer].[PersonID] as [a].[PersonID],[#Clientes].[StoreID] = [AdventureWorks2008].[Sales].[Customer].[StoreID] as [a
       |--Compute Scalar(DEFINE:([Expr1007]=getidentity((-7),(0),N'#Clientes')))
            |--Top(ROWCOUNT est 0)
                 |--Compute Scalar(DEFINE:([a].[AccountNumber]=[AdventureWorks2008].[Sales].[Customer].[AccountNumber] as [a].[AccountNumber], [Expr1006]=CONVERT(int,[AdventureWorks2008].[Sales].[Customer].[CustomerID] as [a].[CustomerID],0)))
                      |--Sort(ORDER BY:([a].[AccountNumber] ASC))
                           |--Compute Scalar(DEFINE:([a].[AccountNumber]=isnull('AW'+[AdventureWorks2008].[dbo].[ufnLeadingZeros]([AdventureWorks2008].[Sales].[Customer].[CustomerID] as [a].[CustomerID]),'')))
                                |--Clustered Index Scan(OBJECT:([AdventureWorks2008].[Sales].[Customer].[PK_Customer_CustomerID] AS [a]))

 

Si vemos los resultados en I/O solo se realizan 123 lecturas logicas

Table 'Customer'.
    Scan count 1,
    logical reads 123,
    physical reads 0,
    read-ahead reads 0,
    lob logical reads 0,
    lob physical reads 0,
    lob read-ahead reads 0.

Select * 
from #Clientes c
order by C.rowguid 

El plan de esta parte es el SCAN y el SORT.

|--Sort(ORDER BY:([c].[rowguid] ASC))
       |--Table Scan(OBJECT:([tempdb].[dbo].[#Clientes] AS [c]))

 

Si vemos los resultados en I/O solo se realizan 167 lecturas logicas

Table '#Clientes_000000000007'.
    Scan count 1,
    logical reads 167,
    physical reads 0,
    read-ahead reads 0,
    lob logical reads 0,
    lob physical reads 0,
    lob read-ahead reads 0.

Queda eliminar la tabla temporal con

drop table #Clientes 

El total acumulado de tiempo fue de 1 segundo.

 

 

Conclusión:

 

Caso	Lecturas	Tiempo
Subquery	1103480	97 seg
Row_Number()	123	1 seg
Tabla temporal + Identity()	280	1 seg

Vemos que es mucho más eficiente la tercera opción usando una tabla temporal y la función identiy() que la opción de la subquery (subconsulta). Sin embargo esta no es  tan eficiente como en el caso de la nueva sintaxis de SQL Server 2005/8 con ROW_NUMBER(). Por lo tanto si tiene SQL Server 2005 o SQL Server 2008 Lo mejor es usar la funcion ROW_NUMBER() y si tienen SQL Server 2000 la mejor opción es usar la tabla temporal y la función Identity().

 

En todos los casos todas las lecturas fueron lógicas ya que la tabla tenia todas las paginas en memoria.

NOTA: Me falta comparar la alternativa de usar CLR y de usar un cursor de T-Sql.