May 6, 2011 at 2:42 am
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
May 6, 2011 at 4:00 am
I remember facing same sort of issues. However updating the statistics of tables involved solved the issue. Can you send the function definition ?
May 6, 2011 at 4:09 am
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
May 6, 2011 at 4:21 am
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 ?
May 6, 2011 at 4:52 am
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.
May 6, 2011 at 5:30 am
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
May 6, 2011 at 6:22 am
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
May 6, 2011 at 7:55 am
Here it is...
May 6, 2011 at 8:15 am
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
May 6, 2011 at 8:21 am
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
May 6, 2011 at 8:25 am
rootfixxxer (5/6/2011)
ThanksLike 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
May 6, 2011 at 8:53 am
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
May 6, 2011 at 9:10 am
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
May 6, 2011 at 9:25 am
The same times 1,3 minutes....
Need to redesign the cursors, but i don't see how to ... 🙂
May 6, 2011 at 9:59 am
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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply