none
Evitar cursores en SQL RRS feed

  • Pregunta

  • Que tal comunidad, tengo un proceso del cual me gustaria saber, si hay alguna forma para NO hacer cursores.

    CARACTERISTICAS TECNICAS:

    • SQL Server 2014

    ESCENARIO:

    Se tienen 2 tablas: Inventario y Salidas, con la siguiente información:

    • Inventario

    CodigoInventario

    Articulo

    CantidadDisponible

    100

    TV

    10

    101

    TV

    15

    • Salidas

    CodigoSalida

    Articulo

    CantidadVendida

    1

    TV

    15

    2

    TV

    5

    Ahora bien, el proceso es simple, a lo que se le da salida, hay que descontarlo de la cantidad Disponible en Inventario, respetando lo siguiente:

    • El proceso debe ordenar ascendente por codigo de salida y deberá descontar ascendentemente por codigo de inventario, por lo tanto, existe otra tabla donde se guarda la relación Salida vs Inventario y como hizo el descuento:
    • y para este ejemplo seria: el 1er codigoSalida necesita descontar 15, por lo tanto tomará 10 del codigoInventario 100 y 5 del codigo del inventario 101, y el 2do codigoSalida necesita descontar 5, por lo tanto tomara 5 del codigoInventario 101, quedando la tablaRelacion de la siguiente forma:

    CodigoSalida

    CodigoInventario

    CantidadDescontada

    1

    100

    10

    1

    101

    5

    2

    101

    5

    Explicación: como el descuento debe ir en orden, el codigoSalida 1 tuvo que afectar al inventario 100 y 101 tomando 10 y 5 respectivamente y posterior el codigoSalida 2 simplemente tomo los 5 que necesitaba del unico inventario que quedaba con saldo (101).

    • y finalmente la tabla de Inventario, debera quedar así:

    CodigoInventario

    Articulo

    CantidadDisponible

    100

    TV

    0

    101

    TV

    5

    SOLUCION ACTUAL

    • Actualmente este proceso se hace con un cursor para ir insertando a la tablaRelacion e ir hacienda la actualización a la tabla de Inventario.
    • Sin embargo en este ejemplo pareceria que el cursor seria rápido, sin embargo, cuando hablamos de miles o quizá millones de registros tanto en Inventario como Salidas, los cursores se vuelven lentos... muy lentos, aunado a que si el proceso no llegara a encontrar CantidadDisponible en inventario, hará otros ciclos anidados para buscarlo en base a otras consideraciones, pero esto ya es otro tema, por lo pronto en este ciclo básico, me gustaria encontrar otro metodo que no sea cursores o ciclos

    Espero me haya explicado y quizá alguien tenga algun tip, para no usar cursor y hacer el proceso con un mayor rendimiento, ya que como ustedes sabrán estos cursores practicamente es un disparo a la cabeza de SQL Server.

    Gracias.

    viernes, 27 de abril de 2018 23:19

Respuestas

  • Hola gaunmanuel:

    Te he preparado una alternativa a ver si te vale para aplicar a tus datos.

    No esta terminada, porque no evalua todas las causisticas, pero creo que es suficiente, y trabajandola, un poquito seguro que te mejora el uso de los cursores.

    create table inventario (CODIGOINVENTARIO INT, ARTICULO VARCHAR(20), CANTIDAD INT)
    GO
    
    CREATE TABLE SALIDAS (CODIGOSALIDA INT IDENTITY (1,1), ARTICULO VARCHAR(20), CANTIDAD INT)
    GO
    CREATE TABLE RELACION (CODIGOSALIDA INT, CODIGOINVENTARIO INT, CANTIDADDESCONTADA INT)
    GO
    /*CREACION DE TABLAS DE EJEMPLO */
    /*FUNCION PARA EVITAR EL USO DEL CURSOR*/
    CREATE FUNCTION EXTRAEINVENTARIO (@ARTICULO VARCHAR(20), @CANTIDAD INT)
    RETURNS TABLE
    AS
    RETURN 
    SELECT TOT.FILA AS FILA, TOT.CANT,tot.SUMA, TOT.CODIGOINVENTARIO FROM (
    		SELECT ROW_NUMBER () OVER (ORDER BY CODIGOINVENTARIO) AS FILA, SUM(SUM(CANTIDAD)) OVER (ORDER BY CODIGOINVENTARIO) AS CANT, SUM(CANTIDAD) AS SUMA, CODIGOINVENTARIO 
    			FROM INVENTARIO WHERE ARTICULO = @ARTICULO 
    			AND CANTIDAD > 0
    		GROUP BY CODIGOINVENTARIO
    	) AS TOT
    	WHERE TOT.CANT <= @CANTIDAD 
    	/*ESTA PARTE DEVUELVE EL ACUMULADO INFERIOR O IGUAL A LO SOLICITADO */
    UNION 
    
    SELECT TOP (1) MIN(TOT.FILA) AS FILA, TOT.CANT,TOT.SUMA, TOT.CODIGOINVENTARIO FROM (
    		SELECT ROW_NUMBER () OVER (ORDER BY CODIGOINVENTARIO) AS FILA, SUM(SUM(CANTIDAD)) OVER (ORDER BY CODIGOINVENTARIO) AS CANT, SUM(CANTIDAd) AS SUMA,CODIGOINVENTARIO 
    			FROM INVENTARIO WHERE ARTICULO = @ARTICULO 
    			AND CANTIDAD > 0
    		GROUP BY CODIGOINVENTARIO
    	) AS TOT
    	WHERE TOT.CANT > @CANTIDAD
    	GROUP BY TOT.CANT, TOT.CODIGOINVENTARIO, TOT.SUMA;
    	/* ESTA PARTE DEVUELVE LA FILA SIGUIENTE */
    GO
    
    declare @articulo vARCHAR(20)='TV', @CANTIDAD INT =27;
    /*CREO QUE HAY QUE CHEQUEAR PRIMERO LA EXISTENCIA ANTES DE HACER LA SALIDA, PERO A MODO DE EJEMPLO VALE IGUAL */
    INSERT INTO SALIDAS(ARTICULO, CANTIDAD) VALUES (@ARTICULO, @CANTIDAD)
    	
    INSERT INTO RELACION SELECT SCOPE_IDENTITY(),/*RECOGEMOS EL ID INSERTADO EN LA SALIDA */
    
      O.CODIGOINVENTARIO, CASE WHEN O.CANT < @CANTIDAD THEN O.SUMA
    											ELSE @CANTIDAD-(O.CANT-O.SUMA)
    										 END AS CANT FROM dbo.EXTRAEINVENTARIO(@ARTICULO, @CANTIDAD) AS O;
    										 
    										 /* EN EL CASE PREPARAMOS LA COLUMNA CANTIDAD DESCONTADA 
    										 HAY QUE EVALUAR MÁS CAUSISTICAS, QUE LAS QUE TE HE PUESTO, PERO PARA QUE VER SI TE ENCAJA, ES SUFICIENTE, CREO
    										 */
     SELECT * fROM inventario
     SELECT *  FROM SALIDAS
     SELECT *  FROM RELACION

    Como ya es un poquito tarde, te lo dejo así.

    Espero te sirva, y puedas mejorarlo. Ya comentas.

    Saludos

    • Marcado como respuesta gaunmanuel lunes, 30 de abril de 2018 22:31
    sábado, 28 de abril de 2018 23:45

