Los cursores pueden parecer accesos directos a un desarrollador. Cuando tiene un trabajo complejo que realizar y necesita manipular las filas de una tabla, la forma más rápida puede parecer iterar a través de las filas una por una utilizando un cursor Transact-SQL. Después de todo, dado que tiene que iterar a través de estructuras de datos en su propio código en el lado del cliente, puede verse tentado a hacer lo mismo cuando se trata de datos de SQL Server., Pero iterar a través de datos usando cursores Transact-SQL a menudo no escala bien, y espero convencerlos de que tampoco es una buena práctica de diseño o arquitectura.

una experiencia de Cursor

traigo esto porque hace unos meses, tuve que lidiar con el script Transact-SQL de un proveedor que actualizó su componente de base de datos a una nueva versión de la aplicación del proveedor. Diseñaron el script para pivotar una tabla muy grande y almacenar los datos relevantes en una nueva tabla horizontalmente, como cadenas concatenadas., El proveedor quería mejorar el rendimiento haciendo la tabla más pequeña, por lo que decidió almacenar los datos de detalle horizontalmente, como cadenas delimitadas por comas para cada ID padre. La aplicación cliente podía consultar las cadenas delimitadas por comas resultantes más rápido que obtener cada una de ellas como filas individuales, y en el contexto, el cambio tenía sentido y mejoró el rendimiento de la aplicación.

sin embargo, el script Transact-SQL del proveedor para pivotar los datos durante la actualización tardó 16 horas en ejecutarse en una máquina de prueba, y el cliente no podía permitirse más de unas pocas horas de tiempo de inactividad para la actualización., Cuando examinamos el script del proveedor, vimos que el desarrollador había codificado el proceso de pivote en dos pasos: un cursor para iterar a través de todos los ID de la tabla principal para construir una tabla preformateada en blanco, y luego otro script para concatenar las cadenas, nuevamente usando un cursor.

mediante el uso de un enfoque basado en conjuntos, pudimos reducir el tiempo de procesamiento de más de 16 horas a menos de cinco minutos. Seguimos la estrategia original del desarrollador, construyendo la tabla en blanco usando instrucciones SELECT, y reducimos el tiempo para ese paso a menos de dos minutos., Luego concatenamos las cadenas usando una instrucción UPDATE, ejecutada por ID padre. Nuestra iteración a través de los identificadores principales utilizó un bucle WHILE y terminó en menos de tres minutos.

la inevitabilidad de la iteración

muchos accesos a los datos de la base de datos deben ser iterativos de alguna manera para preparar los datos para una mayor manipulación. Incluso el motor de SQL Server itera a través de los datos cuando escanea o une datos utilizando los diversos tipos de uniones disponibles para él. Puede ver esto cuando examina el plan de consultas de SQL Server para una consulta que devuelve muchas filas de un conjunto de datos grande., Para una unión, lo más común es ver un bucle anidado, pero a veces también una combinación o una unión hash. Para consultas más simples, puede ver un análisis de índice agrupado o no agrupado. Solo en los casos en los que SQL Server puede devolver una sola fila o un pequeño conjunto de filas, y la tabla tiene un índice apropiado, verá una búsqueda utilizando un índice.

piénselo: Microsoft ha optimizado y ajustado el motor de SQL Server durante años para iterar a través de sus datos disponibles de la manera más eficiente posible., Imagine, si tuviera el tiempo y estuviera dispuesto a gastar la energía, probablemente podría escribir accesos de bajo nivel a archivos de datos de bases de datos que serían bastante eficientes. Sin embargo, sería eficiente solo para la tarea individual frente a usted, y tendría que depurarla y podría tener que reescribirla por completo si cambiara el alcance de su acceso a los datos. Probablemente le tomaría años conseguir realmente el código completamente optimizado y generalizado, e incluso entonces no estaría cerca de la eficiencia del código dentro del motor de almacenamiento de SQL Server.,

entonces, ¿dónde está la ganancia en reinventar la rueda? Es solo porque el motor de SQL Server está tan bien optimizado y depurado, que es mejor dejar que haga la iteración por usted y aprovechar el extenso desarrollo y pruebas que ya está incrustado en la base de datos.

si observa sus tareas de procesamiento de datos más de cerca, creo que encontrará que realmente hay muy pocas ocasiones en las que se requieren cursores. En primer lugar, a menudo puede lograr su objetivo confiando en los comandos SQL basados en conjuntos en Transact-SQL e ignorando el orden de las filas de una tabla., En segundo lugar, los cursores Transact-SQL son solo una forma de iterar una tabla fila por fila. Si puede identificar de forma única cada fila de una tabla que debe iterar, puede usar un bucle WHILE en lugar de un cursor y, potencialmente, obtener un mejor rendimiento. Déjame guiarte a través de un ejemplo para mostrarte por qué.

comparar estrategias de iteración

suponga que puede identificar de forma única cada fila de una tabla porque la tabla tiene una clave única o un grupo único de columnas., En un bucle WHILE, todo lo que necesita hacer es encontrar el valor más bajo de la condición única, y luego encontrar el siguiente valor más alto cada vez que itere. Este es un ejemplo de la producción de bases de datos de muestra de SQL Server 2005 AdventureWorks.TransactionHistory table (en inglés). Tiene un índice agrupado en la clave primaria, y el bucle while puede buscar en la fila cada vez.,

USE AdventureWorksGODECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int SET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory)WHILE @TransactionID IS NOT NULLBEGINSET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory WHERE TransactionID > @TransactionID)END

Aquí está el mismo bucle usando un cursor de avance rápido, que es el tipo más eficiente de cursor Transact-SQL para solo leer datos:

DECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int DECLARE AW_Cursor CURSOR FORWARD_ONLYFORSELECT TransactionID, TransactionType, QuantityFROM Production.TransactionHistory OPEN AW_Cursor FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @Quantity WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @QuantityEND CLOSE AW_Cursor DEALLOCATE AW_Cursor

en mi computadora portátil, después de ejecutarlo un par de veces para asegurarse de que los datos estén todos en caché, el bucle WHILE toma nueve segundos y el cursor toma 17 segundos. Sus propias duraciones pueden variar. Tenga en cuenta que a pesar de que el ejemplo realmente no hace nada con los datos, el bucle WHILE es más rápido. El cursor evidentemente añade más sobrecarga.,

el cursor también requiere comandos adicionales, que hacen que el código se vea desordenado. Sin entrar en los detalles de cómo funcionan los cursores, que Microsoft explica completamente en Microsoft SQL Server 2005 Books Online, tenga en cuenta que cuando utiliza un bucle WHILE, no hay ningún requisito para declarar, abrir, cerrar y desasignar nada. La lógica es más simple, e incluso puede actualizar las filas libremente en el camino. Para actualizar las filas usando el cursor, tendrá que cambiar el tipo de cursor.

incluso un bucle WHILE agrega la sobrecarga de iteración., Es posible que pueda reemplazarlo con un comando SELECT basado en set, o reemplazar cualquier actualización que desee hacer en su bucle con el comando UPDATE basado en set, y dejar la iteración al motor de SQL Server. Una simple instrucción SELECT para obtener los mismos datos que nuestro cursor y WHILE loop anterior tarda menos de 3 segundos, y devuelve las filas al cliente, lo que es más trabajo que los dos bucles anteriores.

SELECT *FROM Production.TransactionHistory

Esta selección se basa en SQL Server para iterar a través de los datos, y es de lejos el más rápido de los tres métodos de acceso a datos que hemos visto.,

de bolsas a conjuntos

a veces los cursores pueden parecer necesarios. Cuando simplemente debe recorrer los datos de la base de datos, fila por fila, en su orden físico, a veces solo funcionará un cursor. Esto ocurre más comúnmente cuando tiene filas duplicadas y no hay forma de identificar de forma única una fila determinada en la tabla. Estas tablas son bolsas, no conjuntos, de datos, ya que una ‘bolsa’ no elimina los valores duplicados, como lo hace un conjunto.

estas bolsas de datos generalmente ocurren cuando importa datos de una fuente externa y no puede confiar completamente en los datos., Por ejemplo, si nuestra tabla Historial de transacciones de AdventureWorks no tenía ningún grupo de columnas que pudiera llamar único o tenía filas duplicadas, podría pensar que debe usar un cursor.

sin embargo, siempre puede convertir una bolsa de filas en una tabla normalizada. Incluso si tiene filas duplicadas en una tabla, o ningún conjunto de columnas en las que pueda confiar para la singularidad, puede agregar una columna de identidad a la tabla y sembrar la identidad para comenzar a numerar con 1. Esto agrega una clave única a la tabla, lo que le permite usar un bucle WHILE en lugar de un cursor., Una vez que tenga una clave única, puede eliminar duplicados utilizando el comando Transact-SQL set-based UPDATE.

la API lógica para los datos de la base de datos

Usando operaciones de base de datos es mejor que iterar los datos usted mismo de al menos dos maneras.

primero, los comandos SQL basados en conjuntos son más eficientes porque está utilizando el motor altamente optimizado de SQL Server para realizar su iteración. Si itera a través de los datos usted mismo, no está utilizando el motor de almacenamiento de SQL Server de manera óptima. En su lugar, lo está salpicando con comandos para recuperar solo una fila a la vez., Cada vez que solicite una sola fila, su comando debe pasar por el optimizador de SQL Server antes de que pueda llegar al motor de almacenamiento, y termina sin usar el código optimizado del motor de almacenamiento de SQL Server. Si iteró usted mismo, también está confiando en información física extraña sobre la tabla, es decir, el orden de las filas, al procesar los datos. Los comandos Set-base Transact-SQL SELECT, UPDATE y DELETE le permiten ignorar el orden de las filas y solo afectarlas en función de las características de los datos, y son más rápidos.,

en segundo lugar, los comandos basados en conjuntos son más lógicos porque pensar en datos en Conjuntos lo abstrae de detalles extraños que están más preocupados por cómo se ordenan realmente los datos. De hecho, los comandos basados en conjuntos, como seleccionar, Actualizar y eliminar, cuando se aplican a las tablas directamente y no en un cursor o bucle WHILE, lo acercan lógicamente a sus datos, precisamente porque puede ignorar el orden de los datos.,

Aquí hay otra forma de pensar sobre este segundo punto: al igual que los procedimientos almacenados son la API más natural para que las aplicaciones interactúen con SQL Server mediante programación, los comandos SQL basados en conjuntos son la API adecuada para acceder a datos relacionales. Los procedimientos almacenados desacoplan su aplicación de los internos de la base de datos y son más eficientes que las consultas ad hoc. Del mismo modo, los comandos SQL set-base dentro de Transact-SQL le proporcionan una interfaz lógica para sus datos relacionales, y son más eficientes porque confía en el motor de almacenamiento de SQL Server para iterar a través de los datos.,

la conclusión no es que iterar a través de datos es malo. En realidad, a menudo es inevitable. Más bien, el punto es, deje que el motor de almacenamiento lo haga por usted y confíe en su lugar en la interfaz lógica de los comandos Transact-SQL basados en conjuntos. Creo que encontrará pocas situaciones en las que deba usar un cursor Transact-SQL.