Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » SQL Server »

cambiar Cursor lento por SP

Estas en el tema de cambiar Cursor lento por SP en el foro de SQL Server en Foros del Web. 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: ' ...
  #1 (permalink)  
Antiguo 04/01/2010, 18:26
 
Fecha de Ingreso: diciembre-2009
Mensajes: 14
Antigüedad: 11 años, 6 meses
Puntos: 0
Exclamación cambiar Cursor lento por SP

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 @[email protected]+1;
              IF @intFlag [email protected]
                 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
  #2 (permalink)  
Antiguo 04/01/2010, 21:28
 
Fecha de Ingreso: marzo-2004
Mensajes: 70
Antigüedad: 17 años, 2 meses
Puntos: 0
Sonrisa Respuesta: cambiar Cursor lento por SP

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 @[email protected]
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
__________________
GuzZpaWn
  #3 (permalink)  
Antiguo 04/01/2010, 21:35
Avatar de Andres95
Colaborador
 
Fecha de Ingreso: diciembre-2004
Mensajes: 1.802
Antigüedad: 16 años, 5 meses
Puntos: 38
Busqueda Respuesta: cambiar Cursor lento por SP

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!
__________________
La sencillez y naturalidad son el supremo y último fin de la cultura...
--
MCTS : SQL Server 2008, .NET Framework 3.5, ASP.NET Applications.
  #4 (permalink)  
Antiguo 05/01/2010, 14:05
 
Fecha de Ingreso: diciembre-2009
Mensajes: 14
Antigüedad: 11 años, 6 meses
Puntos: 0
De acuerdo Respuesta: cambiar Cursor lento por SP

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

Código SQL:
Ver original
  1. SET NOCOUNT ON
  2.  
  3. IF OBJECT_ID('tbl_Consecutive') IS NOT NULL  DROP TABLE tbl_Consecutive
  4.  
  5. CREATE TABLE tbl_Consecutive (iId INT NOT NULL)
  6.  
  7. DECLARE @i BIGINT
  8. DECLARE @cant BIGINT
  9.  
  10. SET @i = 0
  11. SELECT @cant=COUNT(*)
  12. FROM MOMBEF_Trabajo a, MOMBDF_1 b, MOARCF_1 c
  13. WHERE a.Boleta=b.boleta
  14. AND a.sucursal = b.sucursal
  15. AND  b.cveArticulo = CAST(c.CveArticulo AS INT)
  16.  
  17. -- Agrega tantos segun el maximo que esperas iterar
  18. WHILE @i <= @cant
  19. BEGIN
  20.     INSERT INTO tbl_Consecutive (iId) VALUES(@i)  
  21.     SET @i = @i + 1;
  22. END
  23.  
  24. ALTER TABLE tbl_Consecutive ADD PRIMARY KEY (iId)
  25.  
  26. PRINT 'TABLE FINISHED'
  27.  
  28. SELECT * FROM tbl_Consecutive
  29.  
  30. DROP TABLE Carga_Boleta_Match
  31. GO
  32. CREATE TABLE Carga_Boleta_Match
  33. (
  34. CantidadPrendas NUMERIC(18,0),
  35. boleta BIGINT,
  36. CveArticulo NUMERIC(18,0),
  37. DescArticulo VARCHAR(30),
  38. Peso DECIMAL(28,2),
  39. Peso2 DECIMAL(28,2),
  40. Comentario VARCHAR(30)
  41. )
  42.  
  43. INSERT INTO Carga_Boleta_Match(
  44.              CantidadPrendas
  45.             ,boleta
  46.             ,CveArticulo
  47.             ,DescArticulo
  48.             ,Peso
  49.             ,Peso2
  50.             ,Comentario
  51. )
  52. SELECT       b.CantidadPrendas
  53.             ,a.boleta
  54.             ,b.CveArticulo
  55.             ,c.DescArticulo
  56.             ,b.Peso
  57.             ,(b.Peso / CantidadPrendas) AS peso2
  58.             ,'' AS comentario
  59. FROM         MOMBEF_Trabajo a
  60. INNER JOIN   MOMBDF_1  b
  61. ON           a.Boleta = b.boleta     AND
  62.              a.sucursal = b.sucursal  
  63. INNER JOIN   MOARCF_1 c
  64. ON            b.cveArticulo = CAST(c.CveArticulo AS INT)
  65. INNER JOIN   tbl_Consecutive d
  66. ON           d.iId < b.CantidadPrendas
  67. GO
  68. UPDATE Carga_Boleta_Match
  69.    SET comentario = 'Verificar peso'
  70.  WHERE CantidadPrendas>1
  #5 (permalink)  
Antiguo 05/01/2010, 19:23
Avatar de Andres95
Colaborador
 
Fecha de Ingreso: diciembre-2004
Mensajes: 1.802
Antigüedad: 16 años, 5 meses
Puntos: 38
Respuesta: cambiar Cursor lento por SP

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!
__________________
La sencillez y naturalidad son el supremo y último fin de la cultura...
--
MCTS : SQL Server 2008, .NET Framework 3.5, ASP.NET Applications.

Etiquetas: cursor, lento
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 21:30.