Todas las respuestas

  • Hola gaunmanuel:

    Te he preparado una alternativa a ver si te vale para aplicar a tus datos.

    No esta terminada, porque no evalua todas las causisticas, pero creo que es suficiente, y trabajandola, un poquito seguro que te mejora el uso de los cursores.

    create table inventario (CODIGOINVENTARIO INT, ARTICULO VARCHAR(20), CANTIDAD INT)
    GO
    
    CREATE TABLE SALIDAS (CODIGOSALIDA INT IDENTITY (1,1), ARTICULO VARCHAR(20), CANTIDAD INT)
    GO
    CREATE TABLE RELACION (CODIGOSALIDA INT, CODIGOINVENTARIO INT, CANTIDADDESCONTADA INT)
    GO
    /*CREACION DE TABLAS DE EJEMPLO */
    /*FUNCION PARA EVITAR EL USO DEL CURSOR*/
    CREATE FUNCTION EXTRAEINVENTARIO (@ARTICULO VARCHAR(20), @CANTIDAD INT)
    RETURNS TABLE
    AS
    RETURN 
    SELECT TOT.FILA AS FILA, TOT.CANT,tot.SUMA, TOT.CODIGOINVENTARIO FROM (
    		SELECT ROW_NUMBER () OVER (ORDER BY CODIGOINVENTARIO) AS FILA, SUM(SUM(CANTIDAD)) OVER (ORDER BY CODIGOINVENTARIO) AS CANT, SUM(CANTIDAD) AS SUMA, CODIGOINVENTARIO 
    			FROM INVENTARIO WHERE ARTICULO = @ARTICULO 
    			AND CANTIDAD > 0
    		GROUP BY CODIGOINVENTARIO
    	) AS TOT
    	WHERE TOT.CANT <= @CANTIDAD 
    	/*ESTA PARTE DEVUELVE EL ACUMULADO INFERIOR O IGUAL A LO SOLICITADO */
    UNION 
    
    SELECT TOP (1) MIN(TOT.FILA) AS FILA, TOT.CANT,TOT.SUMA, TOT.CODIGOINVENTARIO FROM (
    		SELECT ROW_NUMBER () OVER (ORDER BY CODIGOINVENTARIO) AS FILA, SUM(SUM(CANTIDAD)) OVER (ORDER BY CODIGOINVENTARIO) AS CANT, SUM(CANTIDAd) AS SUMA,CODIGOINVENTARIO 
    			FROM INVENTARIO WHERE ARTICULO = @ARTICULO 
    			AND CANTIDAD > 0
    		GROUP BY CODIGOINVENTARIO
    	) AS TOT
    	WHERE TOT.CANT > @CANTIDAD
    	GROUP BY TOT.CANT, TOT.CODIGOINVENTARIO, TOT.SUMA;
    	/* ESTA PARTE DEVUELVE LA FILA SIGUIENTE */
    GO
    
    declare @articulo vARCHAR(20)='TV', @CANTIDAD INT =27;
    /*CREO QUE HAY QUE CHEQUEAR PRIMERO LA EXISTENCIA ANTES DE HACER LA SALIDA, PERO A MODO DE EJEMPLO VALE IGUAL */
    INSERT INTO SALIDAS(ARTICULO, CANTIDAD) VALUES (@ARTICULO, @CANTIDAD)
    	
    INSERT INTO RELACION SELECT SCOPE_IDENTITY(),/*RECOGEMOS EL ID INSERTADO EN LA SALIDA */
    
      O.CODIGOINVENTARIO, CASE WHEN O.CANT < @CANTIDAD THEN O.SUMA
    											ELSE @CANTIDAD-(O.CANT-O.SUMA)
    										 END AS CANT FROM dbo.EXTRAEINVENTARIO(@ARTICULO, @CANTIDAD) AS O;
    										 
    										 /* EN EL CASE PREPARAMOS LA COLUMNA CANTIDAD DESCONTADA 
    										 HAY QUE EVALUAR MÁS CAUSISTICAS, QUE LAS QUE TE HE PUESTO, PERO PARA QUE VER SI TE ENCAJA, ES SUFICIENTE, CREO
    										 */
     SELECT * fROM inventario
     SELECT *  FROM SALIDAS
     SELECT *  FROM RELACION

    Como ya es un poquito tarde, te lo dejo así.

    Espero te sirva, y puedas mejorarlo. Ya comentas.

    Saludos

    • Marcado como respuesta gaunmanuel lunes, 30 de abril de 2018 22:31
    sábado, 28 de abril de 2018 23:45
  • Que tal Javi.

    En verdad muy buen aporte, te lo agradezco mucho, nunca habia considerado las funciones con un retorno de tabla.

    Voy a aplicar todas las demas consideraciones o causisticas que mencionas y posterior compartire el resultado, pero definitivamente esto ya me amplio el panaroma.

    Entre las cosas que cambiare, son que en lugar de ordenar por el codigo de las tablas, deberá ser con fecha haciendo una especie de FIFO (first input firts out) y algunas otras clausulas, pero en esencia, tu aporte fue muy bueno.

    gracias y en cuanto termine y funcione lo indicare aqui.

    saludos.

    lunes, 30 de abril de 2018 22:31
  • Que tal Javi.

    tengo una duda para que me des algun tip.

    En base a tu código y especificamente en esta parte:

    declare @articulo vARCHAR(20)='TV', @CANTIDAD INT =27;
    /*CREO QUE HAY QUE CHEQUEAR PRIMERO LA EXISTENCIA ANTES DE HACER LA SALIDA, PERO A MODO DE EJEMPLO VALE IGUAL */
    INSERT INTO SALIDAS(ARTICULO, CANTIDAD) VALUES (@ARTICULO, @CANTIDAD)
    	
    INSERT INTO RELACION SELECT SCOPE_IDENTITY(),/*RECOGEMOS EL ID INSERTADO EN LA SALIDA */
    
      O.CODIGOINVENTARIO, CASE WHEN O.CANT < @CANTIDAD THEN O.SUMA
    											ELSE @CANTIDAD-(O.CANT-O.SUMA)
    										 END AS CANT FROM dbo.EXTRAEINVENTARIO(@ARTICULO, @CANTIDAD) AS O;
    										 
    										 /* EN EL CASE PREPARAMOS LA COLUMNA CANTIDAD DESCONTADA 
    										 HAY QUE EVALUAR MÁS CAUSISTICAS, QUE LAS QUE TE HE PUESTO, PERO PARA QUE VER SI TE ENCAJA, ES SUFICIENTE, CREO
    										 */

    vemos que en base al registro que se inserta en la tabla SALIDAS, es como se busca en el inventario para insertarlo a la tabla RELACION y la variables @articulo y @Cantidad, pasan como parámetro en la función ExtraeInventario...

    mi cuestion es: como harías para que no sea registro por registro de la tabla de salidas?

    • es decir, supongamos que en salidas ya tengo 2 registros y de esos 2 registro requiero extrarlos del inventario, entonces como seria la consulta para que los busque al mismo tiempo y no de uno por uno como en el ejemplo que hiciste?

    espero haberme explicado y muchas gracias por el aporte.

    miércoles, 29 de agosto de 2018 16:21
  • Hola gaunmanuel:

    Si tuvieras que hacerlo así, se puede de este modo. Ojo, que tienes que pensar siempre en que tengas una alta concurrencia, y yo no te lo he evaluado. Por ejemplo que recibas tres peticiones a la vez, mientras esta procesando la primera, y esto te podría generar problemas de inventario.

    WITH cte
         AS (
         SELECT CODIGOSALIDA,
                ARTICULO,
                CANTIDAD
         FROM salidas
     --where codigosalida be /* filtro de tabla salidas que quiero ejecutar.... */
         )
    	 /* aquí iría el insert */
         SELECT c.codigoSAlida,
                O.CODIGOINVENTARIO,
                CASE
                    WHEN O.CANT < c.cantidad
                    THEN O.SUMA
                    ELSE c.cantidad - (O.CANT - O.SUMA)
                END AS CANT
         FROM cte c
              CROSS APPLY
    (
        SELECT TOT.FILA AS FILA,
               TOT.CANT,
               tot.SUMA,
               TOT.CODIGOINVENTARIO
        FROM
    (
        SELECT ROW_NUMBER() OVER(ORDER BY CODIGOINVENTARIO) AS FILA,
               SUM(SUM(CANTIDAD)) OVER(ORDER BY CODIGOINVENTARIO) AS CANT,
               SUM(CANTIDAD) AS SUMA,
               CODIGOINVENTARIO
        FROM INVENTARIO
        WHERE ARTICULO = c.articulo
              AND CANTIDAD > 0
        GROUP BY CODIGOINVENTARIO
    ) AS TOT
        WHERE TOT.CANT <= c.cantidad 
    	
    /*ESTA PARTE DEVUELVE EL ACUMULADO INFERIOR O IGUAL A LO SOLICITADO */
    
        UNION
        SELECT TOP (1) MIN(TOT.FILA) AS FILA,
                       TOT.CANT,
                       TOT.SUMA,
                       TOT.CODIGOINVENTARIO
        FROM
    (
        SELECT ROW_NUMBER() OVER(ORDER BY CODIGOINVENTARIO) AS FILA,
               SUM(SUM(CANTIDAD)) OVER(ORDER BY CODIGOINVENTARIO) AS CANT,
               SUM(CANTIDAd) AS SUMA,
               CODIGOINVENTARIO
        FROM INVENTARIO
        WHERE ARTICULO = c.articulo
              AND CANTIDAD > 0
        GROUP BY CODIGOINVENTARIO
    ) AS TOT
        WHERE TOT.CANT > c.cantidad
        GROUP BY TOT.CANT,
                 TOT.CODIGOINVENTARIO,
                 TOT.SUMA
    ) AS o;
    					 
    			
    							

    Algo de este palo podría ser una opción.

    Espero te ayude.

    Un saludo

    lunes, 3 de septiembre de 2018 5:27
  • Hola Amigo.

    es muy bueno tu aporte, y casi salio...

    cuando yo tengo 2 registros, los cuales están aquí:

    WITH cte
         AS (
         SELECT CODIGOSALIDA,
                ARTICULO,
                CANTIDAD
         FROM salidas
     --where codigosalida be /* filtro de tabla salidas que quiero ejecutar.... */
         )

    • El segundo registro, vuelve a tomar los mismos registros del inventario que ya había tomado el primer registro.

    No logro hacer que tome los siguientes del inventario.

    Estaré intentándolo a ver si lo consigo.

    muchas gracias por tu aportación. si tienes una idea te lo agradecería.

    saludos. 

    martes, 18 de junio de 2019 22:35