Rendimiento de cursores por PortaSQL

Este articulo intentara realizar un análisis a una practica muy común en el desarrollo de aplicaciones sobre sqlserver, sobre todo desde el lado del servidor de Base de Datos. Antes de empezar con el análisis haremos una introducción a los cursores.

Introducción:

Un cursor es la forma de procesar los datos fila a fila en lugar de hacerlo por conjunto de resultados. Esta técnica data desde hace varios años ya, donde muchos desarrolladores de bases de datos como Access, FoxPro, etc. lo utilizan de forma muy habitual. Este proceso consta de recorrer fila a fila un conjunto de resultados e ir procesando las mismas una a una. Por Ej., podríamos tener una consulta que nos retorna todos los Clientes y luego un proceso que recorre cliente por cliente (fila a fila) para poder realizar en cada uno de ellos una actualización algún dato.

En las bases de datos antes mencionadas esta técnica era no solo habitual sino que también era una de las pocas maneras que teníamos para poder resolver algunos problemas, quien no ha usado un cursor para poder calcular balances o cosas similares.

Todo esto esta muy bien y es muy natural para los desarrolladores de esas bases de datos, pero ahora nos encontramos con un problema y es que las bases de datos de antes no son las mismas que las de ahora, antes pensar en un gestor de base de datos como SqlSErver era para algunos elegidos que podían abonar los altos costos de equipos, licencias, etc.

Hoy día, el uso de SqlServer (ya sea su versión Standard / Entherprise o su versión MSDE) se ha aumentado y se esta transformando en un Standard entre los desarrolladores. Ustedes se preguntaran que tiene que ver esto con los cursores verdad? Pues mucho mas de lo que se imaginan. Los motores de bases de datos y en especial SqlServer están pensados y optimizados para trabajar con conjuntos de datos y no fila a fila (Cursores) con lo cual ya tenemos aquí un problema, debemos pensar ahora nuestras sentencias de forma tal que traten de no usar cursores ya que dicho motor no esta optimizado para su uso. Claro, todo esto va a requerir un conocimiento mucho mas avanzado de T-SQL y empezar a cambiar la forma de pensar la solución a los problemas, ahora debemos casi olvidarnos de los cursores y usarlo en cosas muy especificas.

Pues bien, se que mucho de ustedes se estarán preguntando: ¿Pero yo uso cursores y nunca he tenido un problema? ¿Porque debería cambiar?, bueno aquí vamos a tratar de demostrar lo malo que son los cursores y el impacto que pueden llegar a tener sobre nuestros sistemas que corren bajo SqlServer.

Pero cuidado, tampoco vale decir “NO USAR NUNCA CURSORES”, no hay que ser tan extremistas, solo debemos saber donde y cuando usarlos, ya que si están disponibles es para que los usemos verdad? El tema es como y cuando, y eso intentaremos aprender J

Cursores y su creación:

Para crear cursores en SqlServer solo debemos usar algo parecido a lo siguiente:

DECLARE micursor CURSOR LOCAL FORWARD_ONLY FOR

SELECT LISTADECAMPOS FROM TABLA

OPEN nuestrocursor

…………………..

FETCH NEXT FROM nuestrocursor

CLOSE nuestrocursor

DEALLOCATE nuestrocursor

Como podrán observar es algo muy común para casi todos ustedes verdad?.

SqlSErver dispone de cuatro tipos de cursores:

• Estáticos

• Dinámicos

• De desplazamiento solo hacia delante

• Controlado por conjunto de claves

Test :

Bueno, ahora ya conocemos que son básicamente los cursores y que tipos tiene SqlServer. Ahora lo que haremos es una primer prueba de su uso versus el uso de instrucciones pensadas en el conjunto de resultados.

Los ejemplos que veremos a continuación deberán ejecutarlos desde su query analizer.

El primer ejercicio es muy simple y la idea es mostrar con algo simple que efectos tienen los cursores sobre el desempeño.

Lo primero que haremos es lo siguiente, crearemos una tabla ARTICULOS la cual tendrá mas o menos unos 100.000 ítems, e intentaremos eliminar los registros que cumplen con una condición, esto lo haremos con cursores y luego sin ellos, y mediremos en ambos casos la performance, manos a la obra J

