Foros del Web - Optimizar un SP que traspone una tabla

Foros del Web (http://www.forosdelweb.com/)

- SQL Server (http://www.forosdelweb.com/f87/)

- - Optimizar un SP que traspone una tabla (http://www.forosdelweb.com/f87/optimizar-sp-que-traspone-tabla-392266/)

Optimizar un SP que traspone una tabla

Tengo un par de vistas que obtienen ciertos registros de la BD's, pero los obtengo por filas y los requiero por columnas, así que implementé un SP que traspone a mis necesidades la información, pero el problema es que se tarda mucho. Para una consulta que regresa 100 registros, se tarda casi 2 minutos para ejecutar el SP.

El detalle es que hago varias concatenaciones por que necesito la información con ciertas características:
1) Debe haber 4 columnas: M, P, T y O adicionales a la de la llave (NoParte). Las 3 columnas primeras son 'unicas y la 4a es el acumulado del resto de los valores del campo "Almacen"
2) La vista puede regresarme 1 o más registros pertenecientes a x número de almacenes, a veces 1 o 2 almacenes, a veces más y en cualquier orden.

Para forzar a que siempre se formen las 4 columnas en orden, hago acumulados y concatenaciones.

Si alguien pudiera darme una idea de como optimizar el SP, se lo agradecería mucho, el SP es el siguiente:

Código:

CREATE PROCEDURE sResumen_Assort_Inventario

AS

BEGIN
 

DECLARE @NoParte varchar(10)

DECLARE @NomAlmacen VARCHAR(50)

DECLARE @Valor float

DECLARE @Suma float
 

DECLARE @AlmacenM float

DECLARE @AlmacenP float

DECLARE @AlmacenT float
 
 

DECLARE @Count INT

DECLARE @GrantCount INT

DECLARE @Select VARCHAR(8000)

DECLARE @GrantSelect VARCHAR(8000)
 

DECLARE @CadAlmacenes VARCHAR(10)

DECLARE @Salida VARCHAR(8000)

DECLARE @IDAlmacen CHAR(1)
 

SET @GrantCount = 0

SET @GrantSelect = ''
 

DECLARE NumerosDeParte CURSOR SCROLL FOR

(SELECT NoParte

 FROM vw_Assort_Esp

 GROUP BY NoParte)
 

OPEN NumerosDeParte
 

FETCH FIRST FROM NumerosDeParte INTO @NoParte

WHILE @@FETCH_STATUS = 0

BEGIN

      DECLARE CurExistencias CURSOR SCROLL FOR 

      (SELECT Almacen, TonTotales, NoParte

      FROM vw_Assort_Inv

      WHERE NoParte = @NoParte)
 

      SET @Select = ''

      SET @Count = 0

      SET @CadAlmacenes = ''

      SET @Suma = 0

      SET @AlmacenM = 0

      SET @AlmacenP = 0

      SET @AlmacenT = 0

      

      OPEN CurExistencias

      FETCH FIRST FROM CurExistencias INTO @NomAlmacen, @Valor, @NoParte

      WHILE @@FETCH_STATUS = 0

      BEGIN

            SET @Count = @Count + 1

            SET @NomAlmacen = RTRIM(LTRIM(@NomAlmacen))

            IF @Count = 1

                SET @Select = @Select + ' SELECT NoParte=''' + RTRIM(LTRIM(@NoParte))+''''
 

            IF @NomAlmacen='M' OR @NomAlmacen='P' OR @NomAlmacen='T' 

            BEGIN

                 IF @NomAlmacen = 'M'

                       SET @AlmacenM = @Valor

                 IF @NomAlmacen = 'P'

                       SET @AlmacenP = @Valor

                 IF @NomAlmacen = 'T'

                       SET @AlmacenT = @Valor

            END

            ELSE

                 SET  @Suma = @Suma + @Valor
 

            SET @CadAlmacenes = @CadAlmacenes + @NomAlmacen
 

            FETCH NEXT FROM CurExistencias INTO @NomAlmacen, @Valor, @NoParte

      END
 

      CLOSE CurExistencias

      DEALLOCATE CurExistencias
 
 

      SET @Salida = ''

      DECLARE CurAlmacenes CURSOR SCROLL FOR  (SELECT Almacen FROM Almacenes WHERE Tipo='E')

      OPEN CurAlmacenes

      FETCH FIRST FROM CurAlmacenes INTO @IDAlmacen

      WHILE @@FETCH_STATUS = 0

      BEGIN

           IF CHARINDEX(@IDAlmacen, @CadAlmacenes)=0

           BEGIN

               IF RTRIM(LTRIM(@IDAlmacen))='M' OR RTRIM(LTRIM(@IDAlmacen))='P' OR RTRIM(LTRIM(@IDAlmacen))='T' 

               BEGIN

                   SET @Salida = @Salida +  ', ' + @IDAlmacen + '= ''0'''

               END

           END

           ELSE

               BEGIN

                    IF @IDAlmacen = 'M'

                       SET @Salida = @Salida +  ',M= '''+CONVERT(VARCHAR,@AlmacenM)+'''' 

                    IF @IDAlmacen = 'P'

                       SET @Salida = @Salida +  ',P= '''+CONVERT(VARCHAR,@AlmacenP)+'''' 

                    IF @IDAlmacen = 'T'

                       SET @Salida = @Salida +  ',T= '''+CONVERT(VARCHAR,@AlmacenT)+'''' 

               END

 

           FETCH NEXT FROM CurAlmacenes INTO @IDAlmacen

      END

      CLOSE CurAlmacenes

      DEALLOCATE CurAlmacenes
 

      IF @Suma > 0

          SET @Salida = @Salida +  ', NG= '''+CONVERT(VARCHAR,@Suma)+''''

      ELSE

          SET @Salida = @Salida +  ', NG= ''0'''
 

      IF @Count=0

           SET @Select = @Select + ' SELECT NoParte=''' + RTRIM(LTRIM(CONVERT(VARCHAR,@NoParte)))+'''' + @Salida

      ELSE 

           SET @Select = @Select + @Salida
 

      SET @Select = @Select + CHAR(13)
 

      SET @GrantCount = @GrantCount + 1

      IF @GrantCount = 1

            SET @GrantSelect = @Select

      ELSE

            SET @GrantSelect = @GrantSelect + CHAR(13) + ' UNION ' + CHAR(13)+  @Select
 

      FETCH NEXT FROM NumerosDeParte INTO @NoParte

END

PRINT(@GrantSelect)

EXEC(@GrantSelect)

CLOSE NumerosDeParte

DEALLOCATE NumerosDeParte

END

GO

Checa que las tablas que son llamadas por tus vistas vw_Assort_Esp y vw_Assort_Inv tengan NoParte en un index. Mas que eso... cambiar a SQL 2005, nomas...

Las llaves estan correctas .... y según las pruebas que he hecho, SQL Server 2000 me da mejor desempeño que SQL 2005..... lástima

:pensando: El problema que yo veo master, son muchos cursores, entiendo que tienes que recorrer esos registros para hacerlos columnas, se me ocurre que si ya tienes tus vistas, puedes hacerle individualmente un group by al o los campos necesarios todo esto dentro de un select, no se que tanto mejoraría el rendimiento contra lo que ya tienes, y tampoco si realmente funcionará, pero algo tenía que decir :borracho:

Ya nos contarás más cosas...

mmmmmmmmmmmmmmmmm, pues no se, sería cosa de probar....

aunque te diré, en este momento estoy peleandome con una vista con no menos de 10 subconsultas y regresa 2000 registros perfectamente procesados en menos de 3 segundos. Por eso la extrañeza de el uso de esos 3 cursoritos anidados con tan pocos registros.

Por cierto .... ¿cómo diablos podría hacer para generar una columna con un consecutivo de número de registro en una consulta agrupada? .... sorry, es que no era solo una expresión eso de que me estaba "peleando", jejeje

Saludos y dejame "mastico" la sugerencia

Cita:

Por cierto .... ¿cómo diablos podría hacer para generar una columna con un consecutivo de número de registro en una consulta agrupada? .... sorry, es que no era solo una expresión eso de que me estaba "peleando", jejeje

WHAT??? :ojotes:

Creo que eso se traduce a lo mismo que lo que estás intentando para transponer tus registros verdad?

Se me acaba de ocurrir una idea...quizás en lugar de recorrer esos sets con cursores podrías tratar de usar tablas temporales y entonces hacer tus iteraciones a partir de ellas, me parece que sería más rápido...:pensando: deja hago unas pequeñas pruebas.

No alcanzo a entender exactamente que es lo que pretendes lograr (por tanto cursor), pero creo que algo como esto se aproxima a lo que necesitas:

Código:

SELECT 

        NoParte,

        SUM(CASE WHEN Almacen = 'M' THEN TonTotales ELSE 0 END) AS AlmacenM,

        SUM(CASE WHEN Almacen = 'P' THEN TonTotales ELSE 0 END) AS AlmacenP,

        SUM(CASE WHEN Almacen = 'T' THEN TonTotales ELSE 0 END) AS AlmacenT,

        SUM(TonTotales) AS Total

FROM tabla

GROUP BY Almacen, NoParte

Cita:

Iniciado por u_goldman

WHAT??? :ojotes:

Creo que eso se traduce a lo mismo que lo que estás intentando para transponer tus registros verdad?

.....

Nop, jeje, es para otra cosa "más urgente" necesitaba (por que ya lo hice de otra forma) generar un campo extra a una vista con un número correspondiente al número de regfistro que le correspondiera dentro de ese grupo...... si ya se, estaba medio jalado, pero pasó lo mismo que con el SP de transpuesta.

Cita:

Iniciado por Mithrandir

No alcanzo a entender exactamente que es lo que pretendes lograr (por tanto cursor), pero creo que algo como esto se aproxima a lo que necesitas: ....

[suspiro]pues si[/suspiro], ya lo había hecho similar a esa forma, pero no me cuadran las cifras :'( , el SP lo hace perfectamente, pero se tarda más de un minuto.
Supongo que ahora en lugar de gastarle tiempo al SP, mejor se lo dedico a la consulta :arriba:

machas thanks y bueno, si alguien alguna vez necesita el algoritmo que transponga una tabla de filas a columnas, el SP que publiqué al principio lo hace, la idea supongo que esta bien: Leer las filas, formar la consulta, concatenar y ejecutar consultas UNION, aunque seguramente debe haber otra forma. Ahora que tenga chance (oportunidad) veré como optimizar el caso de la tranpuesta, por ahora creo que me regresaré a la consulta.

Les debo unas birras :borracho: :arriba: (te las perdiste Mith, hasta el otro año en San Marcos)