usar - reemplazar cursores sql server

¿Por qué las consultas basadas en conjuntos relacionales son mejores que los cursores? (11)

Al escribir consultas de bases de datos en algo como TSQL o PLSQL, a menudo tenemos la opción de iterar sobre filas con un cursor para realizar la tarea, o crear una sola declaración de SQL que realice el mismo trabajo de una sola vez.

Además, tenemos la opción de simplemente recuperar un gran conjunto de datos en nuestra aplicación y luego procesarlos fila por fila, con C # o Java o PHP o lo que sea.

¿Por qué es mejor usar consultas basadas en conjuntos? ¿Cuál es la teoría detrás de esta elección? ¿Cuál es un buen ejemplo de una solución basada en cursor y su equivalente relacional?

Además de lo anterior, "dejar que el DBMS haga el trabajo" (que es una gran solución), existen otras buenas razones para dejar la consulta en el DBMS:

Es (subjetivamente) más fácil de leer. Al mirar el código más tarde, ¿preferiría intentar analizar un procedimiento almacenado complejo (o código del lado del cliente) con bucles y cosas, o preferiría ver una declaración SQL conciso?
Evita los viajes redondos de la red. ¿Por qué meter toda esa información al cliente y luego empujar más hacia atrás? ¿Por qué destruir la red si no es necesario?
Es un desperdicio Su DBMS y servidor (es) de aplicación necesitarán almacenar en búfer algunos / todos esos datos para que funcionen en él. Si no tienes memoria infinita es probable que coloques otros datos; ¿por qué expulsar cosas posiblemente importantes de la memoria para amortiguar un conjunto de resultados que en su mayoría es inútil?
¿Por qué no? Usted compró (o está usando) un DBMS muy confiable y muy rápido. ¿Por qué no lo usarías?

Como se ha dicho, la base de datos está optimizada para operaciones de conjunto. Literalmente, los ingenieros se sentaron y depuraron / sintonizaron esa base de datos durante largos períodos de tiempo. Las posibilidades de que los optimices son bastante escasas. Hay muchos tipos de trucos divertidos con los que puede jugar si tiene que trabajar con un conjunto de datos como lecturas / escrituras de lotes de discos, caché, multihilo. Además, algunas operaciones tienen un alto costo general, pero si lo hace a un montón de datos a la vez, el costo por pieza de datos es bajo. Si solo está trabajando una fila a la vez, muchos de estos métodos y operaciones simplemente no pueden suceder.

Por ejemplo, solo mira la forma en que se une la base de datos. Al mirar los planes de explicación, puede ver varias formas de hacer combinaciones. Lo más probable es que con un cursor vaya fila por fila en una tabla y luego seleccione los valores que necesita de otra tabla. Básicamente es como un bucle anidado solo sin la rigidez del bucle (que probablemente se haya compilado en el lenguaje de la máquina y súper optimizado). SQL Server por sí solo tiene muchas formas de unirse. Si las filas están ordenadas, usará algún tipo de algoritmo de combinación, si una tabla es pequeña, puede convertir una tabla en una tabla de búsqueda hash y hacer la unión al realizar búsquedas O (1) de una tabla en la tabla de búsqueda. Hay una serie de estrategias de combinación que tienen muchos DBMS que le ganarán buscando valores desde una tabla en un cursor.

Solo mira el ejemplo de crear una tabla de búsqueda hash. Construir la tabla probablemente sea m operaciones si está uniendo dos tablas una de longitud n y una de longitud m donde m es la tabla más pequeña. Cada búsqueda debe ser de tiempo constante, entonces eso es n operaciones. así que, básicamente, la eficiencia de una combinación hash está alrededor de m (configuración) + n (búsquedas). Si lo haces tú mismo y no asumes búsquedas / índices, entonces para cada una de las n filas tendrás que buscar m registros (en promedio equivale a m / 2 búsquedas). Así que, básicamente, el nivel de operaciones va desde m + n (uniendo un montón de registros a la vez) a m * n / 2 (haciendo búsquedas a través de un cursor). También las operaciones son simplificaciones. Dependiendo del tipo de cursor, ir a cada fila de un cursor puede ser lo mismo que hacer otra selección desde la primera tabla.

Los bloqueos también te matan. Si tiene cursores en una tabla, está bloqueando filas (en el servidor SQL esto es menos severo para los cursores estáticos y de avance solamente ... pero la mayoría del código de cursor que veo abre un cursor sin especificar ninguna de estas opciones). Si realiza la operación en un conjunto, las filas seguirán bloqueadas, pero por un período de tiempo menor. Además, el optimizador puede ver lo que está haciendo y puede decidir que es más eficaz bloquear toda la tabla en lugar de un montón de filas o páginas. Pero si vas línea por línea, el optimizador no tiene idea.