USE NORTHWIND

GO

CREATE TABLE ARTICULOS (ROWID INT IDENTITY, ID VARCHAR(30),TIPO CHAR(3))

GO

DECLARE @CEROS INT

DECLARE @N INT

DECLARE @TIPO varCHAR(3)

SET @N = 1

SET @CEROS =  6

SET @TIPO =’A’

WHILE @N  <= 100000

BEGIN

INSERT INTO ARTICULOS(ID,TIPO) VALUES (REPLICATE(‘0’,@CEROS – LEN(@N)) +

CONVERT(VARCHAR(6),@N),@TIPO)

IF @TIPO = ‘A’ SET @TIPO =’B’

ELSE IF @TIPO = ‘B’ SET @TIPO = ‘C’

ELSE IF @TIPO = ‘C’ SET @TIPO = ‘A’

SET @N = @N + 1

END

Ahora probaremos de eliminar todos los artículos C usando cursores. Para poder medir la performance lo primero que haremos es medir cuanto tiempo demora, como así también otros indicadores.

DECLARE @ROWID INT

DECLARE @TIPO VARCHAR(3)

DECLARE micursor CURSOR LOCAL FORWARD_ONLY

FOR SELECT ROWID,TIPO FROM ARTICULOS

BEGIN TRAN

OPEN MICURSOR

FETCH NEXT FROM MICURSOR

INTO @ROWID,@TIPO

WHILE @@FETCH_STATUS = 0

BEGIN

IF @TIPO = ‘A’

BEGIN

DELETE FROM ARTICULOS WHERE CURRENT OF MICURSOR

END

FETCH NEXT FROM MICURSOR

INTO @ROWID,@TIPO

END

ROLLBACK TRAN

Bien, ahora haremos la misma operación pero sin utilizar cursores y si pensando en el conjunto de registros, veamos que sucede J

BEGIN TRAN

DELETE FROM ARTICULOS WHERE TIPO=’A’

ROLLBACK TRAN

Como podrán observar entre el primer método con cursores y el segundo hay una enorme diferencia, el primer método tardo casi el triple y además uso mucho mas el CPU y genero muchos mas bloqueos mientras duro todo el proceso. El segundo método ha mostrado una mejora considerable.

Este es solo un simple ejemplo con una instrucción muy simple, imagínese lo que pasaría si fueran muchos mas registros o si el problema a resolver seria mas complejo, pues el resultado puede ser catastrófico y que un proceso se demore horas cuando si se hubiere pensado en conjunto de instrucciones quizás hubiere demorado minutos.

Claro ahora nos preguntaremos, este es un simple ejemplo y lo veo fácil, pero hay cosas que si no uso cursores no las puedo solucionar!!, pues bien, les diré algo, yo llevo mas de 8 años con sqlserver y he implementado varios sistemas con el, les aseguro que en producción no he utilizado ni un solo cursor, y he tenido que resolver problemas tan complejos o mas que los comunes. Un detalle, pensar en cursores es mucho mas fácil para los desarrolladores ya que tienen eso incorporado, pero como hemos visto en esta primer etapa no es una muy buena idea para el motor de base de datos, así que si se toman el tiempo para pensar el problema sin cursores seguro que le harán un gran beneficio a sus aplicaciones J

Ahora mostraremos un caso un tanto mas complejo para usar los dos métodos (cursores y T-SQL) y comparar como se comportan cada uno.

Test2:

La tabla artículos que teníamos en el caso 1 le agregaremos un campo llamado “Precio” el cual deberemos actualizar según algunos criterios. Este campo será alimentado de una tabla “Transacciones” donde hay una cantidad de 500.000 registros, (podrían a llegar a ser líneas de Ordenes de Compra para cada articulo, un caso muy común en nuestros sistemas)

La actualización tendrá este criterio

• Si el tipo de articulo es “A”, debemos poner en el campo precio el ultimo “Precio” de la tabla transacciones referente a ese articulo

• Si el tipo de articulo es “C” tomaremos el primer costo.

• Todo debe estar dentro de una transacción

Nota: Definimos primero o ultimo según la fecha de transacción.

