#sql-server #tsql
#sql-server #tsql
Вопрос:
Сегодня я начал оптимизировать различные процедуры базы данных в SQL Server.
Я хотел бы знать, можете ли вы помочь мне оптимизировать следующий запрос:
DECLARE @fkTipoDato INTEGER = 8;
DECLARE @fkPlanta INTEGER = 1;
DECLARE @FechaInicio DATETIME = CAST('2020-27-10 00:00:00' AS DATETIME);
DECLARE @FechaFin DATETIME = CAST('2020-28-10 00:00:00' AS DATETIME);
DECLARE @FechaWhile DATETIME = @FechaInicio;
DECLARE @valorMaximoMedidor REAL = 2999999;
DECLARE @TablaTemp TABLE (
tempFecha VARCHAR(20)
,cero REAL
,uno REAL
,dos REAL
,tres REAL
,cuatro REAL
,cinco REAL
,seis REAL
,siete REAL
,ocho REAL
,nueve REAL
,diez REAL
,ONCE REAL
,doce REAL
,trece REAL
,catorce REAL
,quince REAL
,dieciseis REAL
,diecisiete REAL
,dieciocho REAL
,diecinueve REAL
,veinte REAL
,veintiuno REAL
,veintidos REAL
,veintitres REAL
);
WHILE @FechaWhile <= @FechaFin
BEGIN
SET NOCOUNT ON;
DECLARE @ValorFinal0 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= CAST(@FechaWhile AS DATETIME)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 1, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial0 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= CAST(@FechaWhile AS DATETIME)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 1, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal1 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 1, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 2, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial1 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 1, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 2, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal2 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 2, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 3, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial2 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 2, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 3, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal3 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 3, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 4, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial3 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 3, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 4, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal4 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 4, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 5, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial4 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 4, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 5, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal5 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 5, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 6, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial5 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 5, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 6, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal6 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 6, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 7, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial6 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 6, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 7, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal7 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 7, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 8, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial7 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 7, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 8, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal8 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 8, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 9, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial8 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 8, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 9, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal9 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 9, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 10, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial9 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 9, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 10, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal10 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 10, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 11, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial10 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 10, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 11, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal11 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 11, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 12, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial11 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 11, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 12, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal12 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 12, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 13, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial12 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 12, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 13, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal13 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 13, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 14, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial13 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 13, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 14, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal14 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 14, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 15, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial14 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 14, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 15, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal15 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 15, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 16, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial15 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 15, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 16, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal16 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 16, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 17, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial16 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 16, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 17, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal17 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 17, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 18, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial17 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 17, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 18, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal18 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 18, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 19, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial18 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 18, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 19, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal19 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 19, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 20, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial19 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 19, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 20, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal20 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 20, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 21, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial20 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 20, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 21, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal21 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 21, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 22, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial21 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 21, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 22, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal22 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 22, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 23, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial22 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 22, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 23, @FechaWhile))
ORDER BY FechaHora ASC
);
DECLARE @ValorFinal23 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 23, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 24, @FechaWhile))
ORDER BY FechaHora DESC
);
DECLARE @ValorInicial23 REAL = (
SELECT TOP 1 valor
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= DATEADD(HOUR, 23, @FechaWhile)
AND FechaHora <= DATEADD(MILLISECOND, - 1, DATEADD(HOUR, 24, @FechaWhile))
ORDER BY FechaHora ASC
);
IF EXISTS (
SELECT TOP 1 FechaHora
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND FechaHora >= @FechaWhile
AND FechaHora <= DATEADD(day, 1, @FechaWhile)
AND fk_Planta = @fkPlanta
)
BEGIN
INSERT INTO @TablaTemp
SELECT TOP 1 CONVERT(VARCHAR(10), FechaHora, 103) AS [Fecha]
,(
CASE
WHEN @ValorFinal0 >= @ValorInicial0
THEN (
SELECT (@ValorFinal0 - @ValorInicial0)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial0) @ValorFinal0)
)
END
) AS [00:00]
,(
CASE
WHEN @ValorFinal1 >= @ValorInicial1
THEN (
SELECT (@ValorFinal1 - @ValorInicial1)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial1) @ValorFinal1)
)
END
) AS [01:00]
,(
CASE
WHEN @ValorFinal2 >= @ValorInicial2
THEN (
SELECT (@ValorFinal2 - @ValorInicial2)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial2) @ValorFinal2)
)
END
) AS [02:00]
,(
CASE
WHEN @ValorFinal3 >= @ValorInicial3
THEN (
SELECT (@ValorFinal3 - @ValorInicial3)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial3) @ValorFinal3)
)
END
) AS [03:00]
,(
CASE
WHEN @ValorFinal4 >= @ValorInicial4
THEN (
SELECT (@ValorFinal4 - @ValorInicial4)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial4) @ValorFinal4)
)
END
) AS [04:00]
,(
CASE
WHEN @ValorFinal5 >= @ValorInicial5
THEN (
SELECT (@ValorFinal5 - @ValorInicial5)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial5) @ValorFinal5)
)
END
) AS [05:00]
,(
CASE
WHEN @ValorFinal6 >= @ValorInicial6
THEN (
SELECT (@ValorFinal6 - @ValorInicial6)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial6) @ValorFinal6)
)
END
) AS [06:00]
,(
CASE
WHEN @ValorFinal7 >= @ValorInicial7
THEN (
SELECT (@ValorFinal7 - @ValorInicial7)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial7) @ValorFinal7)
)
END
) AS [07:00]
,(
CASE
WHEN @ValorFinal8 >= @ValorInicial8
THEN (
SELECT (@ValorFinal8 - @ValorInicial8)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial8) @ValorFinal8)
)
END
) AS [08:00]
,(
CASE
WHEN @ValorFinal9 >= @ValorInicial9
THEN (
SELECT (@ValorFinal9 - @ValorInicial9)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial9) @ValorFinal9)
)
END
) AS [09:00]
,(
CASE
WHEN @ValorFinal10 >= @ValorInicial10
THEN (
SELECT (@ValorFinal10 - @ValorInicial10)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial10) @ValorFinal10)
)
END
) AS [10:00]
,(
CASE
WHEN @ValorFinal11 >= @ValorInicial11
THEN (
SELECT (@ValorFinal11 - @ValorInicial11)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial11) @ValorFinal11)
)
END
) AS [11:00]
,(
CASE
WHEN @ValorFinal12 >= @ValorInicial12
THEN (
SELECT (@ValorFinal12 - @ValorInicial12)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial12) @ValorFinal12)
)
END
) AS [12:00]
,(
CASE
WHEN @ValorFinal13 >= @ValorInicial13
THEN (
SELECT (@ValorFinal13 - @ValorInicial13)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial13) @ValorFinal13)
)
END
) AS [13:00]
,(
CASE
WHEN @ValorFinal14 >= @ValorInicial14
THEN (
SELECT (@ValorFinal14 - @ValorInicial14)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial14) @ValorFinal14)
)
END
) AS [14:00]
,(
CASE
WHEN @ValorFinal15 >= @ValorInicial15
THEN (
SELECT (@ValorFinal15 - @ValorInicial15)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial15) @ValorFinal15)
)
END
) AS [15:00]
,(
CASE
WHEN @ValorFinal16 >= @ValorInicial16
THEN (
SELECT (@ValorFinal16 - @ValorInicial16)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial16) @ValorFinal16)
)
END
) AS [16:00]
,(
CASE
WHEN @ValorFinal17 >= @ValorInicial17
THEN (
SELECT (@ValorFinal17 - @ValorInicial17)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial17) @ValorFinal17)
)
END
) AS [17:00]
,(
CASE
WHEN @ValorFinal18 >= @ValorInicial18
THEN (
SELECT (@ValorFinal18 - @ValorInicial18)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial18) @ValorFinal18)
)
END
) AS [18:00]
,(
CASE
WHEN @ValorFinal19 >= @ValorInicial19
THEN (
SELECT (@ValorFinal19 - @ValorInicial19)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial19) @ValorFinal19)
)
END
) AS [19:00]
,(
CASE
WHEN @ValorFinal20 >= @ValorInicial20
THEN (
SELECT (@ValorFinal20 - @ValorInicial20)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial20) @ValorFinal20)
)
END
) AS [20:00]
,(
CASE
WHEN @ValorFinal21 >= @ValorInicial21
THEN (
SELECT (@ValorFinal21 - @ValorInicial21)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial21) @ValorFinal21)
)
END
) AS [21:00]
,(
CASE
WHEN @ValorFinal22 >= @ValorInicial22
THEN (
SELECT (@ValorFinal22 - @ValorInicial22)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial22) @ValorFinal22)
)
END
) AS [22:00]
,(
CASE
WHEN @ValorFinal23 >= @ValorInicial23
THEN (
SELECT (@ValorFinal23 - @ValorInicial23)
)
ELSE (
SELECT ((@valorMaximoMedidor - @ValorInicial23) @ValorFinal23)
)
END
) AS [23:00]
FROM RegistroDatos
WHERE fk_TipoDato = @fkTipoDato
AND FechaHora >= @FechaWhile
AND FechaHora <= DATEADD(day, 1, @FechaWhile)
AND fk_Planta = @fkPlanta
END
SET @FechaWhile = DATEADD(day, 1, @FechaWhile);
END
SELECT tempFecha AS [Fecha]
,cero AS [00:00]
,uno AS [01:00]
,dos AS [02:00]
,tres AS [03:00]
,cuatro AS [04:00]
,cinco AS [05:00]
,seis AS [06:00]
,siete AS [07:00]
,ocho AS [08:00]
,nueve AS [09:00]
,diez AS [10:00]
,ONCE AS [11:00]
,doce AS [12:00]
,trece AS [13:00]
,catorce AS [14:00]
,quince AS [15:00]
,dieciseis AS [16:00]
,diecisiete AS [17:00]
,dieciocho AS [18:00]
,diecinueve AS [19:00]
,veinte AS [20:00]
,veintiuno AS [21:00]
,veintidos AS [22:00]
,veintitres AS [23:00]
FROM @TablaTemp
Он возвращает потребление (разницу между максимальным и минимальным значением), разделенное часами и днями (что было бы легко сделать), но проблема возникает, поскольку при сохранении данных и достижении значения = 2999999 счетчик сбрасывается. Поэтому вы должны проверять первое и последнее значения вместо минимального и максимального, а затем работать с ними.
А также прикрепите код, он работает, но медленно.
ВЕРСИЯ: Microsoft SQL Server 2008 R2 (RTM)
Таблица RegistroDatos имеет следующую структуру: 
Есть предложения?
Комментарии:
1. С первого взгляда избавьтесь от всего кода -1ms и замените оператор сравнения
<=на<. И избавьтесь от всех ложныхselects.(select(@ValorFinal7-@ValorInicial7))просто@ValorFinal7-@ValorInicial7.2. итак, я уверен, что для этого есть один оператор select, использующий windowing, но я понятия не имею, что вы делаете — можете ли вы объяснить, что вы делаете, и привести пример начальных данных и результатов?
3. Пожалуйста, добавьте DDL, включая любые индексы, для
RegistroDatosвашего сообщения.4. @HABO Готово, добавьте в соответствии с запросом
5. Я удалил все -1 мс и select (@ValorFinalx — @ValorInicialx), но задержка запроса остается прежней
Ответ №1:
Я не тестировал код, потому что вы разместили свой DDL как изображение, а не как текст. Но я думаю, что я понял, чего вы пытаетесь достичь. Вы выполняете один и тот же запрос несколько раз, поэтому выполнение всей работы за один проход должно увеличить время выполнения.
WITH timeParts AS (
/* Get the date and hour parts as independent columns
to simplify expressions down the line */
SELECT
FechaHora, Valor,
CAST(FechaHora AS DATE) AS [date], --Truncate time part
DATEPART([HOUR], FechaHora) AS [hour]
FROM
dbo.RegistroDatos
WHERE
fk_TipoDato = @fkTipoDato
AND fk_Planta = @fkPlanta
AND FechaHora >= CAST(@FechaInicio AS DATETIME)
AND FechaHora < DATEADD([DAY], 1, CAST(@FechaFin AS DATETIME))
),
firstAndLast AS (
/* generate sequences to identify the first and last "valor" values */
SELECT
[date], [hour],
ROW_NUMBER() OVER(PARTITION BY [date], [hour] ORDER BY FechaHora) AS firstR,
ROW_NUMBER() OVER(PARTITION BY [date], [hour] ORDER BY FechaHora DESC) AS lastR,
Valor
FROM
timeParts
),
firstPass AS (
/* Put initial and last values side by side to simplify the comsumption calculation,
basically a pivot operation */
SELECT
[date], [hour],
MIN(CASE WHEN firstR = 1 THEN Valor END) AS initialValue,
MIN(CASE WHEN lastR = 1 THEN Valor END) AS finalValue
FROM
firstAndLast
GROUP BY
[date], [hour]
),
consumptions AS (
/* Having every value needed side by side simplifies the code:
you don't need to repeat 24 times the same code... */
SELECT
[date], [hour],
CASE
WHEN finalValue >= initialValue THEN finalValue - initialValue
ELSE @valorMaximoMedidor - initialValue finalValue
END AS consumption
FROM
firstPass
)
SELECT *
FROM
consumptions
PIVOT(MIN(consumption) FOR [hour] IN (
[0], [1], [2], [3], [4], [5], [6], [7], [8], [9],[10],[11],
[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) pvt;
Соответствующий индекс для вашего запроса еще больше увеличит время выполнения, если он еще не существует:
CREATE INDEX index_name
ON dbo.RegistroDatos(FechaHora, fk_Planta, fk_TipoDato)
INCLUDE(Valor);
Комментарии:
1. Большое спасибо!!! Это решило мою проблему. Низкое время запроса от 2 минут до 2 секунд!
Ответ №2:
Возможно, существует решение, основанное на наборе. Я бы начал с ГРУППИРОВАНИЯ по СОСТАВУ (FechaHora как дата), DATEPART(ЧАС, FechaHora). При необходимости группируйте в других столбцах. МИН и МАКС — это просто. Первое и последнее сложнее. Если у вас есть монотонно увеличивающееся или уменьшающееся значение, которое уникально, его можно использовать для получения «ключа» к первой и последней записям в каждой группе. FechaHora может подойти для этого, если нет ничего другого. В противном случае я бы попробовал решение, используя функции Windows FIRST() и LAST() .
SELECT CAST(FechaHora as date) as [FechaHora_date],
DATEPART(HOUR, FechaHora) as [FechaHora_hour],
MIN(valor) as [valor_min],
MAX(valor) as [valor_max],
MAX(valor) - MIN(valor) as [Consumption],
MIN(id) as [id_min], -- is there a min value the identifies the first record?
MAX(id) as [id_max], -- is there a max value the identifies the last record?
FROM RegistroDatos
WHERE FechaHora BETWEEN @FechaInicio AND @FechaFin
GROUP BY CAST(FechaHora as date), DATEPART(HOUR, FechaHora)
Если вам нужно использовать первую и последнюю записи, затем присоединитесь к RegistroDatosto по id_min, а затем снова по id_max, чтобы получить значение valor для обеих.
Если вам нужны записи для тех мест, где нет данных, вы можете сгенерировать таблицу чисел, чтобы создать таблицу со всеми необходимыми датами и часами. Затем присоединитесь к нему с данными, при необходимости заменив нули на ноль.
Комментарии:
1. В сообщении добавьте формат таблицы RegistroDatos