SQL Server, ideas y experiencias

Como numerar eficientemente las filas de una consulta select

por Jose Mariano Alvarez 10. julio 2009

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.

Tags: , , ,

Artículos

FCE - Materiales - SQL Server Práctico 27 Marzo 2009

por Jose Mariano Alvarez 28. marzo 2009
UBA-FCE En esta presentación que realicé en la Facultad de Ciencias Económicas de la UBA(Universidad de Buenos Aires) con el patrocinio del Grupo de Usuarios Microsoft, estuve  mostrando algunos detalles prácticos de la programabilidad del SQL Server 2008. Esta presentación estuvo orientada a mostrar algunos detalles prácticos del lenguaje T-SQL, especialmente en temas de facilidad y productividad.
 
Comenzamos mostrando cómo se puede manipular la concurrencia mediante la utilización de los distintos niveles de aislamiento con las sentencias SET TRANSACTION ISOLATION LEVEL READ COMMITTED, SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED y el hint WITH (NOLOCK). Se explico las consecuencias de realizar lecturas sucias algo que no es muy conocido.
 
Luego mostré los nuevos operadores INTERSEC, EXCEPT, la sentencia CREATE SYNONYM y las common table expressions  (CTE) que permiten simplificar la forma en que escribimos nuestro código.
 
Cuando explique las nuevas clausulas de la integridad referencial declarativa en la definición de las  CONSTRAINT FOREIGN KEY, ON DELETE SET NULL, ON DELETE SET DEFAULT, ON UPDATE SET NULL, ON UPDATE SET DEFAULT, introducidas en SQL Server 2005, que completaban la especificación del ANSI estuvimos hablando de la diferentes problemáticas que se presentan cuando no se respetan las condiciones de integridad de datos.
 
También pudimos ver las novedades de la clausula TOP, las posibilidades de la clausula OUTPUT y el nuevo manejo estructurado de errores con BEGIN TRY, END TRY, BEGIN CATCH, END CATCH que abre nuevas posibilidades en la metodología de programación de procedimientos almacenados (STORED PROCEDURES).
 
Luego pasamos a la sentencia MERGE que permita realizar actualizaciones masivas de manera más eficiente y simple. Cuando vimos  la posibilidad de usar columnas SPARSE y XML COLUMN_SET FOR ALL_SPARSE_COLUMNS , surgieron varias preguntas acerca del tipo de dato XML, introducido en SQL Server 2005 y una pregunta clásica de si se podían pasar desde el cliente “registros” en un XML como parámetro de un stored procedure. Entonces aproveche la ocasión para mostrar el nuevo tipo de parámetro tabla que se puede crear a partir de un tipo creado con CREATE TYPE.
 
Estuve muy a gusto con el auditorio y con los asistentes y organizadores y espero que lo hayan podido aprovechar.
 
Este es el contenido de los ejemplos contenidos en el archivo de demos:
  • 01 - Isolation Levels.sql   
  • 02 - EXCEPT-INTERSECT.sql
  • 03 - Synonym.sql            
  • 04 - CTE.sql
  • 05 - DRI.sql                
  • 06 - TOP.sql
  • 07 - OUTPUT.sql             
  • 08 - ERRORES.sql
  • 09 - Merge.sql              
  • 10 - Sparse column SET.sql
  • 11 - TVP-DATE-TIME.sql      

FCE20090327 - SQL Practico - Demos.zip (9,00 kb)

Tags: , , ,

Eventos y conferencias

Desafio T-SQL - Concatenación de strings agrupados

por Jose Mariano Alvarez 4. marzo 2009

Adam Machanic ha creado un interesante desafío que tiene como premio una suscripción MSDN full.

Quienes estén intersados tienen tiempo hasta el 15 de marzo. Pueden encontrar los detalles en el Blog de Adam.

Adam Machanic : T-SQL Challenge: Grouped String Concatenation

Tags: , ,

Comunidad

Como depurar (debug) código T-SQL en SQL Server 2005

por Jose Mariano Alvarez 3. marzo 2009

Antes los desarrolladores de T-SQL escribían y depuraban sus aplicaciones de T-SQL con el Analizador de consultas de SQL Server 2000 (Query Analyzer). Ahora, el SQL Server Management Studio 2005 lo ha reemplazado y no tiene ningún depurador. Para poder depurar código T-SQL es necesario usar el depurador que tiene el Visual Studio 2005. También se puede escribir el código T-SQL en Visual Studio 2005 utilizando un proyecto de base de datos.

Para depurar paso a paso un objeto T-SQL como por ejemplo un stored procedure de base de datos se debe hacer:

  1. Crear un proyecto del tipo base de datos.
  2. Luego de realizada la conexión a la base de datos en el Server Explorer del Visual Studio, seleccionar el objeto que se va a depurar abriendo en el árbol el nodo que corresponda.
  3. Hacer doble clic en el nombre del objeto seleccionado para cargar el código en el editor de texto.
  4. Hacer clic con el botón derecho del mouse en el nombre del objeto dentro del árbol y en el menú elegir “Ir a <tipo de objeto>”. Si requiere parámetros, aparecerá un cuadro de diálogo que permite asignar los valores para cada parámetro.

DebugStepInto2

Para depurar desde una secuencia de comandos (batch) que se ejecuta en el SQL Server Management Studio (no se puede poner puntos de interrupción en esta secuencia de comandos pero si en los objetos de base de datos que utiliza) se debe hacer:

  1. Crear un proyecto del tipo base de datos.
  2. Luego de realizada la conexión a la base de datos en el Server Explorer del Visual Studio, abrir el objeto de base de datos que se va a depurar (doble clic) y definir los puntos de parada con F9 (breakpoint).
  3. Asociar el depurador del Visual Studio al proceso de SQL Server. En el menú Herramientas seleccionar Asociar al proceso y en el cuadro de diálogo, buscar “sqlservr.exe” en la lista de procesos disponibles y hacer clic para marcarlo. Luego presionar el botón Seleccionar para abrir el cuadro “Seleccionar tipo de código” y seleccionar la opción “Determinar automáticamente el tipo de código para depurar” y luego el botón Aceptar y el botón Asociar.
  4. En SQL Server Management Studio, ejecutar la secuencia de comandos.

VsTsqlBreakpoint2

También es posible ejecutar una secuencia de comandos (batch) desde un proyecto de base de datos de Visual Studio 2005. Se debe recordar que para depurar un trigger, se debe iniciar la sesión de depuración (debug) en un procedimiento almacenado. Porque no se puede depurar desde una secuencia de comandos SQL.

Tags: , ,

Artículos

Powered by SQL Total Consulting


View Jose Mariano Alvarez's profile on LinkedIn

 Add to Technorati Favorites 

Calendar

<<  octubre 2017  >>
lumamijuvido
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

View posts in large calendar

Locations of visitors to this page

Widget Twitter not found.

Root element is missing.X


Valid XHTML 1.0 Transitional

Valid CSS!