Bien, manos a la obra, empecemos por rearmar la tabla artículos y correr los otros pasos para la creación de la tabla “Transacciones” y todo el llenado de esta misma

USE  NORTHWIND

GO

TRUNCATE TABLE ARTICULOS

DECLARE @CEROS INT

DECLARE @N INT

DECLARE @TIPO varCHAR(3)

SET @N = 1

SET @CEROS =  6

SET @TIPO =’A’

WHILE @N  <= 100000

BEGIN

INSERT INTO ARTICULOS(ID,TIPO) VALUES (REPLICATE(‘0’,@CEROS – LEN(@N)) +

CONVERT(VARCHAR(6),@N),@TIPO)

IF @TIPO = ‘A’ SET @TIPO =’B’

ELSE IF @TIPO = ‘B’ SET @TIPO = ‘C’

ELSE IF @TIPO = ‘C’ SET @TIPO = ‘A’

SET @N = @N + 1

END

USE NORTHWIND

GO

ALTER TABLE ARTICULOS ADD PRECIO FLOAT

GO

CREATE TABLE TRANSACCIONES (ROWID INT IDENTITY, FECHA DATETIME, ARTICULO_ID VARCHAR(15), PRECIO FLOAT)

GO

–CREAMOS INDICES

CREATE CLUSTERED INDEX INDICE11 ON ARTICULOS(TIPO)

CREATE CLUSTERED INDEX INDICE12 ON TRANSACCIONES(FECHA)

CREATE INDEX INDICE13 ON ARTICULOS(ID)

CREATE INDEX INDICE14 ON TRANSACCIONES(ARTICULO_ID)

GO

USE NORTHWIND

GO

— LLENAMOS LA TABLA TRANSACCIONES

DECLARE @CEROS INT

DECLARE @N INT

DECLARE @N2 INT

SET @N = 1

SET @CEROS =  6

SET @N2 = 1

truncate table transacciones

WHILE @N  <= 100000

BEGIN

SET @N2 = 1

WHILE @N2 <= 5

BEGIN

INSERT INTO TRANSACCIONES(FECHA,ARTICULO_ID,PRECIO)

VALUES (GETDATE()+@N2, REPLICATE(‘0’,@CEROS – LEN(@N)) +

CONVERT(VARCHAR(6),@N),@N * 1.00 /@N2 * 1.00)

SET @N2 = @N2 + 1

END

SET @N = @N + 1

END

Bien, ahora ya tenemos la tabla “Transacciones” y la hemos llenado con datos. El siguiente paso será realizar el UPDATE del campo “Precio” de la tabla “Artículos” con los criterios anteriormente marcados. Esto lo haremos primero con cursores y luego sin ellos.

Nota: tanto el Script de generación de datos como el de UPDATE siguiente pueden demorar varios minutos, si desea hacer la prueba con menos registros solo debe cambiar las condiciones de los WHILE, le aconsejo que no lo haga con pocos registros porque no podrá comprender la magnitud del problema L

Con Cursores:

DECLARE @ID VARCHAR(15)

DECLARE @TIPO VARCHAR(3)

DECLARE micursor CURSOR LOCAL FORWARD_ONLY

FOR SELECT ID,TIPO FROM ARTICULOS

BEGIN TRAN

OPEN MICURSOR

FETCH NEXT FROM MICURSOR

INTO @ID,@TIPO

WHILE @@FETCH_STATUS = 0

BEGIN

IF @TIPO = ‘A’

BEGIN

UPDATE ARTICULOS SET PRECIO = ISNULL(t2.precio,0)

FROM ARTICULOS, (SELECT T.ARTICULO_ID,T.PRECIO FROM TRANSACCIONES T

INNER JOIN (SELECT MAX(FECHA) AS FECHA, ARTICULO_ID

FROM TRANSACCIONES WHERE ARTICULO_ID = @ID GROUP BY ARTICULO_ID) T2 ON

T.ARTICULO_ID = T2.ARTICULO_ID AND

T.FECHA = T2.FECHA WHERE T.ARTICULO_ID=@ID) t2 where

articulos.id = t2.articulo_id and id = @id and

articulos.tipo=’A’

END

IF @TIPO = ‘C’

BEGIN

