none
Optimizar procedimiento almacenado RRS feed

  • Pregunta

  • Hola comunidad, necesito mejorar el rendimiento de un procedimiento almacenado, dentro de el creo una tabla virtual en la cual tengo que ir añadiendo un registro por cada segundo que hay comprendido entre dos fechas. Utilizo un while par ello.

    declare @timinicio datetime=(SELECT TOP 1 D.FECHA_HORA FROM (
    Select a.fecha_hora from (
    SELECT TOP (1)[FECHA_HORA]  FROM [oee_db].[oee_db].[crudo_tracking] where ID_PARTE=@Linea order by FECHA_HORA asc) as a
    union
    select b.HORA_INICIO FROM (
    SELECT TOP (1)
         [HORA_INICIO]  FROM [oee_db].[oee_db].[crudo_paros] where ID_PARTE=@Linea order by HORA_INICIO asc) AS b
    UNION
    SELECT [FECHA_INICIO] FROM [oee_db].[oee_db].[crudo] where id_parte=@Linea
     ) AS D ORDER BY D.FECHA_HORA ASC)

     DECLARE @TIMFIN DATETIME=(SELECT TOP (1)[FECHA_HORA]  FROM [oee_db].[oee_db].[crudo_tracking] where ID_PARTE=@linea order by FECHA_HORA DESC)
    DECLARE @TOTALMIN TIME=(DATEADD(S, DATEDIFF(S,@timinicio,@TIMFIN), TIMEFROMPARTS(0, 0, 0, 0, 0)))
     DECLARE @TABLAPAROS TABLE (ID INT IDENTITY(1,1),MINUTO_INICIO TIME, MINUTO_FIN TIME,COD_PARO VARCHAR(50),PRIMARY KEY CLUSTERED (ID,MINUTO_INICIO,MINUTO_FIN))

    DECLARE @TABSEG TABLE (MINUTO TIME not null,VALOR FLOAT DEFAULT 0,IDENT INT IDENTITY (1,1),Cod_Paro nvarchar(50),PRIMARY KEY CLUSTERED (MINUTO ASC,IDENT ASC))

    DECLARE @TABMIN TABLE (MINUTO TIME not null,VALOR FLOAT DEFAULT 0,IDENT INT IDENTITY (1,1),Cod_Paro nvarchar(50),PRIMARY KEY CLUSTERED (MINUTO ASC,IDENT ASC))

    DECLARE @TIM TIME=TIMEFROMPARTS(0, 0, 0, 0, 0)
    select CURRENT_TIMESTAMP
    WHILE @TIM < @TOTALMIN
    BEGIN
    INSERT INTO @TABSEG (MINUTO) VALUES (@TIM)
    SET @TIM=DATEADD(S, 1,@TIM)
    END

    El While tarda casi 1segundo para un intervalo de 5h.

    gracias por adelantado.

    sábado, 2 de febrero de 2019 20:01

Respuestas

  • El While tarda casi 1segundo para un intervalo de 5h.

    Bueno según eso estamos hablando de que el bucle logra realizar unas 18.000 inserciones por segundo, que no está mal. No olvidemos que la tabla en realidad está en disco (la variable tipo table se crea en el tempdb), y que tiene una clave de tipo clustered, que se tiene que actualizar por cada registro. Y que las inserciones en el tempdb están logadas, por lo que estás limitado por la velocidad de grabación del .ldf del tempdb.

    Si quieres acelerar el bucle, puedes probar a quitar la clave primaria de la tabla. Haz las inserciones, y después de terminarlas haces un alter table y le añades el índice (si es que resulta necesario para el procedimiento almacenado). Añadir el índice cuando la tabla ya está completa suele ser más rápido que indexar los registros sobre la marcha según se van creando.

    Si no necesitas expresamente el identity por algún otro motivo, suprímelo. Fíjate que aunque el campo se llame "minuto" en realidad contiene segundos, y nunca le insertas dos valores iguales, por lo que vale perfectamente como clave primaria sin necesidad de agregarle el identity.

    • Marcado como respuesta kumekk domingo, 3 de febrero de 2019 10:00
    sábado, 2 de febrero de 2019 22:01

Todas las respuestas

  • El While tarda casi 1segundo para un intervalo de 5h.

    Bueno según eso estamos hablando de que el bucle logra realizar unas 18.000 inserciones por segundo, que no está mal. No olvidemos que la tabla en realidad está en disco (la variable tipo table se crea en el tempdb), y que tiene una clave de tipo clustered, que se tiene que actualizar por cada registro. Y que las inserciones en el tempdb están logadas, por lo que estás limitado por la velocidad de grabación del .ldf del tempdb.

    Si quieres acelerar el bucle, puedes probar a quitar la clave primaria de la tabla. Haz las inserciones, y después de terminarlas haces un alter table y le añades el índice (si es que resulta necesario para el procedimiento almacenado). Añadir el índice cuando la tabla ya está completa suele ser más rápido que indexar los registros sobre la marcha según se van creando.

    Si no necesitas expresamente el identity por algún otro motivo, suprímelo. Fíjate que aunque el campo se llame "minuto" en realidad contiene segundos, y nunca le insertas dos valores iguales, por lo que vale perfectamente como clave primaria sin necesidad de agregarle el identity.

    • Marcado como respuesta kumekk domingo, 3 de febrero de 2019 10:00
    sábado, 2 de febrero de 2019 22:01
  • Gracias Alberto, por tu aporte, el identity lo utilizo luego, y no lo puedo eliminar, voy a probar a eliminar la clave y crearla luego. 
    domingo, 3 de febrero de 2019 9:38
  • Hola de nuevo Alberto,

    Tras probar el crear después las claves principales, tu puedo confirmar que se aprecia una diferencia muy pequeña en el tiempo. apenas unos milisegundos. Pero algo es algo.

    Te agradezco mucho tu tiempo. 

    • Editado kumekk domingo, 3 de febrero de 2019 10:00
    domingo, 3 de febrero de 2019 9:57