December 3, 2013 at 5:41 am
Hi.
I have a problem with this one ITVF. It was working fine, but I had to change it so that it is limiting humidity into maximum and minimum during period of time. Periods are located in dbo.HumidityChangePeriods table. So I had to convert function to recursive CTE from just summing up humidity changes.
This function is used in view so it gets called a lot.
Can anyone share ideas how to speed this up?
I attached execution plan.
USE tempdb;
-- CREATE TABLES USED
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[numbers]') AND type in (N'U'))
DROP TABLE [dbo].[numbers]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[numbers](
[n] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.numbers ON;
WITH cte (n) AS
(
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_columns AS a CROSS JOIN sys.all_columns
)
INSERT INTO numbers (n) SELECT n FROM cte;
SET IDENTITY_INSERT dbo.numbers OFF;
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[numbers]') AND name = N'NumberN')
DROP INDEX [NumberN] ON [dbo].[numbers] WITH ( ONLINE = OFF )
GO
CREATE UNIQUE CLUSTERED INDEX [NumberN] ON [dbo].[numbers]
(
[n] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
WITH cte AS
(
SELECT 1 AS periodId, CONVERT(DATE, '2000-01-01') AS periodBegin, CONVERT(DATE, '2000-02-29') AS periodEnd, 0.0 AS unitChange UNION ALL
SELECT 2 AS periodId, CONVERT(DATE, '2000-03-01') AS periodBegin, CONVERT(DATE, '2000-04-01') AS periodEnd, 0.09375 AS unitChange UNION ALL -- 0.097
SELECT 3, CONVERT(DATE, '2000-04-02'), CONVERT(DATE, '2000-06-15'), 0.16 UNION ALL -- 0.162
SELECT 4, CONVERT(DATE, '2000-06-16'), CONVERT(DATE, '2000-07-30'), 5.0/45.0 UNION ALL -- 0.114 (oikeasti 5/45)
SELECT 5, CONVERT(DATE, '2000-07-31'), CONVERT(DATE, '2000-08-31'), 0.046875 UNION ALL -- 0.048
SELECT 6, CONVERT(DATE, '2000-09-01'), CONVERT(DATE, '2000-11-30'), -12.0/91.0 UNION ALL -- -0.133 (oikeasti -12/91)
SELECT 7, CONVERT(DATE, '2000-12-01'), CONVERT(DATE, '2000-12-31'), 0.0
)
SELECT * INTO dbo.HumidityChangePeriods FROM cte ;
CREATE UNIQUE CLUSTERED INDEX [HumiditychangeperiodsPeriodbeginPeriodendUC] ON [dbo].[HumidityChangePeriods]
(
[periodBegin] ASC,
[periodEnd] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
--- PROBLEMATIC FUNCTION STARTS HERE
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[calculateCurrentHumidity]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[calculateCurrentHumidity]
GO
CREATE FUNCTION dbo.calculateCurrentHumidity(@identity INT, @beginDate DATE, @endDate DATE, @startHumidity FLOAT)
RETURNS TABLE
RETURN
-- generate all dates for time perios
WITH days AS
(
SELECT
DATEADD(DD, numbers.n - 1, @beginDate) AS calcDay
FROM dbo.numbers
WHERE n < DATEDIFF(DAY, @beginDate, @endDate) + 1
)
-- move dates to year 2000 and add year as grouping
, dayCountsPerPeriod AS
(
SELECT
DATEADD(YEAR, - 1 * YEAR(calcDay) + 2000, calcDay) AS movedDate,
YEAR(calcDay) AS calcYear
FROM days
)
-- calculate days and humidity change in period
, changes AS (
SELECT
COUNT(*) AS days
, SUM(unitChange) AS change
, ROW_NUMBER () OVER (ORDER BY calcYear, periodId) AS rno
FROM
dayCountsPerPeriod
JOIN
dbo.HumidityChangePeriods AS periods ON movedDate BETWEEN periodBegin AND periodEnd
GROUP
BY calcYear, periodId
)
-- calculate humidity change recursively because humidity cannot go over 65 and below 20
--
, rcte AS
(
SELECT
CASE
WHEN @startHumidity - changes.change > 65.0 THEN 65.0
WHEN @startHumidity - changes.change < 20.0 THEN 20.0
ELSE @startHumidity - changes.change
END
AS humidity, days, change, rno FROM changes WHERE rno = 1
UNION ALL
SELECT
CASE
WHEN humidity - changes.change > 65.0 THEN 65.0
WHEN humidity - changes.change < 20.0 THEN 20.0
ELSE humidity - changes.change
END
AS humidity
, changes.days
, changes.change
, changes.rno
FROM rcte
JOIN changes ON rcte.rno + 1 = changes.rno
-- maxrecursion is 100 so stop before that
WHERE rcte.rno < 50
)
-- return last value
SELECT TOP 1 @identity AS id, CASE WHEN rcte.rno > 48 THEN -1 ELSE humidity END AS humidity FROM rcte ORDER BY rno DESC
GO
-- TESTING
CREATE TABLE #TestData
(
testDataId INT NOT NULL,
StartHumidity NUMERIC(10,4) NULL,
BeginDate DATE NULL,
EndDate DATE NULL,
EndHumidity NUMERIC(10,4) NULL,
Explanation VARCHAR(100) NULL
)
INSERT INTO #TestData (testDataId, StartHumidity, BeginDate, EndDate, EndHumidity, Explanation)
VALUES
(1, 60, '2013-02-01', '2013-01-02', NULL, 'Begin date bigger that end date '),
(2, 60, '2013-01-01', '2013-01-02', 60, 'winter time'),
(3, 60, '2013-01-01', '2013-05-01', 52.36, ''),
(4, 60, '2013-01-01', '2013-09-01', 38.5, ''),
(5, 60, '2013-09-01', '2013-12-01', 65, 'max humidity is 65%'),
(6, 25, '2013-04-02', '2013-11-06', 28.7, 'Minimum reached and then starting to wet'),
(7, 65, '2012-06-16', '2013-11-06', 52.2, ''),
(8, 65, '2010-06-16', '2013-11-06', 33.2, ''),
(9, 60, '2013-01-01', '2014-12-31', 41.0, '');
SELECT td.testDataId, result.id, td.BeginDate, StartHumidity, result.humidity as currentHumidity, EndHumidity AS expectedHumidity
FROM #TestData AS td
OUTER APPLY dbo.calculateCurrentHumidity(testDataId, BeginDate, EndDate, StartHumidity) AS result
--WHERE testDataId IN (5);
-- CLEAN UP
DROP TABLE #TestData;
DROP TABLE [dbo].[HumidityChangePeriods];
DROP TABLE [dbo].[numbers];
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply