MTBASE / SYBASE DE COLOMBIA
 
Búsqueda avanzada...
Versión para imprimir Versión para imprimir Enviar vínculo por e-mail Enviar por e-mail Ver atributos del documento Atributos del documento
 

Cursores: Recomendaciones sobre Rendimiento y Uso

¿Cómo el Uso de Cursores Puede Optimizar el Rendimiento ?

Los cursores son un mecanismo usado para accesar el resultado de una cláusula select registro a registro. Dado que los cursores usan un modelo diferente al usado por el lenguaje SQL nativo, estos hacen uso de la memoria y de bloqueos ("locks") de tal manera que implican un impacto en el rendimiento de las aplicaciones. Particularmente, a nivel de rendimiento el uso de cursores implica:

  • Bloqueos a nivel de página y de tabla.

  • Recursos de red.

  • Más líneas de procesamiento.

¿Qué es un cursor ?

Un cursor es un nombre simbólico asociado a una cláusula select. Esto hace posible la manipulación de registros, uno a la vez.

Programación SQL Nativo Vs. Orientada a Registro (Cursores)

El SQL no fue creado como un lenguaje orientado a nivel de registro; este fue concebido como un lenguaje orientado a conjuntos. Sybase Adaptive Server Enterprise es extremadamente eficiente cuando trabaja orientado a conjuntos (SQL Nativo). Los cursores fueron requeridos por el estándar ANSI-89 y cuando son necesarios son muy poderosos. Por supuesto, estos pueden tener un efecto negativo en el rendimiento.

Por ejemplo, la siguiente sentencia realiza una operación idéntica para todos los registros que cumplen la condición:

update titles
set contract = 1 
where type = 'business'

El optimizador de Sybase Adaptive Server Enterprise busca la manera más eficiente para ejecutar este update. En contraste, un cursor examinará cada registro y ejecutará un update por cada registro que cumpla la condición. La aplicación declara un cursor para una cláusula select, abre el cursor, recupera un registro, lo procesa, recupera el siguiente y así sucesivamente. La aplicación puede realizar cualquier operación basadas en los valores de la posición actual del cursor y puede ser mucho menos eficiente que operaciones realizadas sobre un conjunto de datos (SQL Nativo). Por supuesto, los cursores pueden proveer bastante flexibilidad, úselos cuando sea estrictamente necesario.

La siguiente figura muestra los pasos involucrados con el uso de cursores. La esencia del cursor está en la caja del medio, es decir, donde el usuario examina el registro y decide qué hacer basado en el valor de este.

Procesamiento de Cursores en ASE
Fig. 1 – Procesamiento de cursores en ASE

Cursores: Un ejemplo Simple

Aquí está un ejemplo de un cursor:

declare biz_book cursor
for select * from titles
where type = 'business'
go
open biz_book
go
fetch biz_book
go
/* Se examina el registro recuperado
** y se ejecutan ciertas tareas con el valor
**del cursor, se repite el fetch hasta
**agotar los registros.
*/
close biz_book
go
deallocate cursor biz_book
go

Dependiendo del valor del registro, el usuario decide borrar el registro actual así:

delete titles where current of biz_book

o actualizar el registro actual, así:

update titles 
set title="The Rich Executive's Database Guide"
where current of biz_book

Comparando Rendimiento con y sin Cursores

Esta sección examina el rendimiento de un procedimiento almacenado escrito de dos maneras diferentes:

  • Sin usar cursores. Este procedimiento recorre la tabla tres veces cambiando el precio para cada libro.

  • Con un cursor. Este procedimiento realiza una sola pasada a través de la tabla. En ambos casos la tabla titles tiene un índice único sobre title_id.

Sin usar Cursores

/* Incrementa el precio de los libro de acuerdo a:
** 
**Si el precio es <= $30, increméntelo en un 20%
**Si el precio es > $30 y <= $60, increméntelo en un 10%
** Si el precio es > $60, increméntelo en un 5%
**
** Todos los cambios se realizaran bajo una transacción.
*/
create procedure increase_price
as
/* Comienzo transacción/
begin transaction
/* Primer Update > $60 */
update titles
set price = price * 1.05
where price > $60
/*Siguiente actualización precio entre $30 y $60 */
update titles 
set price = price * 1.10 
where price > $30 and price <= $60
/* y finalmente precios <= $30 */
update titles 
set price = price * 1.20
where price <= $30
/* Confirme la transacción */ 
commit transaction
return

Usando Cursores

El procedimiento realiza el mismo cambio en la tabla, pero usa un cursor. Cada registro es recuperado, examinado y actualizado. Un candado es colocado en la página correspondiente al registro en cuestión. Por supuesto, como lo indica el comentario, cada update realiza un commit.

/* Cada Update realiza un commit.*/
create procedure increase_price_cursor
as
declare @price money
/* declara un cursor para el select de la tabla titles */
declare curs cursor for 
select price 
from titles 
for update of price
/* Abre el cursor */
open curs
/* fetch the first row */
fetch curs into @price
/* Ahora el loop, procesara todos los registros
** @@sqlstatus = 0 significa fetch exitoso.
** @@sqlstatus = 1 significa error en fetch previo
** @@sqlstatus = 2 son hay mas regsutros.
*/
while (@@sqlstatus != 2)
begin 
/* Verifica errores */
if (@@sqlstatus = 1)
begin
    print "Error en increase_price"
    return
end
/* Ajuste del precio de acuerdo al criterio */
if @price > $60
    select @price = @price * 1.05
else
    if @price > $30 and @price <= $60
        select @price = @price * 1.10
    else
        if @price <= $30 
            select @price = @price * 1.20
/* Ahora , Update del registro */
update titles
set price = @price
where current of curs
/* Recupera el siguiente registro/
fetch curs into @price
end
/* Cierra el cursor y termina */
close curs
return

¿Cuál procedimiento piensa usted que tendrá mejor rendimiento?

Comparación de Rendimiento Programando con y sin Cursores

La tabla siguiente muestra las estadísticas de rendimiento con una tabla de 5000 registros. Observe que el procedimiento sin cursor realiza tres recorridos sobre la tabla ("table scan"), pero es mejor en términos de tiempo de respuesta pues sólo gasta 2 minutos para completar la operación, mientras que el procedimiento con cursor, realiza un sólo recorrido sobre la tabla pero invierte más del doble del tiempo para completar la misma operación.

Procedimiento

Método de Acceso

Tiempo

increase_price

Utiliza 3 "table scan"

2 minutos

increase_price_cursor

Utiliza un "table scan" del cursor

5 minutos

Los tiempos de respuesta pueden variar drásticamente si tenemos una red congestionada, muchos usuarios de base de datos y/o muchos usuarios sobre la misma tabla en donde se ejecutó el update.

Explicación de Rendimiento: Programando con y sin Cursores

Adicionalmente, el uso de cursores involucra mucha más actividad en la red de lo que sería necesario si se usara programación convencional, sin uso de cursores. Las aplicaciones que usan cursores necesitan comunicarse con Adaptive Server Enterprise por cada registro arrojado por la sentencia. Esta es la razón por la que el código del cursor toma más tiempo para completarse que el código que recorre la tabla las tres veces.

Cuando los cursores son absolutamente necesarios, por su puesto, deben ser usados. Pero estos afectaran el rendimiento del sistema.

El uso de cursores:

  • Usa bloqueo a nivel de pagina y/o tabla.

  • Requiere mayores recursos de red.

  • Produce un sobre costo a nivel de líneas de procesamiento.

Use los cursores sólo si son necesarios. Si existe una solución usando SQL nativo, úsela, puede ser preferible así realice múltiples recorridos de tablas.

¿Cuando usar Cursores?

Los cursores son muy poderosos pero pueden afectar el rendimiento. Si usted los usa los candados se aplicarán cuando se abre el cursor. Por ese motivo, deberán ser cerrados tan pronto como sea posible.

Los cursores pueden degradar el rendimiento por que ellos involucran:

  • Un incremento en los bloqueos.

  • Incremento en tráfico de red.

  • Un sobre costo en procesamiento.

Sugerencias de Rendimiento para el uso de Cursores

  • Optimice los select usados en la creación de los cursores.

  • Siempre que sea posible trate al máximo de usar el comando union o union all en vez de la cláusula or o la cláusula in.

  • Sea explícito en el comando declare.

  • Especifique los nombres de las columnas en la cláusula for update.

  • Tenga en cuenta la cláusula shared.

  • Use la instrucción set cursor rows.

  • Mantenga los cursores abiertos mientras se ejecuta el commit y/o el rollback.

  • Si abre muchos cursores, hágalo en la misma conexión.

Optimice los select usados en la creación de los cursores

Una sentencia select se optimiza de manera muy diferente dentro de un cursor que fuera de este. No es equivalente analizar el comportamiento de la sentencia select y luego introducir dicha sentencia dentro de la creación del cursor. Use los comandos set show plan y set statistics i/o una vez creado el cursor con la sentencia select en cuestión para corroborar el correcto uso de índices, operaciones de lectura/escritura, etc.

Siempre que sea posible trate al máximo de usar el comando union o union all en vez de la cláusula OR o la cláusula IN

Los cursores no pueden usar los índices de las columnas que usan la estrategia OR. Las sentencias que usan la estrategia OR en comandos select normalmente usan recorridos de la tabla ("table scan") para ejecutar la sentencia. Si el cursor fue creado con la opción for update seguramente usara un índice único pues requiere acceso a cada registro de manera secuencial para evaluar la cláusula en cuestión.

Los cursores de solo lectura ("read-only"), cuando se crean usando la cláusula union, al momento de declararse son construidos sobre una tabla de trabajo ordenada para eliminar los duplicados. Esta tabla de trabajo es usada para hacer el recorrido del cursor ("fetch"). Esta situación puede ser muy benéfica en términos de rendimiento. Los cursores creados con la instrucción union all retornan los registros duplicados y por esto no requieren tablas de trabajo.

