cambiar Cursor lento por SP

[gaviota81]

Hol,a en semanas anteriores publiqué una duda para realizar un sp o un cursor que me insertara en una tabla una consulta como esta: ' Hacer un match por registro en donde se evalua el campo CantidadPrendas, si este campo tuviera un valor mayor a 1, es necesario grabar un registro por cada unidad adicional al valor de 1. Por citar un ejemplo: si tuviera 2, hay que grabar 2 registros idénticos del archivo MOMBEF en lugar de 1 y así sucesivamente dependiendo de la cantidad que esté reflejada en el campo CantidadPrendas.

Esto lo resolví mediante el siguiente cursor, si lo hace bien pero se tarda mucho para cuando son millones de registros, alguna idea de como se puede mejorar o convertir en un SP para que sea más rápido??? muchas gracias!

Código:

IF OBJECT_ID('Carga_Boleta_Match') IS NOT NULL
 Drop Table Carga_Boleta_Match
go
create table Carga_Boleta_Match 
( Boleta bigint,
  CveArticulo bigint,
  desArticulo varchar(30),
  Peso real,
  Peso2 real,
  CantidadPrendas bigint,
  Comentario varchar(50))
  go
 
SET NOCOUNT ON   
 DECLARE @intFlag INT
 DECLARE @temp INT
 DECLARE @CantidadPrendas INT
 DECLARE @boleta VARCHAR(10)
 DECLARE @cveArticulo VARCHAR(10)
 DECLARE @desArticulo VARCHAR(10)
 DECLARE @peso VARCHAR(10) 
 DECLARE @peso2 REAL 
 DECLARE @comentario VARCHAR(10)
 DECLARE @Sucursal INT
 DECLARE articulos_cursor CURSOR FOR
 select b.CantidadPrendas,a.boleta,c.CveArticulo, c.DescArticulo, c.Peso,0 as peso2,'' as comentario
  FROM MOMBEF_Trabajo a, MOMBDF_1  b, MOARCF_1 c
  where a.Boleta=b.boleta
    and cast(a.sucursal as bigint)=b.sucursal
    and b.cveArticulo=c.CveArticulo
OPEN articulos_cursor;
 FETCH NEXT FROM articulos_cursor INTO @temp,@boleta,@cveArticulo,@desArticulo,@peso,@Peso2,@comentario;
 WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @intFlag = 0;
        WHILE  (@intFlag <@temp)
          BEGIN
              PRINT @temp
              INSERT INTO Carga_Boleta_Match 
              values (@boleta, @cveArticulo, @DesArticulo, @Peso,@Peso2,@temp, @comentario)
              SET @intFlag=@intFlag+1;
              IF @intFlag =@temp
                 BREAK
              ELSE 
                CONTINUE 
             END  
          UPDATE Carga_Boleta_Match 
             SET Comentario ='Validar', 
                 Peso2=Peso/CantidadPrendas
           WHERE CantidadPrendas >1
       FETCH NEXT FROM articulos_cursor INTO @temp,@boleta,@cveArticulo,@desArticulo,@peso,@peso2,@comentario;
    END
CLOSE articulos_cursor;
DEALLOCATE articulos_cursor;
GO

[devilguzz]

puedes mejorarlo usando una tabla temporal... quedaria algo asi:

IF OBJECT_ID('Carga_Boleta_Match') IS NOT NULL
Drop Table Carga_Boleta_Match
go
create table Carga_Boleta_Match
( Boleta bigint,
CveArticulo bigint,
desArticulo varchar(30),
Peso real,
Peso2 real,
CantidadPrendas bigint,
Comentario varchar(50))
go

/**********************************/
--Creas una tabla temporal para la sesion donde tiene un contador en el id
CREATE TABLE #tabla
(
id INT IDENTITY(1,1),
CantidadPrendas INT,
boleta varchar(10),
CveArticulo varchar(10),
DescArticulo varchar(10),
Peso varchar(10),
Peso2 real,
Comentario varchar(10)
)

--Insertas tu consulta que hacias en tu CURSOR directamente en la tabla temporal #tabla
INSERT INTO #tabla
select b.CantidadPrendas,a.boleta,c.CveArticulo, c.DescArticulo, c.Peso,0 as peso2,'' as comentario
FROM MOMBEF_Trabajo a, MOMBDF_1 b, MOARCF_1 c
WHERE a.Boleta=b.boleta and cast(a.sucursal as bigint)=b.sucursal and b.cveArticulo=c.CveArticulo

DECLARE @cant INT
--Aqui preguntamos cuantas registros tiene nuestra tabla temporal
select @cant=COUNT(id) FROM #tabla

--Con este WHILE recorremos la tabla temporal
WHILE(@cant>0)
BEGIN
<Aca realizas tus operaciones>
SET @cant=@cant-1
END

--Creo que esto lo debes dejar para el final
UPDATE Carga_Boleta_Match
SET Comentario ='Validar', Peso2=Peso/CantidadPrendas
WHERE CantidadPrendas >1


PD.. si deseas lo puedes poner como transaccion por si algo sale mal...

en mi corta experiencia, hacia este tipo de procedimientos con cursores pero se demoraban una eternidad y me hacian crecer el LOG no sabes cuanto... pero con las tablas temporales y el IDENTITY... se reduce casi a nada... espero que te sirva de ayuda

[Andres95]

Primero, podrias generar una tabla que solo contenga los consecutivos para evitar el ciclo / cursor.

Solo tendrias que crearla una vez y utilizarla cada que requieras de su funcionalidad....

Código:

SET NOCOUNT ON

IF OBJECT_ID('tbl_Consecutive') IS NOT NULL  Drop Table tbl_Consecutive

CREATE TABLE tbl_Consecutive (iId int NOT NULL)

DECLARE @i BIGINT

SET @i = 0

-- Agrega tantos segun el maximo que esperas iterar
WHILE @i < 100000
BEGIN
    INSERT INTO tbl_Consecutive (iId) VALUES(@i)   
    SET @i = @i + 1;
END

ALTER TABLE tbl_Consecutive ADD PRIMARY KEY (iId)

PRINT 'TABLE FINISHED'

Despues con un solo select con joins insertas directamente la info en tu tabla destino...

Código:

INSERT INTO Carga_Boleta_Match(
             boleta
            ,CveArticulo
            ,DescArticulo
            ,Peso
            ,Peso2
            ,Comentario
) 
SELECT       a.boleta
            ,c.CveArticulo
            ,c.DescArticulo
            ,c.Peso
            ,(Peso / CantidadPrendas) AS peso2
            ,'Validar' AS comentario
FROM         MOMBEF_Trabajo a 
INNER JOIN   MOMBDF_1  b 
ON           a.Boleta = b.boleta     AND
             CAST(a.sucursal AS BIGINT) = b.sucursal  
INNER JOIN   MOARCF_1 c
ON           b.cveArticulo=c.CveArticulo
INNER JOIN   tbl_Consecutive d
ON           d.iId < b.CantidadPrendas

Saludos!

[gaviota81]

Hola! les agradezco a ambos infinitamente! probé las dos soluciones y ambas me resultaron de mucha utilidad. Anexo la solución:

Código SQL:

SET NOCOUNT ON
 
IF OBJECT_ID('tbl_Consecutive') IS NOT NULL  DROP TABLE tbl_Consecutive
 
CREATE TABLE tbl_Consecutive (iId INT NOT NULL)
 
DECLARE @i BIGINT
DECLARE @cant BIGINT
 
SET @i = 0
SELECT @cant=COUNT(*) 
FROM MOMBEF_Trabajo a, MOMBDF_1 b, MOARCF_1 c
WHERE a.Boleta=b.boleta 
AND a.sucursal = b.sucursal 
AND  b.cveArticulo = CAST(c.CveArticulo AS INT)
 
-- Agrega tantos segun el maximo que esperas iterar
WHILE @i <= @cant
BEGIN
    INSERT INTO tbl_Consecutive (iId) VALUES(@i)   
    SET @i = @i + 1;
END
 
ALTER TABLE tbl_Consecutive ADD PRIMARY KEY (iId)
 
PRINT 'TABLE FINISHED'
 
SELECT * FROM tbl_Consecutive
 
DROP TABLE Carga_Boleta_Match
GO
CREATE TABLE Carga_Boleta_Match
(
CantidadPrendas NUMERIC(18,0),
boleta BIGINT,
CveArticulo NUMERIC(18,0),
DescArticulo VARCHAR(30),
Peso DECIMAL(28,2),
Peso2 DECIMAL(28,2),
Comentario VARCHAR(30)
) 
 
INSERT INTO Carga_Boleta_Match(
             CantidadPrendas
            ,boleta
            ,CveArticulo
            ,DescArticulo
            ,Peso
            ,Peso2
            ,Comentario
) 
SELECT       b.CantidadPrendas
            ,a.boleta
            ,b.CveArticulo
            ,c.DescArticulo
            ,b.Peso
            ,(b.Peso / CantidadPrendas) AS peso2
            ,'' AS comentario
FROM         MOMBEF_Trabajo a 
INNER JOIN   MOMBDF_1  b 
ON           a.Boleta = b.boleta     AND
             a.sucursal = b.sucursal  
INNER JOIN   MOARCF_1 c
ON            b.cveArticulo = CAST(c.CveArticulo AS INT)
INNER JOIN   tbl_Consecutive d
ON           d.iId < b.CantidadPrendas
GO
UPDATE Carga_Boleta_Match
   SET comentario = 'Verificar peso'
 WHERE CantidadPrendas>1
 

[Andres95]

Solo un punto..

el hacer el select con las tablas con comas es igual a hacer un producto cartesiano en cada relacion...sql server optimiza las busquedas al utilizar joins explicitos...

Saludos!