UPDATE ARTICULOS SET PRECIO = ISNULL(t2.precio,0)

FROM ARTICULOS, (SELECT T.ARTICULO_ID,T.PRECIO FROM TRANSACCIONES T

INNER JOIN (SELECT MIN(FECHA) AS FECHA, ARTICULO_ID

FROM TRANSACCIONES WHERE ARTICULO_ID = @ID GROUP BY ARTICULO_ID) T2 ON

T.ARTICULO_ID = T2.ARTICULO_ID AND

T.FECHA = T2.FECHA WHERE T.ARTICULO_ID=@ID) t2 where

articulos.id = t2.articulo_id and id = @id

and articulos.tipo=’C’

END

FETCH NEXT FROM MICURSOR

INTO @ID,@TIPO

END

COMMIT TRAN

Sin Cursores:

UPDATE ARTICULOS SET PRECIO = case when ARTICULOS.tipo=’a’ then ISNULL(t2.precio,0) when ARTICULOS.tipo=’c’ then

isnull(t3.precio,0) end

FROM ARTICULOS LEFT JOIN (SELECT T.ARTICULO_ID,T.PRECIO FROM TRANSACCIONES T

INNER JOIN (SELECT MAX(FECHA) AS FECHA, ARTICULO_ID

FROM TRANSACCIONES INNER JOIN ARTICULOS ON

TRANSACCIONES.ARTICULO_ID = ARTICULOS.ID  WHERE TIPO = ‘A’ GROUP BY ARTICULO_ID) T2 ON

T.ARTICULO_ID = T2.ARTICULO_ID AND

T.FECHA = T2.FECHA) t2 ON

ARTICULOS.ID = T2.ARTICULO_ID

LEFT JOIN

(SELECT T.ARTICULO_ID,T.PRECIO FROM TRANSACCIONES T

INNER JOIN (SELECT MIN(FECHA) AS FECHA, ARTICULO_ID

FROM TRANSACCIONES INNER JOIN ARTICULOS ON

TRANSACCIONES.ARTICULO_ID = ARTICULOS.ID

WHERE TIPO = ‘C’ GROUP BY ARTICULO_ID) T2 ON

T.FECHA = T2.FECHA) t3 ON

articulos.id = t3.articulo_id

WHERE ARTICULOS.TIPO=’C’ OR ARTICULOS.TIPO=’A’

Bueno, seria interesante que corran ambos procesos y saquen sus propias conclusiones pero en mi maquina paso lo siguiente:

Proceso con Cursores: Duro unos 16 minutos

Proceso sin Cursores: Duro unos 5 segundos

Resumen:

El uso de cursores no es una técnica para nada recomendada con sqlserver, pero tampoco es que no se deben usar nunca, por Ej.: si queremos armar un script que recorra nuestras bases de datos y verifique por Ej. el espacio utilizado por el Log y a partir de un valor tomar la decisión de hacerle un backup, esto seria un buen ejemplo donde el uso de cursores no afectaría en lo mas mínimo, y la razón principal es porque tendríamos muy pocos registros, no creo que exista un servidor con 100.000 bases de datos verdad J

Via: PortalSQL

He visto sistemas donde tenían procesos que duraban horas (incluso uno duraba mas de 7hs) y el gran problema era que estaban pensados con cursores, se han sacado los mismos y dichos procesos han paso a durar minutos y hasta en algunos casos segundos.

Esto es muy importante, si nuestros procesos son lentos nuestro sistema no será algo que los usuarios quieran usar, y además pensemos que en un servidor de base de datos no estamos solos y suelen existir otras bases de datos de otros sistemas, hacer las cosas bien implica que nuestro sistema no sea el que quieran dar de baja por causantes de problemas o que le ahorremos mucho dinero al cliente en compra de nuevo hardware para que nuestro proceso de cursores dure en lugar de 7hs unas 3hs J

La idea es que piensen en resolver los problemas sin el uso de cursores y que cada vez que implementen uno piensen lo mal que le están haciendo al servidor de base de datos por ende a la aplicación en general. Si algo no sale sin cursores vuélvanlo a pensar o consulten en las News de MS donde siempre intentaremos ayudarlos y mas si se trata de eliminar un cursor J