Sea explícito en el comando declare

Los cursores son de "solo lectura" ("read only") o de actualización ("for update"). Si usted no especifica el tipo de cursor el optimizador decidirá por usted. En la gran mayoría de casos decidirá que es un cursor para actualizar ("for update") y por este motivo colocará candados de update.

Especifique los nombres de las columnas en la cláusula for Update

Adaptive Server Enterprise coloca candados de update sobre las tablas implicadas en la cláusula from que contengan columnas mencionadas en la cláusula for update. Si en la cláusula for update no está incluido el nombre de la(las) columna(s) a actualizar, el optimizador decide colocar candados de update sobre todas la tablas que se mencionadas en la cáusula from.

La siguiente secuencia de comandos incluye el nombre de la columna en la cláusula for update:.

declare curs3 cursor
for
select 	au_lname, au_fname, price
from 	titles t, authors a, titleauthor ta
where advance <= $1000 
    and t.title_id = ta.title_id
    and a.au_id = ta.au_id
for update of price

Observe que la cláusua for update nombra la columna price. Esta columna pertenece a la tabla titles , por lo tanto, solo esta tabla tendrá candados de actualización. Si la instrucción fuese sólamente for update (sin el nombre de la columna) se colocaría un candado de actualización sobre todas las tablas de la cláusula from, es decir sobre titles, authors y titleauthors.

Observe que el hecho de incluir el nombre de la columna a actualizar en la cláusula for update permite mejorar el uso de los candados.

Tenga en cuenta la instrucción shared

Permite que otros clientes coloquen candados de actualización sobre la tabla. Veamos un ejemplo:

declare xyz cursor
for select title, title_id
from titles shared
where title_id like "BU%"
For read only

Use la instrucción set cursor rows

El SQL estándar permite recuperar ("fetch") un registro a la vez. Usando set cursor rows es posible obligar a que se entreguen a la aplicación cliente más de un registro por instrucción fetch. Esto puede mejorar el rendimiento pues en una petición está trayendo más de un registro. La instrucción es:

set cursor rows numero_de_registros for nombre_del_cursor

en donde:

numero_de_registros: numero de elementos a recuperar por sentencia fetch.

nombre_del_cursor: cursor que se afectará con el comando set.

Mantenga los cursores abiertos mientras se ejecuta el commit y/o el rollback

El estándar ANSI cierra los cursores cuando se acaba la transacción. Sybase Adaptive Server Enterprise provee la opción close on endtran para aplicaciones que deben tener el comportamiento ANSI. Por defecto, esta opción esta en apagada ("off"). A menos que sea imprescindible, deje esta opción así para procurar mantener un buen tiempo de respuesta y buenos niveles de concurrencia.

Si usted debe cumplir el estándar ANSI, usted debe decidir cómo manejar los efectos en el servidor. ¿Debería usted realizar un gran numero de Updates y/o Deletes en una única transacción? ¿Debería tener muchas transacciones cortas para manejar estas operaciones?

Si usted decide mantener transacciones cortas, usted deberá estar cerrando y abriendo el cursor, aun que esto podría afectar el rendimiento del sistema. Si usted escoge realizar muchas tareas en una operación, esto causa problemas de concurrencia.

Si abre muchos cursores, hágalo en la misma conexión

Algunos desarrolladores simulan cursores usando dos o más conexiones. Una conexión ejecuta un select, mientras otra conexión ejecuta un update y/o delete sobre la misma tabla. Esto puede generar "abrazos mortales". Evite al máximo este tipo de estrategias.

Notas Finales

  • Este documento es una guía rápida para el uso de los cursores. De ninguna manera pretende convertirse en una camisa de fuerza.

  • Más información al respecto se puede encontrar en el manual "Sybase® SQL Server Performance and Tuning Guide" capitulo "Cursors and Performance"

  • Se recomienda colocar a prueba las nuevas técnicas, medir su impacto y luego implementarlas en producción.

Atributos del Documento
Resumen: El SQL tradicional es un lenguaje orientado al manejo de conjuntos, por este motivo no contempló en sus inicios la posibilidad de usar algún tipo de filosofía diferente. Los estándares ANSI y otras corrientes introdujeron el concepto de cursor dentro de la jerga del SQL. Este documento busca como objetivo principal, ser una guía práctica y rápida para entender su funcionamiento y procurar su buen uso a través de recomendaciones al respecto.
Código: 10034 Última Modificación: Jun 09, 2009
Temas: Administración Rendimiento Programación Tipo de Documento: Nota Técnica
Productos: Adaptive Server Enterprise Versión: 12.5.x
Plataformas: Todas las Plataformas Sistema Operativo: Todos los Sistemas Operativos
 
 Inicio   Sobre MTBASE   Sobre Sybase   Empleos en MTBASE   Mapa del Sitio   Mail Corp.   Aspectos Legales y Políticas de Privacidad