Table valued Function Performance

  • Hello

    In the last week, my company installed a new machine with SQL Server 2005, i had to migrate my database to this server, before this it was in the SQL Server 2000, but after some tests i realize that some of the sp's and functions are slower than in the 2000, i didn't test everything in my database.

    I have one particular table valued function, that in the 2000 version takes 11 seconds and in the new one it takes more that 15 minutes to finish...

    I already rebuild, clean, recompile, update statistics and the final result was the same...

    It's the table var the problem? Should i "replace it" by a temp table?

    Thanks

  • I remember facing same sort of issues. However updating the statistics of tables involved solved the issue. Can you send the function definition ?

  • Yes, but it's a big function and i didn't have the time to optimize.

    CREAT FUNCTION [dbo].[Carga]

    (

    @DataHojeDATETIME,

    @MinModelacaoINTEGER,

    @MinFabricoINTEGER,

    @MinExpedicaoINTEGER,

    @IDREVINTEGER,

    @IDSALINTEGER

    )

    RETURNS

    @TabelaCargaPrevistaFabrico TABLE

    (

    DataDATETIME,

    ValorRevDECIMAL(10,3),

    ValorSalDECIMAL(10,3),

    ValorTotDECIMAL(10,3)

    )

    AS

    BEGIN

    DECLARE

    -- Cursor -- Valores Lidos da Tabela --

    @DataTempDATETIME,

    @DataInicio DATETIME,

    @DataFimDATETIME,

    @SeccaoINTEGER,

    @HorasTotal DECIMAL(10,3),

    @HorasRevDECIMAL(10,3),

    @HorasSalDECIMAL(10,3),

    --

    @RowActualINTEGER,

    @IntervaloINTEGER,

    @ValorDiaDECIMAL(10,3),

    -- Cursor Data ---

    @DataCursor DATETIME,

    @DataTabela DATETIME,

    @HorasREVTabela DECIMAL(10,3),

    @HorasSALTabela DECIMAL(10,3),

    @HorasTOTTabela DECIMAL(10,3),

    @HorasDiaREV DECIMAL(10,3),

    @HorasDiaSAL DECIMAL(10,3),

    @HorasDiaTOT DECIMAL(10,3)

    ----------------------

    -- 1 Ponto PRV GAB MOD

    ----------------------

    DECLARE cur CURSOR FOR

    SELECT DataPrevistaEntregaGP,DataPrevistaEntregaObra,PA.Seccao,(PF.Quantidade / RGF.Racio) AS Horas

    FROM PF INNER JOIN dbo.produto_acabado PA ON PF.produtoAcabado=PA.id_produto_acabado

    INNER JOIN dbo.racioGF RGF ON RGF.produtoAcabado=PA.id_produto_acabado AND RGF.Ano = DATEPART(YEAR,@DataHoje)

    INNER JOIN dbo.seccao SC ON SC.idSeccao = PA.seccao

    WHERE EntregaDirectaFabrico=0 AND EnviadoGF=0 AND EnviadoGP=0 AND Recolhido=0 AND Terminado=0

    --- Trata do processamento linha a linha

    OPEN cur

    FETCH NEXT FROM cur INTO @DataTemp,@DataFim,@Seccao,@HorasTotal

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Entrega na Modelacao tenho de lhe dar o intervalo de dias da mesma

    SELECT @DataInicio = C.Data

    FROM dbo.Calendario C

    WHERE C.Feriado=0 AND C.DiaSemana=1 AND C.Data <= DATEADD(DAY,(10 * @MinModelacao),@DataFim) --Segurança no intervalo

    AND @MinModelacao = (

    SELECT COUNT(C2.Data)

    FROM dbo.Calendario C2

    WHERE C2.Data >= @DataTemp

    AND C2.Data <= C.Data

    AND C2.DiaSemana=1

    AND C2.Feriado=0

    )

    -- Segurança nas datas

    IF @DataInicio < @DataHoje SET @DataInicio = @DataHoje

    IF @DataFim < @DataHoje SET @DataFim = @DataHoje

    -- Devolve dias úteis dipsoniveis

    SELECT @Intervalo = COUNT(Data) FROM dbo.Calendario WHERE (Data BETWEEN @DataInicio AND @DataFim) AND Feriado=0 AND DiaSemana = 1

    IF @Intervalo <= 1 SET @Intervalo = 1

    SET @ValorDia = @HorasTotal / @Intervalo

    IF @ValorDia < 0 SET @ValorDia = 0

    SET @HorasDiaREV = 0

    SET @HorasDiaSAL = 0

    SET @HorasDiaTOT = @ValorDIa

    IF @Seccao=@IDREV

    SET @HorasDiaREV = @ValorDia

    ELSE

    SET @HorasDiaSAL = @ValorDia

    SET @RowActual = 1

    DECLARE curData CURSOR FOR

    SELECT Data FROM dbo.Calendario WHERE Data>=@DataInicio AND Feriado=0 AND DiaSemana = 1

    OPEN curData

    FETCH NEXT FROM curData INTO @DataCursor

    WHILE @@FETCH_STATUS = 0 AND @RowActual <= @Intervalo

    BEGIN

    SET @RowActual = @RowActual + 1

    SET @DataTabela = NULL

    SET @HorasREVTabela = 0

    SET@HorasSALTabela = 0

    SET@HorasTOTTabela = 0

    SELECT@DataTabela = Data,

    @HorasREVTabela = ValorRev,

    @HorasSALTabela = ValorSal,

    @HorasTOTTabela = ValorTot

    FROM @TabelaCargaPrevistaFabrico WHERE Data = @DataCursor

    IF @DataTabela IS NULL

    INSERT INTO @TabelaCargaPrevistaFabrico VALUES (@DataCursor,@HorasDiaREV,@HorasDiaSAL,@HorasDiaTOT)

    ELSE

    UPDATE @TabelaCargaPrevistaFabrico

    SET ValorRev = (@HorasREVTabela+@HorasDiaREV),

    ValorSal = (@HorasSALTabela+@HorasDiaSAL),

    ValorTot = (@HorasTOTTabela+@HorasDiaTOT)

    WHERE

    Data = @DataCursor

    FETCH NEXT FROM curData INTO @DataCursor

    END

    CLOSE curData

    DEALLOCATE curData

    FETCH NEXT FROM cur INTO @DataTemp,@DataFim,@Seccao,@HorasTotal

    END

    CLOSE cur

    DEALLOCATE cur

    ----------------------

    -- 2 Ponto ENV GAB MOD

    ----------------------

    DECLARE cur CURSOR FOR

    SELECT DataEnvioGP,DataPrevistaEntregaObra,PA.Seccao,(PF.Quantidade / RGF.Racio) AS Horas

    FROM PF INNER JOIN dbo.produto_acabado PA ON PF.produtoAcabado=PA.id_produto_acabado

    INNER JOIN dbo.racioGF RGF ON RGF.produtoAcabado=PA.id_produto_acabado AND RGF.Ano = DATEPART(YEAR,@DataHoje)

    INNER JOIN dbo.seccao SC ON SC.idSeccao = PA.seccao

    WHERE EntregaDirectaFabrico=0 AND EnviadoGF=0 AND EnviadoGP=1 AND AceiteGP=0 AND Recolhido=0 AND Terminado=0

    --- Trata do processamento linha a linha

    OPEN cur

    FETCH NEXT FROM cur INTO @DataTemp,@DataFim,@Seccao,@HorasTotal

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Entrega na Modelacao tenho de lhe dar o intervalo de dias da mesma

    SELECT @DataInicio = C.Data

    FROM dbo.Calendario C

    WHERE C.Feriado=0 AND C.DiaSemana=1 AND C.Data <= DATEADD(DAY,(10 * @MinModelacao),@DataFim) --Segurança no intervalo

    AND @MinModelacao = (

    SELECT COUNT(C2.Data)

    FROM dbo.Calendario C2

    WHERE C2.Data >= @DataTemp

    AND C2.Data <= C.Data

    AND C2.DiaSemana=1

    AND C2.Feriado=0

    )

    -- Segurança nas datas

    IF @DataInicio < @DataHoje SET @DataInicio = @DataHoje

    IF @DataFim < @DataHoje SET @DataFim = @DataHoje

    -- Devolve dias úteis dipsoniveis

    SELECT @Intervalo = COUNT(Data) FROM dbo.Calendario WHERE (Data BETWEEN @DataInicio AND @DataFim) AND Feriado=0 AND DiaSemana = 1

    IF @Intervalo <= 1 SET @Intervalo = 1

    SET @ValorDia = @HorasTotal / @Intervalo

    IF @ValorDia < 0 SET @ValorDia = 0

    SET @HorasDiaREV = 0

    SET @HorasDiaSAL = 0

    SET @HorasDiaTOT = @ValorDIa

    IF @Seccao=@IDREV

    SET @HorasDiaREV = @ValorDia

    ELSE

    SET @HorasDiaSAL = @ValorDia

    SET @RowActual = 1

    DECLARE curData CURSOR FOR

    SELECT Data FROM dbo.Calendario WHERE Data>=@DataInicio AND Feriado=0 AND DiaSemana = 1

    OPEN curData

    FETCH NEXT FROM curData INTO @DataCursor

    WHILE @@FETCH_STATUS = 0 AND @RowActual <= @Intervalo

    BEGIN

    SET @RowActual = @RowActual + 1

    SET @DataTabela = NULL

    SET @HorasREVTabela = 0

    SET@HorasSALTabela = 0

    SET@HorasTOTTabela = 0

    SELECT@DataTabela = Data,

    @HorasREVTabela = ValorRev,

    @HorasSALTabela = ValorSal,

    @HorasTOTTabela = ValorTot

    FROM @TabelaCargaPrevistaFabrico WHERE Data = @DataCursor

    IF @DataTabela IS NULL

    INSERT INTO @TabelaCargaPrevistaFabrico VALUES (@DataCursor,@HorasDiaREV,@HorasDiaSAL,@HorasDiaTOT)

    ELSE

    UPDATE @TabelaCargaPrevistaFabrico

    SET ValorRev = (@HorasREVTabela+@HorasDiaREV),

    ValorSal = (@HorasSALTabela+@HorasDiaSAL),

    ValorTot = (@HorasTOTTabela+@HorasDiaTOT)

    WHERE

    Data = @DataCursor

    FETCH NEXT FROM curData INTO @DataCursor

    END

    CLOSE curData

    DEALLOCATE curData

    FETCH NEXT FROM cur INTO @DataTemp,@DataFim,@Seccao,@HorasTotal

    END

    CLOSE cur

    DEALLOCATE cur

    -----------------------------------

    -- 3 Ponto ACEITE/ATRIBUIDO GAB MOD

    -----------------------------------

    DECLARE cur CURSOR FOR

    SELECT DataPrevistaGF,DataPrevistaObra,PA.Seccao,(PF.Quantidade / RGF.Racio) AS Horas

    FROM PF INNER JOIN dbo.produto_acabado PA ON PF.produtoAcabado=PA.id_produto_acabado

    INNER JOIN dbo.racioGF RGF ON RGF.produtoAcabado=PA.id_produto_acabado AND RGF.Ano = DATEPART(YEAR,@DataHoje)

    INNER JOIN dbo.seccao SC ON SC.idSeccao = PA.seccao

    WHERE EntregaDirectaFabrico=0 AND EnviadoGF=0 AND EnviadoGP=1 AND AceiteGP=1 AND Recolhido=0 AND Terminado=0

    --- Trata do processamento linha a linha

    OPEN cur

    FETCH NEXT FROM cur INTO @DataInicio,@DataFim,@Seccao,@HorasTotal

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Segurança nas datas

    IF @DataInicio < @DataHoje SET @DataInicio = @DataHoje

    IF @DataFim < @DataHoje SET @DataFim = @DataHoje

    -- Devolve dias úteis dipsoniveis

    SELECT @Intervalo = COUNT(Data) FROM dbo.Calendario WHERE (Data BETWEEN @DataInicio AND @DataFim) AND Feriado=0 AND DiaSemana = 1

    IF @Intervalo <= 1 SET @Intervalo = 1

    SET @ValorDia = @HorasTotal / @Intervalo

    IF @ValorDia < 0 SET @ValorDia = 0

    SET @HorasDiaREV = 0

    SET @HorasDiaSAL = 0

    SET @HorasDiaTOT = @ValorDIa

    IF @Seccao=@IDREV

    SET @HorasDiaREV = @ValorDia

    ELSE

    SET @HorasDiaSAL = @ValorDia

    SET @RowActual = 1

    DECLARE curData CURSOR FOR

    SELECT Data FROM dbo.Calendario WHERE Data>=@DataInicio AND Feriado=0 AND DiaSemana = 1

    OPEN curData

    FETCH NEXT FROM curData INTO @DataCursor

    WHILE @@FETCH_STATUS = 0 AND @RowActual <= @Intervalo

    BEGIN

    SET @RowActual = @RowActual + 1

    SET @DataTabela = NULL

    SET @HorasREVTabela = 0

    SET@HorasSALTabela = 0

    SET@HorasTOTTabela = 0

    SELECT@DataTabela = Data,

    @HorasREVTabela = ValorRev,

    @HorasSALTabela = ValorSal,

    @HorasTOTTabela = ValorTot

    FROM @TabelaCargaPrevistaFabrico WHERE Data = @DataCursor

    IF @DataTabela IS NULL

    INSERT INTO @TabelaCargaPrevistaFabrico VALUES (@DataCursor,@HorasDiaREV,@HorasDiaSAL,@HorasDiaTOT)

    ELSE

    UPDATE @TabelaCargaPrevistaFabrico

    SET ValorRev = (@HorasREVTabela+@HorasDiaREV),

    ValorSal = (@HorasSALTabela+@HorasDiaSAL),

    ValorTot = (@HorasTOTTabela+@HorasDiaTOT)

    WHERE

    Data = @DataCursor

    FETCH NEXT FROM curData INTO @DataCursor

    END

    CLOSE curData

    DEALLOCATE curData

    FETCH NEXT FROM cur INTO @DataInicio,@DataFim,@Seccao,@HorasTotal

    END

    CLOSE cur

    DEALLOCATE cur

    ----------------------------

    -- 4 Ponto ENV GAB FAB MOD - Já envia com Horas

    ----------------------------

    DECLARE cur CURSOR FOR

    SELECT DataEnvioGF,DataPrevistaObra,PA.Seccao,

    (PF.RevCorteAuto + PF.RevCorteGuilhotina + PF.RevCunhagem + PF.RevDobragemQuinagem + PF.RevMontagemInterna) AS HorasRev,

    (PF.SalCorte + PF.SalMaquinagemAuto + PF.SalMaquinagemManual + PF.SalMontagemInterna) AS HorasSal

    FROM PF INNER JOIN dbo.produto_acabado PA ON PF.produtoAcabado=PA.id_produto_acabado

    INNER JOIN dbo.racioGF RGF ON RGF.produtoAcabado=PA.id_produto_acabado AND RGF.Ano = DATEPART(YEAR,@DataHoje)

    INNER JOIN dbo.seccao SC ON SC.idSeccao = PA.seccao

    WHERE EntregaDirectaFabrico=0 AND EnviadoGF=1 AND AceiteGF=0 AND Recolhido=0 AND Terminado=0

    --- Trata do processamento linha a linha

    OPEN cur

    FETCH NEXT FROM cur INTO @DataInicio,@DataFim,@Seccao,@HorasRev,@HorasSal

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Segurança nas datas

    IF @DataInicio < @DataHoje SET @DataInicio = @DataHoje

    IF @DataFim < @DataHoje SET @DataFim = @DataHoje

    -- Devolve dias úteis dipsoniveis

    SELECT @Intervalo = COUNT(Data) FROM dbo.Calendario WHERE (Data BETWEEN @DataInicio AND @DataFim) AND Feriado=0 AND DiaSemana = 1

    IF @Intervalo <= 1 SET @Intervalo = 1

    SET @HorasDiaREV = @HorasRev / @Intervalo

    SET @HorasDiaSAL = @HorasSal / @Intervalo

    SET @HorasDiaTOT = (@HorasRev + @HorasSal) / @Intervalo

    IF @HorasDiaREV < 0 SET @HorasDiaREV = 0

    IF @HorasDiaSAL < 0 SET @HorasDiaSAL = 0

    IF @ValorDia < 0 SET @ValorDia = 0

    SET @RowActual = 1

    DECLARE curData CURSOR FOR

    SELECT Data FROM dbo.Calendario WHERE Data>=@DataInicio AND Feriado=0 AND DiaSemana = 1

    OPEN curData

    FETCH NEXT FROM curData INTO @DataCursor

    WHILE @@FETCH_STATUS = 0 AND @RowActual <= @Intervalo

    BEGIN

    SET @RowActual = @RowActual + 1

    SET @DataTabela = NULL

    SET @HorasREVTabela = 0

    SET@HorasSALTabela = 0

    SET@HorasTOTTabela = 0

    SELECT@DataTabela = Data,

    @HorasREVTabela = ValorRev,

    @HorasSALTabela = ValorSal,

    @HorasTOTTabela = ValorTot

    FROM @TabelaCargaPrevistaFabrico WHERE Data = @DataCursor

    IF @DataTabela IS NULL

    INSERT INTO @TabelaCargaPrevistaFabrico VALUES (@DataCursor,@HorasDiaREV,@HorasDiaSAL,@HorasDiaTOT)

    ELSE

    UPDATE @TabelaCargaPrevistaFabrico

    SET ValorRev = (@HorasREVTabela+@HorasDiaREV),

    ValorSal = (@HorasSALTabela+@HorasDiaSAL),

    ValorTot = (@HorasTOTTabela+@HorasDiaTOT)

    WHERE

    Data = @DataCursor

    FETCH NEXT FROM curData INTO @DataCursor

    END

    CLOSE curData

    DEALLOCATE curData

    FETCH NEXT FROM cur INTO @DataInicio,@DataFim,@Seccao,@HorasRev,@HorasSal

    END

    CLOSE cur

    DEALLOCATE cur

    -------------------------

    -- 5 Ponto DO PRV GAB FAB

    -------------------------

    DECLARE cur CURSOR FOR

    SELECT DataPrevistaEntregaGF,DataPrevistaEntregaObra,PA.Seccao,(PF.Quantidade / RGF.Racio) AS Horas

    FROM PF INNER JOIN dbo.produto_acabado PA ON PF.produtoAcabado=PA.id_produto_acabado

    INNER JOIN dbo.racioGF RGF ON RGF.produtoAcabado=PA.id_produto_acabado AND RGF.Ano = DATEPART(YEAR,@DataHoje)

    INNER JOIN dbo.seccao SC ON SC.idSeccao = PA.seccao

    WHERE EntregaDirectaFabrico=1 AND EnviadoGF=0 AND Recolhido=0 AND Terminado=0

    --- Trata do processamento linha a linha

    OPEN cur

    FETCH NEXT FROM cur INTO @DataInicio,@DataFim,@Seccao,@HorasTotal

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Segurança nas datas

    IF @DataInicio < @DataHoje SET @DataInicio = @DataHoje

    IF @DataFim < @DataHoje SET @DataFim = @DataHoje

    -- Devolve dias úteis dipsoniveis

    SELECT @Intervalo = COUNT(Data) FROM dbo.Calendario WHERE (Data BETWEEN @DataInicio AND @DataFim) AND Feriado=0 AND DiaSemana = 1

    IF @Intervalo <= 1 SET @Intervalo = 1

    SET @ValorDia = @HorasTotal / @Intervalo

    IF @ValorDia < 0 SET @ValorDia = 0

    SET @HorasDiaREV = 0

    SET @HorasDiaSAL = 0

    SET @HorasDiaTOT = @ValorDIa

    IF @Seccao=@IDREV

    SET @HorasDiaREV = @ValorDia

    ELSE

    SET @HorasDiaSAL = @ValorDia

    SET @RowActual = 1

    DECLARE curData CURSOR FOR

    SELECT Data FROM dbo.Calendario WHERE Data>=@DataInicio AND Feriado=0 AND DiaSemana = 1

    OPEN curData

    FETCH NEXT FROM curData INTO @DataCursor

    WHILE @@FETCH_STATUS = 0 AND @RowActual <= @Intervalo

    BEGIN

    SET @RowActual = @RowActual + 1

    SET @DataTabela = NULL

    SET @HorasREVTabela = 0

    SET@HorasSALTabela = 0

    SET@HorasTOTTabela = 0

    SELECT@DataTabela = Data,

    @HorasREVTabela = ValorRev,

    @HorasSALTabela = ValorSal,

    @HorasTOTTabela = ValorTot

    FROM @TabelaCargaPrevistaFabrico WHERE Data = @DataCursor

    IF @DataTabela IS NULL

    INSERT INTO @TabelaCargaPrevistaFabrico VALUES (@DataCursor,@HorasDiaREV,@HorasDiaSAL,@HorasDiaTOT)

    ELSE

    UPDATE @TabelaCargaPrevistaFabrico

    SET ValorRev = (@HorasREVTabela+@HorasDiaREV),

    ValorSal = (@HorasSALTabela+@HorasDiaSAL),

    ValorTot = (@HorasTOTTabela+@HorasDiaTOT)

    WHERE

    Data = @DataCursor

    FETCH NEXT FROM curData INTO @DataCursor

    END

    CLOSE curData

    DEALLOCATE curData

    FETCH NEXT FROM cur INTO @DataInicio,@DataFim,@Seccao,@HorasTotal

    END

    CLOSE cur

    DEALLOCATE cur

    -------------------------

    -- 6 Ponto DO ENV GAB FAB

    -------------------------

    DECLARE cur CURSOR FOR

    SELECT DataEnvioGF,DataPrevistaObra,PA.Seccao,

    (PF.RevCorteAuto + PF.RevCorteGuilhotina + PF.RevCunhagem + PF.RevDobragemQuinagem + PF.RevMontagemInterna) AS HorasRev,

    (PF.SalCorte + PF.SalMaquinagemAuto + PF.SalMaquinagemManual + PF.SalMontagemInterna) AS HorasSal

    FROM PF INNER JOIN dbo.produto_acabado PA ON PF.produtoAcabado=PA.id_produto_acabado

    INNER JOIN dbo.racioGF RGF ON RGF.produtoAcabado=PA.id_produto_acabado AND RGF.Ano = DATEPART(YEAR,@DataHoje)

    INNER JOIN dbo.seccao SC ON SC.idSeccao = PA.seccao

    WHERE EntregaDirectaFabrico=1 AND EnviadoGF=1 AND AceiteGF=0 AND Recolhido=0 AND Terminado=0

    --- Trata do processamento linha a linha

    OPEN cur

    FETCH NEXT FROM cur INTO @DataInicio,@DataFim,@Seccao,@HorasRev,@HorasSal

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Segurança nas datas

    IF @DataInicio < @DataHoje SET @DataInicio = @DataHoje

    IF @DataFim < @DataHoje SET @DataFim = @DataHoje

    -- Devolve dias úteis dipsoniveis

    SELECT @Intervalo = COUNT(Data) FROM dbo.Calendario WHERE (Data BETWEEN @DataInicio AND @DataFim) AND Feriado=0 AND DiaSemana = 1

    IF @Intervalo <= 1 SET @Intervalo = 1

    SET @HorasDiaREV = @HorasRev / @Intervalo

    SET @HorasDiaSAL = @HorasSal / @Intervalo

    SET @HorasDiaTOT = (@HorasRev + @HorasSal) / @Intervalo

    IF @HorasDiaREV < 0 SET @HorasDiaREV = 0

    IF @HorasDiaSAL < 0 SET @HorasDiaSAL = 0

    IF @ValorDia < 0 SET @ValorDia = 0

    SET @RowActual = 1

    DECLARE curData CURSOR FOR

    SELECT Data FROM dbo.Calendario WHERE Data>=@DataInicio AND Feriado=0 AND DiaSemana = 1

    OPEN curData

    FETCH NEXT FROM curData INTO @DataCursor

    WHILE @@FETCH_STATUS = 0 AND @RowActual <= @Intervalo

    BEGIN

    SET @RowActual = @RowActual + 1

    SET @DataTabela = NULL

    SET @HorasREVTabela = 0

    SET@HorasSALTabela = 0

    SET@HorasTOTTabela = 0

    SELECT@DataTabela = Data,

    @HorasREVTabela = ValorRev,

    @HorasSALTabela = ValorSal,

    @HorasTOTTabela = ValorTot

    FROM @TabelaCargaPrevistaFabrico WHERE Data = @DataCursor

    IF @DataTabela IS NULL

    INSERT INTO @TabelaCargaPrevistaFabrico VALUES (@DataCursor,@HorasDiaREV,@HorasDiaSAL,@HorasDiaTOT)

    ELSE

    UPDATE @TabelaCargaPrevistaFabrico

    SET ValorRev = (@HorasREVTabela+@HorasDiaREV),

    ValorSal = (@HorasSALTabela+@HorasDiaSAL),

    ValorTot = (@HorasTOTTabela+@HorasDiaTOT)

    WHERE

    Data = @DataCursor

    FETCH NEXT FROM curData INTO @DataCursor

    END

    CLOSE curData

    DEALLOCATE curData

    FETCH NEXT FROM cur INTO @DataInicio,@DataFim,@Seccao,@HorasRev,@HorasSal

    END

    CLOSE cur

    DEALLOCATE cur

    RETURN

    END

  • A quick look and I can see big usage of dbo.Calendario table. Can you check if statistics are updated and all the indexes are defragmented on this table ?

  • I ran this commands:

    DBCC DBREINDEX ('Calendario', ' ', 80)

    UPDATE STATISTICS dbo.Calendario

    DBCC SHOWCONTIG ('Calendario')

    Results:

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    DBCC SHOWCONTIG scanning 'Calendario' table...

    Table: 'Calendario' (2019538278); index ID: 1, database ID: 10

    TABLE level scan performed.

    - Pages Scanned................................: 110

    - Extents Scanned..............................: 14

    - Extent Switches..............................: 13

    - Avg. Pages per Extent........................: 7.9

    - Scan Density [Best Count:Actual Count].......: 100.00% [14:14]

    - Logical Scan Fragmentation ..................: 0.00%

    - Extent Scan Fragmentation ...................: 0.00%

    - Avg. Bytes Free per Page.....................: 1613.5

    - Avg. Page Density (full).....................: 80.07%

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  • Best way:

    Try to divide the queries and run separately to check which part is taking how much time. Catch the performance of the six cursors one at a time anc check the performance:

    -- 1 Ponto PRV GAB MOD

    -- 2 Ponto ENV GAB MOD

    -- 3 Ponto ACEITE/ATRIBUIDO GAB MOD

    -- 4 Ponto ENV GAB FAB MOD - Já envia com Horas

    -- 5 Ponto DO PRV GAB FAB

    -- 6 Ponto DO ENV GAB FAB

  • the devil is in the details.

    Can you post the actual execution plan as a .sqlplan xml file, so we can look at it and identify what it's running so slow?

    it'll be educational for everyone, and help you identify performance issues in the future as well.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Here it is...

  • ok now i see it; 5 cursors are being executed for each row;sqlnaive hit it on the issue first; you've got to break it up, or change the logic to return an inline table value function instead;

    i'll look at the code a little more, but that's the first thing;

    a

    also note the missing index in the sql plan; i'd create that missing index and see how the performance improves with the existing logic:

    CREATE NONCLUSTERED INDEX [IX_EntregaDirectaFabrico_SUGGESTED]

    ON [dbo].[PF] ([EntregaDirectaFabrico],[EnviadoGP],[EnviadoGF],[Recolhido],[Terminado])

    INCLUDE ([idPF],[DataPrevistaEntregaGP],[DataPrevistaEntregaObra],[produtoAcabado],[Quantidade])

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks

    Like i wrote i didn't had the time to optimize the function...

    But the main problem here it's why in 2000 only takes 11 seconds and now in the 2005 version takes around 16 minutes (last time 22). The 2005 version doesn't "look" to the query in the same way?!

    I'll try to do that changes that you recommend, and see what happens.

    Other thing setting the compatibility mode to 2005 should make any difference?

    THanks

  • rootfixxxer (5/6/2011)


    Thanks

    Like i wrote i didn't had the time to optimize the function...

    But the main problem here it's why in 2000 only takes 11 seconds and now in the 2005 version takes around 16 minutes (last time 22). The 2005 version doesn't "look" to the query in the same way?!

    I'll try to do that changes that you recommend, and see what happens.

    Other thing setting the compatibility mode to 2005 should make any difference?

    THanks

    we'd have to see the 2000 execution plan to see what is different; typically what i've seen from a db upgraded from 2000 is that the statistics are incorrect; I've seen posts suggesting not just updating statistics, but dropping statistics and recreating them to fix performance issues after an upgrade.

    changing compatibility just filters what SQL commands are allowed to be run/processed; it would not affect HOW things are processed.

    also, this is an estimated execution paln, where the actual execution paln is more helpful.

    how many rows are acutally returned?

    for example, the clustered index scan estimates 10957 rows. if the query is returning much fewer rows than that, that still implies bad stats.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks

    The returning number it's aprox. 500, but it's not a fixed number, i just set the compatibility mode to 9, it was in the 8, and now it "only" takes 1,2 minutes... wired.

    The execution plan returned when i execute the command, doesn't show anything, just a select cost 0, a sequence cost 0, and a table scan that costs 100...

    So what you recommend me to do? Drop the stats and recreating them? And how to do this?

    Thanks

  • this is the lsit of tables used int he query; can you try this update statistics command?

    UPDATE STATISTICS [CWT] WITH FULLSCAN

    UPDATE STATISTICS [Calendario] WITH FULLSCAN

    UPDATE STATISTICS [PF] WITH FULLSCAN

    UPDATE STATISTICS [fuCargaPrevistaFabrico] WITH FULLSCAN

    UPDATE STATISTICS [produto_acabado] WITH FULLSCAN

    UPDATE STATISTICS [racioGF] WITH FULLSCAN

    UPDATE STATISTICS [seccao] WITH FULLSCAN

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • The same times 1,3 minutes....

    Need to redesign the cursors, but i don't see how to ... 🙂

  • rootfixxxer (5/6/2011)


    The same times 1,3 minutes....

    Need to redesign the cursors, but i don't see how to ... 🙂

    I see three things that can be involved in the slow performance:

    1. Cursors - you need to move to set-based logic.

    2. Potential triangular joins (check out this article[/url]).

    3. Use of the BETWEEN clause on columns / variables with a DATETIME data type.

    If you can get rid of the cursors and triangular joins, you should see this run drastically faster.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply