usar - ¿Por qué se considera una mala práctica utilizar cursores en SQL Server?
recorrer tabla sql server sin usar cursores (11)
Conocí algunas razones de rendimiento en los 7 días de SQL, pero ¿aún existen los mismos problemas en SQL Server 2005? Si tengo un conjunto de resultados en un procedimiento almacenado sobre el que quiero actuar individualmente, ¿los cursores siguen siendo una mala elección? Si es así, ¿por qué?
@ Daniel P -> no necesitas usar un cursor para hacerlo. Puede usar fácilmente la teoría basada en conjuntos para hacerlo. Por ejemplo: con Sql 2008
DECLARE @commandname NVARCHAR(1000) = '''';
SELECT @commandname += ''truncate table '' + tablename + ''; '';
FROM tableNames;
EXEC sp_executesql @commandname;
simplemente hará lo que dijo anteriormente. Y puede hacer lo mismo con Sql 2000 pero la sintaxis de la consulta sería diferente.
Sin embargo, mi consejo es evitar los cursores tanto como sea posible.
Gayam
A veces, la naturaleza del procesamiento que necesita realizar requiere cursores, aunque, por motivos de rendimiento, siempre es mejor escribir la operación (es) usando lógica basada en conjuntos, si es posible.
Yo no llamaría "mala práctica" el uso de cursores, pero consumen más recursos en el servidor (que un enfoque basado en conjuntos equivalente) y la mayoría de las veces no son necesarios. Dado que, mi consejo sería considerar otras opciones antes de recurrir a un cursor.
Hay varios tipos de cursores (solo hacia adelante, estático, conjunto de teclas, dinámico). Cada uno tiene diferentes características de rendimiento y gastos indirectos asociados. Asegúrese de usar el tipo de cursor correcto para su operación. Reenviar solo es el predeterminado.
Un argumento para usar un cursor es cuando necesita procesar y actualizar filas individuales, especialmente para un conjunto de datos que no tiene una buena clave única. En ese caso, puede usar la cláusula FOR UPDATE al declarar el cursor y procesar actualizaciones con UPDATE ... WHERE CURRENT OF.
Tenga en cuenta que los cursores "del lado del servidor" solían ser populares (de ODBC y OLE DB), pero ADO.NET no los admite, y AFAIK nunca lo hará.
Creo que los cursores obtienen un mal nombre porque los novatos de SQL los descubren y piensan "¡Hey, for loop! ¡Sé cómo usarlos!" y luego continúan usándolos para todo.
Si los usa para lo que están diseñados, no puedo encontrarle la culpa a eso.
El problema básico, creo, es que las bases de datos están diseñadas y ajustadas para operaciones basadas en conjuntos: selecciona, actualiza y elimina grandes cantidades de datos en un solo paso rápido basado en las relaciones en los datos.
El software en memoria, por otro lado, está diseñado para operaciones individuales, por lo que hacer un bucle sobre un conjunto de datos y potencialmente realizar diferentes operaciones en cada elemento en serie es lo mejor.
El bucle no es lo que la base de datos o la arquitectura de almacenamiento están diseñados, e incluso en SQL Server 2005, no obtendrá rendimiento en ningún lugar cercano si obtiene los datos básicos establecidos en un programa personalizado y realiza el bucle en la memoria , usando objetos / estructuras de datos que sean lo más livianos posible.
Hay muy, muy pocos casos donde el uso de un cursor está justificado. Casi no hay casos en que supere una consulta relacional basada en conjuntos. A veces es más fácil para un programador pensar en términos de bucles, pero el uso de la lógica establecida, por ejemplo, para actualizar una gran cantidad de filas en una tabla, dará como resultado una solución que no solo tiene muchas menos líneas de código SQL, pero eso corre mucho más rápido, a menudo varios órdenes de magnitud más rápido.
Incluso el cursor de avance rápido en Sql Server 2005 no puede competir con las consultas basadas en conjuntos. El gráfico de la degradación del rendimiento a menudo comienza a parecerse a una operación n ^ 2 en comparación con la basada en conjuntos, que tiende a ser más lineal a medida que el conjunto de datos crece mucho.
Hay un trabajo alrededor de los cursores que utilizo cada vez que necesito uno.
Creo una variable de tabla con una columna de identidad.
inserte todos los datos que necesito para trabajar en él.
Luego haga un bloque while con una variable de contador y seleccione los datos que deseo de la variable de tabla con una instrucción select donde la columna de identidad coincida con el contador.
De esta manera no bloqueo nada y uso mucha menos memoria y es seguro, no perderé nada con una corrupción de memoria o algo así.
Y el código de bloque es fácil de ver y manejar.
Este es un ejemplo simple:
DECLARE @TAB TABLE(ID INT IDENTITY, COLUMN1 VARCHAR(10), COLUMN2 VARCHAR(10))
DECLARE @COUNT INT,
@MAX INT,
@CONCAT VARCHAR(MAX),
@COLUMN1 VARCHAR(10),
@COLUMN2 VARCHAR(10)
SET @COUNT = 1
INSERT INTO @TAB VALUES(''TE1S'', ''TE21'')
INSERT INTO @TAB VALUES(''TE1S'', ''TE22'')
INSERT INTO @TAB VALUES(''TE1S'', ''TE23'')
INSERT INTO @TAB VALUES(''TE1S'', ''TE24'')
INSERT INTO @TAB VALUES(''TE1S'', ''TE25'')
SELECT @MAX = @@IDENTITY
WHILE @COUNT <= @MAX BEGIN
SELECT @COLUMN1 = COLUMN1, @COLUMN2 = COLUMN2 FROM @TAB WHERE ID = @COUNT
IF @CONCAT IS NULL BEGIN
SET @CONCAT = ''''
END ELSE BEGIN
SET @CONCAT = @CONCAT + '',''
END
SET @CONCAT = @CONCAT + @COLUMN1 + @COLUMN2
SET @COUNT = @COUNT + 1
END
SELECT @CONCAT
Los comentarios anteriores acerca de que SQL es un entorno basado en conjunto son todos ciertos. Sin embargo, hay ocasiones en que las operaciones fila por fila son útiles. Considere una combinación de metadatos y dynamic-sql.
Como un ejemplo muy simple, digamos que tengo más de 100 registros en una tabla que definen los nombres de las tablas que quiero copiar / truncar / lo que sea. ¿Cuál es el mejor? ¿Hardcoding el SQL para hacer lo que necesito? ¿O iterar a través de este conjunto de resultados y usar dynamic-SQL (sp_executesql) para realizar las operaciones?
No hay forma de lograr el objetivo anterior utilizando SQL basado en conjuntos.
Entonces, ¿para usar cursores o un ciclo while (pseudocuros)?
Los cursores SQL están bien siempre que use las opciones correctas:
INSENSITIVE hará una copia temporal de su conjunto de resultados (lo que le ahorrará tener que hacer esto usted mismo para su pseudo-cursor).
READ_ONLY se asegurará de que no se mantengan bloqueos en el conjunto de resultados subyacente. Los cambios en el conjunto de resultados subyacente se reflejarán en las búsquedas posteriores (lo mismo que si se obtiene el TOP 1 de su pseudo-cursor).
FAST_FORWARD creará un cursor optimizado solo de avance, de solo lectura.
Lea sobre las opciones disponibles antes de decidir que todos los cursores son malvados.
Los cursores generalmente no son la enfermedad, sino un síntoma: no se usa el enfoque basado en conjuntos (como se menciona en las otras respuestas).
No entender este problema, y simplemente creer que evitar el cursor "malvado" lo resolverá, puede empeorar las cosas.
Por ejemplo, reemplazar la iteración del cursor por otro código iterativo, como mover datos a tablas temporales o variables de tabla, para recorrer las filas de la siguiente manera:
SELECT * FROM @temptable WHERE Id=@counter
o
SELECT TOP 1 * FROM @temptable WHERE Id>@lastId
Tal enfoque, como se muestra en el código de otra respuesta, empeora las cosas y no soluciona el problema original. Es una programación anti-patrón llamada culto a la carga : ¡no saber POR QUÉ algo es malo y por lo tanto implementar algo peor para evitarlo! Hace poco cambié ese código (usando una #temptable y ningún índice en identidad / PK) de nuevo a un cursor, y la actualización de un poco más de 10000 filas tomó solo 1 segundo en lugar de casi 3 minutos. Todavía carezco de un enfoque basado en el sistema (siendo el mal menor), pero lo mejor que pude hacer en ese momento.
Otro síntoma de esta falta de comprensión puede ser lo que a veces llamo "una enfermedad objeto": aplicaciones de bases de datos que manejan objetos únicos a través de capas de acceso a datos o mapeadores relacionales de objetos. Por lo general, codifica como:
var items = new List<Item>();
foreach(int oneId in itemIds)
{
items.Add(dataAccess.GetItemById(oneId);
}
en lugar de
var items = dataAccess.GetItemsByIds(itemIds);
El primero normalmente inundará la base de datos con toneladas de SELECT, un viaje de ida y vuelta para cada uno, especialmente cuando los árboles / gráficos de objetos entren en juego y se produzca el infame problema SELECT N + 1.
Este es el lado de la aplicación de no entender las bases de datos relacionales y el enfoque basado en conjuntos, al igual que los cursores cuando se usa código de base de datos de procedimientos, como T-SQL o PL / SQL.
Los cursores tienen su lugar, sin embargo, creo que es principalmente porque a menudo se usan cuando una única declaración de selección sería suficiente para proporcionar agregación y filtrado de resultados.
Al evitar los cursores, SQL Server optimiza más el rendimiento de la consulta, muy importante en sistemas más grandes.
Porque los cursores toman memoria y crean bloqueos.
Lo que realmente está haciendo es intentar forzar a la tecnología basada en conjuntos a una funcionalidad no basada en conjuntos. Y, para ser justos, debo señalar que los cursores sí tienen utilidad, pero están mal visto porque muchas personas que no están acostumbradas a usar soluciones basadas en conjuntos usan cursores en lugar de descifrar la solución basada en conjuntos.
Pero cuando abre un cursor, básicamente está cargando esas filas en la memoria y bloqueándolas, creando bloques potenciales. Luego, mientras recorre el cursor, está realizando cambios en otras tablas y manteniendo toda la memoria y bloqueos del cursor abiertos.
Todo lo cual tiene el potencial de causar problemas de rendimiento para otros usuarios.
Entonces, como regla general, los cursores son mal vistos. Especialmente si esa es la primera solución para resolver un problema.
SQL es un lenguaje basado en conjunto: eso es lo que mejor hace.
Creo que los cursores siguen siendo una mala opción a menos que comprendas lo suficiente sobre ellos como para justificar su uso en circunstancias limitadas.
Otra razón por la que no me gustan los cursores es la claridad. El bloque del cursor es tan feo que es difícil de usar de una manera clara y efectiva.
Dicho todo esto, hay algunos casos en los que el cursor es realmente el mejor; simplemente no son los casos en que los principiantes quieren usarlos.