Foros del Web - Ver Mensaje Individual

EztebanD · #1 (**permalink**) 23/06/2015, 10:02

Buen día.

Me encuentro en un dilema y es que en el lugar donde actualmente trabajo utilizo mucho SQL y me han pedido optimizar unos procesos ya que la cantidad de información que se maneja es bastante, hay un proceso que esta basado en un cursor y necesito optimizarlo ya que estos son muy lentos y mas cuando se maneja mucha información.

Este es el código

Código SQL:

Ver originalUSE [STREAMS]
GO
/****** Object:  StoredProcedure [dbo].[morasconsumoultimos12]    Script Date: 06/23/2015 10:22:42 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[morasconsumoultimos12]
                    
 
AS
 
DECLARE @mora30 INT,@mora60 INT,@mora90 INT,@morama90 INT,@contador INT, @SQL VARCHAR(100)
 
DECLARE @LNNOTE FLOAT,  
@DPD1 INT,@DPD2 INT,@DPD3 INT,@DPD4 INT,@DPD5 INT,@DPD6 INT,@DPD7 INT,
@DPD8 INT,@DPD9 INT,@DPD10 INT,@DPD11 INT,@DPD12 INT
 
 
DECLARE curs_mora cursor
FOR
SELECT LNNOTE
,DPD1405,DPD1406,DPD1407 ,DPD1408 ,DPD1409,DPD1410 ,DPD1411,DPD1412
,DPD1501,DPD1502,DPD1503, DPD1504   FROM Bstringconsumolina
 
OPEN curs_mora
fetch NEXT FROM curs_mora INTO @LNNOTE,@DPD1,@DPD2,@DPD3,@DPD4,@DPD5,@DPD6,
@DPD7,@DPD8,@DPD9,@DPD10,@DPD11,@DPD12
 
while @@fetch_status=0
BEGIN
    SET @mora30=0
    SET @mora60=0
    SET @mora90=0
    SET @morama90=0
    --set @contador=1
    --while @contador<7
    --Begin
        --set @sql= 'case when @CBCUV70' + CONVERT (VARCHAR,@contador) + '>30 THEN  @mora +1 ELSE 0 END'
 
        SET @mora30= CASE WHEN @DPD12 BETWEEN 1 AND 30   THEN  @mora30 +1 ELSE @mora30 END
        SET @mora60= CASE WHEN @DPD12 BETWEEN 31 AND 60  THEN  @mora60 +1 ELSE @mora60 END
        SET @mora90= CASE WHEN @DPD12 BETWEEN 61 AND 90  THEN  @mora90 +1 ELSE @mora90 END
        SET @morama90= CASE WHEN @DPD12 > 90  THEN  @morama90 +1 ELSE @morama90 END
 
        SET @mora30= CASE WHEN @DPD11 BETWEEN 1 AND 30   THEN  @mora30 +1 ELSE @mora30 END
        SET @mora60= CASE WHEN @DPD11 BETWEEN 31 AND 60  THEN  @mora60 +1 ELSE @mora60 END
        SET @mora90= CASE WHEN @DPD11 BETWEEN 61 AND 90  THEN  @mora90 +1 ELSE @mora90 END
        SET @morama90= CASE WHEN @DPD11 > 90  THEN  @morama90 +1 ELSE @morama90 END
 
        SET @mora30= CASE WHEN @DPD10 BETWEEN 1 AND 30   THEN  @mora30 +1 ELSE @mora30 END
        SET @mora60= CASE WHEN @DPD10 BETWEEN 31 AND 60  THEN  @mora60 +1 ELSE @mora60 END
        SET @mora90= CASE WHEN @DPD10 BETWEEN 61 AND 90  THEN  @mora90 +1 ELSE @mora90 END
        SET @morama90= CASE WHEN @DPD10 > 90  THEN  @morama90 +1 ELSE @morama90 END
 
        SET @mora30= CASE WHEN @DPD9 BETWEEN 1 AND 30   THEN  @mora30 +1 ELSE @mora30 END
        SET @mora60= CASE WHEN @DPD9 BETWEEN 31 AND 60  THEN  @mora60 +1 ELSE @mora60 END
        SET @mora90= CASE WHEN @DPD9 BETWEEN 61 AND 90  THEN  @mora90 +1 ELSE @mora90 END
        SET @morama90= CASE WHEN @DPD9 > 90  THEN  @morama90 +1 ELSE @morama90 END
 
        SET @mora30= CASE WHEN @DPD8 BETWEEN 1 AND 30   THEN  @mora30 +1 ELSE @mora30 END
        SET @mora60= CASE WHEN @DPD8 BETWEEN 31 AND 60  THEN  @mora60 +1 ELSE @mora60 END
        SET @mora90= CASE WHEN @DPD8 BETWEEN 61 AND 90  THEN  @mora90 +1 ELSE @mora90 END
        SET @morama90= CASE WHEN @DPD8 > 90  THEN  @morama90 +1 ELSE @morama90 END
 
        SET @mora30= CASE WHEN @DPD7 BETWEEN 1 AND 30   THEN  @mora30 +1 ELSE @mora30 END
        SET @mora60= CASE WHEN @DPD7 BETWEEN 31 AND 60  THEN  @mora60 +1 ELSE @mora60 END
        SET @mora90= CASE WHEN @DPD7 BETWEEN 61 AND 90  THEN  @mora90 +1 ELSE @mora90 END
        SET @morama90= CASE WHEN @DPD7 > 90  THEN  @morama90 +1 ELSE @morama90 END
 
        SET @mora30= CASE WHEN @DPD6 BETWEEN 1 AND 30   THEN  @mora30 +1 ELSE @mora30 END
        SET @mora60= CASE WHEN @DPD6 BETWEEN 31 AND 60  THEN  @mora60 +1 ELSE @mora60 END
        SET @mora90= CASE WHEN @DPD6 BETWEEN 61 AND 90  THEN  @mora90 +1 ELSE @mora90 END
        SET @morama90= CASE WHEN @DPD6 > 90  THEN  @morama90 +1 ELSE @morama90 END
 
        SET @mora30= CASE WHEN @DPD5 BETWEEN 1 AND 30   THEN  @mora30 +1 ELSE @mora30 END
        SET @mora60= CASE WHEN @DPD5 BETWEEN 31 AND 60  THEN  @mora60 +1 ELSE @mora60 END
        SET @mora90= CASE WHEN @DPD5 BETWEEN 61 AND 90  THEN  @mora90 +1 ELSE @mora90 END
        SET @morama90= CASE WHEN @DPD5 > 90  THEN  @morama90 +1 ELSE @morama90 END
 
        SET @mora30= CASE WHEN @DPD4 BETWEEN 1 AND 30   THEN  @mora30 +1 ELSE @mora30 END
        SET @mora60= CASE WHEN @DPD4 BETWEEN 31 AND 60  THEN  @mora60 +1 ELSE @mora60 END
        SET @mora90= CASE WHEN @DPD4 BETWEEN 61 AND 90  THEN  @mora90 +1 ELSE @mora90 END
        SET @morama90= CASE WHEN @DPD4 > 90  THEN  @morama90 +1 ELSE @morama90 END
 
        SET @mora30= CASE WHEN @DPD3 BETWEEN 1 AND 30   THEN  @mora30 +1 ELSE @mora30 END
        SET @mora60= CASE WHEN @DPD3 BETWEEN 31 AND 60  THEN  @mora60 +1 ELSE @mora60 END
        SET @mora90= CASE WHEN @DPD3 BETWEEN 61 AND 90  THEN  @mora90 +1 ELSE @mora90 END
        SET @morama90= CASE WHEN @DPD3 > 90  THEN  @morama90 +1 ELSE @morama90 END
 
        SET @mora30= CASE WHEN @DPD2 BETWEEN 1 AND 30   THEN  @mora30 +1 ELSE @mora30 END
        SET @mora60= CASE WHEN @DPD2 BETWEEN 31 AND 60  THEN  @mora60 +1 ELSE @mora60 END
        SET @mora90= CASE WHEN @DPD2 BETWEEN 61 AND 90  THEN  @mora90 +1 ELSE @mora90 END
        SET @morama90= CASE WHEN @DPD2 > 90  THEN  @morama90 +1 ELSE @morama90 END
 
        SET @mora30= CASE WHEN @DPD1 BETWEEN 1 AND 30   THEN  @mora30 +1 ELSE @mora30 END
        SET @mora60= CASE WHEN @DPD1 BETWEEN 31 AND 60  THEN  @mora60 +1 ELSE @mora60 END
        SET @mora90= CASE WHEN @DPD1 BETWEEN 61 AND 90  THEN  @mora90 +1 ELSE @mora90 END
        SET @morama90= CASE WHEN @DPD1 > 90  THEN  @morama90 +1 ELSE @morama90 END
                
                                
            
                         --SET @contador=@contador+1
    --END
    
    UPDATE Bstringconsumolina SET mora30 = @mora30  WHERE  CURRENT OF curs_mora
    UPDATE Bstringconsumolina SET mora60 = @mora60  WHERE CURRENT OF curs_mora
    UPDATE Bstringconsumolina SET mora90 = @mora90  WHERE CURRENT OF curs_mora
    UPDATE Bstringconsumolina SET mora_mayor90 = @morama90  WHERE CURRENT OF curs_mora
 
fetch NEXT FROM curs_mora INTO @LNNOTE,@DPD1,@DPD2,@DPD3,@DPD4,@DPD5,@DPD6,
@DPD7,@DPD8,@DPD9,@DPD10,@DPD11,@DPD12
 
END
close curs_mora
deallocate curs_mora

Cabe aclarar que:

La tabla 'Bstringconsumolina' fue previamente creada antes de que se ejecute el cursor, luego de ello se modifica la tabla y se añade las columnas de mora:

Código SQL:

Ver originalALTER TABLE Bstringconsumolina ADD mora30 INT, mora60 INT, mora90 INT,mora_mayor90 INT

luego de esto se ejecuta el cursor, pero toma mucho tiempo, podrían darme sugerencias de como optimizar este cursor

Gracias.