La otra cosa es que he oído que en el caso de Oracle está súper optimizado para hacer operaciones con el cursor, por lo que no está cerca de la misma penalidad para las operaciones basadas en conjuntos contra los cursores en Oracle que en SQL Server. No soy un experto en Oracle, así que no puedo decirlo con certeza. Pero más de una persona de Oracle me ha dicho que los cursores son mucho más eficientes en Oracle. Entonces, si sacrificaste a tu hijo primogénito por Oracle, es posible que no tengas que preocuparte por los cursores, consulta con tu DBA de Oracle local altamente pagado :)

Creo que la verdadera respuesta es, como todos los enfoques en programación, que depende de cuál sea mejor. En general, un lenguaje basado en conjuntos será más eficiente, porque para eso fue diseñado. Hay dos lugares donde el cursor tiene una ventaja:

Está actualizando un gran conjunto de datos en una base de datos donde el bloqueo de filas no es aceptable (quizás durante las horas de producción). Una actualización basada en un conjunto tiene la posibilidad de bloquear una tabla durante varios segundos (o minutos), donde un cursor (si está escrito correctamente) no lo hace. El cursor puede recorrer las filas actualizando una a la vez y no tiene que preocuparse por afectar a nada más.
La ventaja de usar SQL es que la mayor parte del trabajo para la optimización es manejada por el motor de la base de datos en la mayoría de los casos. Con los motores db de clase empresarial, los diseñadores han hecho todo lo posible para asegurarse de que el sistema sea eficiente en el manejo de los datos. El inconveniente es que SQL es un lenguaje basado en conjunto. Debe poder definir un conjunto de datos para usarlo. Aunque esto suena fácil, en algunas circunstancias no lo es. Una consulta puede ser tan compleja que los optimizadores internos en el motor no pueden crear una ruta de ejecución de manera efectiva, y adivine qué sucede ... su caja súper potente con 32 procesadores usa un solo hilo para ejecutar la consulta porque no sabe cómo hacer cualquier otra cosa, por lo que pierdes tiempo de procesador en el servidor de la base de datos, que generalmente solo tiene uno en lugar de múltiples servidores de aplicaciones (así que de vuelta a la razón 1, te encuentras con contenciones de recursos con otras cosas que necesitan ejecutarse en el servidor de la base de datos ) Con un lenguaje basado en filas (C #, PHP, JAVA, etc.), tienes más control sobre lo que sucede. Puede recuperar un conjunto de datos y forzarlo para que se ejecute de la manera que desee. (Separe los datos establecidos para que se ejecuten en varios hilos, etc.). La mayoría de las veces, no será tan eficiente como ejecutarlo en el motor de la base de datos, porque aún tendrá que acceder al motor para actualizar la fila, pero cuando tenga que hacer más de 1000 cálculos para actualizar una fila ( y digamos que tiene un millón de filas), un servidor de base de datos puede comenzar a tener problemas.

Creo que todo se reduce al uso de la base de datos está diseñado para ser utilizado. Los servidores de bases de datos relacionales están específicamente desarrollados y optimizados para responder mejor a las preguntas expresadas en la lógica establecida.

Funcionalmente, la penalización de los cursores variará enormemente de un producto a otro. Algunos (¿la mayoría?) Rdbmss se construyen al menos parcialmente sobre los motores isam. Si la pregunta es adecuada y la chapa es lo suficientemente fina, podría ser tan eficaz usar un cursor. Pero esa es una de las cosas con las que debe familiarizarse íntimamente, en términos de su marca de DBM, antes de intentarlo.

En pocas palabras, en la mayoría de los casos, es más rápido / más fácil dejar que la base de datos lo haga por usted.

El propósito de la base de datos en la vida es almacenar / recuperar / manipular datos en formatos establecidos y ser realmente rápido. Su código VB.NET/ASP.NET probablemente no sea tan rápido como un motor de base de datos dedicado. Aprovechar esto es un uso inteligente de los recursos.

La idea detrás de preferir hacer el trabajo en consultas es que el motor de la base de datos puede optimizar reformulándolo. También es por eso que desea ejecutar EXPLAIN en su consulta, para ver qué está haciendo realmente el db. (por ejemplo, aprovechando los índices, el tamaño de las tablas y, a veces, incluso el conocimiento sobre la distribución de los valores en las columnas).

Dicho esto, para obtener un buen rendimiento en su caso concreto real, es posible que tenga que doblegar o romper las reglas.

Ah, otra razón podría ser restricciones: Incrementar una columna única por una podría estar bien si se verifican las restricciones después de todas las actualizaciones, pero genera una colisión si se hace una por una.

La razón principal de la que soy consciente es que el motor puede optimizar las operaciones basadas en conjuntos al ejecutarlas en varios hilos. Por ejemplo, piense en un quicksort: puede separar la lista que está ordenando en varios "fragmentos" y ordenar cada uno por separado en su propio hilo. Los motores SQL pueden hacer cosas similares con grandes cantidades de datos en una consulta basada en conjuntos.

Cuando realiza operaciones basadas en cursor, el motor solo puede ejecutarse de forma secuencial y la operación debe ser de un solo hilo.

La respuesta REAL es obtener uno de los libros de EF Codd y repasar el álgebra relacional . A continuación, obtenga un buen libro sobre notación Big O. Después de casi dos décadas en TI esto es, en mi humilde opinión, una de las grandes tragedias del grado moderno de MIS o CS: muy pocos realmente estudian computación. Ya sabes ... la parte de "cálculo" de "computadora"? El lenguaje de consulta estructurado (y todos sus superconjuntos) es simplemente una aplicación práctica del álgebra relacional. Sí, los RDBMS tienen una administración optimizada de memoria y lectura / escritura, pero lo mismo podría decirse de los lenguajes de procedimiento. A medida que lo leí, la pregunta original no es sobre el IDE, el software, sino sobre la eficiencia de un método de cálculo vs. otro.

Incluso una rápida familiarización con la notación Big O comenzará a arrojar luz sobre por qué, cuando se trata de conjuntos de datos, la iteración es más costosa que una declaración declarativa.

Las consultas basadas en conjunto son (generalmente) más rápidas porque:

Tienen más información para el optimizador de consultas para optimizar
Pueden leer por lotes desde el disco
Hay menos registro involucrado para reversiones, registros de transacciones, etc.
Se toman menos bloqueos, lo que disminuye los gastos generales
La lógica basada en conjuntos es el foco de los RDBMS, por lo que se han optimizado mucho para ello (a menudo, a expensas del rendimiento de los procedimientos)

Sin embargo, extraer los datos al nivel intermedio para procesarlos puede ser útil, ya que elimina el costo de procesamiento del servidor de BD (que es lo más difícil de escalar, y normalmente también hace otras cosas). Además, normalmente no tiene los mismos gastos generales (o beneficios) en el nivel intermedio. Cosas como el registro transaccional, el bloqueo y el bloqueo integrados, etc., a veces son necesarios y útiles, otras son un desperdicio de recursos.

Un cursor simple con lógica de procedimiento vs. conjunto basado en un ejemplo (T-SQL) que asignará un código de área basado en la central telefónica:

--Cursor DECLARE @phoneNumber char(7) DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT PhoneNumber FROM Customer WHERE AreaCode IS NULL OPEN c FETCH NEXT FROM c INTO @phoneNumber WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @exchange char(3), @areaCode char(3) SELECT @exchange = LEFT(@phoneNumber, 3) SELECT @areaCode = AreaCode FROM AreaCode_Exchange WHERE Exchange = @exchange IF @areaCode IS NOT NULL BEGIN UPDATE Customer SET AreaCode = @areaCode WHERE CURRENT OF c END FETCH NEXT FROM c INTO @phoneNumber END CLOSE c DEALLOCATE c END --Set UPDATE Customer SET AreaCode = AreaCode_Exchange.AreaCode FROM Customer JOIN AreaCode_Exchange ON LEFT(Customer.PhoneNumber, 3) = AreaCode_Exchange.Exchange WHERE Customer.AreaCode IS NULL

Querías algunos ejemplos de la vida real. Mi compañía tenía un cursor que tomó más de 40 minutos para procesar 30,000 registros (y hubo momentos en los que necesitaba actualizar más de 200,000 registros). Tardó 45 segundos para hacer la misma tarea sin el cursor. En otro caso, eliminé un cursor y envié el tiempo de procesamiento de más de 24 horas a menos de un minuto. Una era una inserción que usaba la cláusula de valores en lugar de una selección y la otra era una actualización que usaba variables en lugar de una combinación. Una buena regla empírica es que si se trata de una inserción, actualización o eliminación, debe buscar una manera de realizar la tarea basada en conjuntos.

Los cursores tienen sus usos (o el código no sería el suyo en primer lugar), pero deberían ser extremadamente raros cuando se consulta una base de datos relacional (excepto Oracle que está optimizado para usarlos). Un lugar donde pueden ser más rápidos es cuando se realizan cálculos basados en el valor del registro precedente (totales acumulados). BUt incluso eso debe ser probado.

Otro caso limitado de usar un cursor es hacer un procesamiento por lotes. Si intenta hacer demasiado de una vez en forma de conjunto, puede bloquear la tabla a otros usuarios. Si tiene un conjunto realmente grande, puede ser mejor dividirlo en inserciones, actualizaciones o eliminaciones más pequeñas basadas en conjuntos que no mantengan el bloqueo demasiado tiempo y luego ejecutar los conjuntos con un cursor.

Un tercer uso de un cursor es ejecutar procesos almacenados del sistema a través de un grupo de valores de entrada. Como esto se limita a un conjunto generalmente pequeño y nadie debe meterse con los procesos del sistema, esto es aceptable para un administrador. No recomiendo hacer lo mismo con un proceso almacenado creado por el usuario para procesar un lote grande y volver a usar el código. Es mejor escribir una versión basada en conjuntos que tenga un mejor rendimiento, ya que el rendimiento debería prevalecer sobre la reutilización de códigos en la mayoría de los casos.

el conjunto basado se realiza en un cursor de operación tantas operaciones como el conjunto de filas del cursor