Как предотвратить сканирование кластерного индекса?
Я работаю в базе данных SAP B1, поэтому изменение структуры базы данных не допускается.
У меня есть таблица с 4 столбцами.
Table name: HLD1
Column Name Type
1 HldCode nvarchar
2 StrDate datetime
3 EndDate datetime
4 Rmrks nvarchar
Некоторые данные выглядят так:
HldCode StrDate EndDate Rmrks
2016 Holidays 2016-09-05 00:00:00.000 2016-09-05 00:00:00.000 Labor Day
2016 Holidays 2016-11-24 00:00:00.000 2016-11-25 00:00:00.000 Thankgiving
2016 Holidays 2016-12-26 00:00:00.000 2016-12-26 00:00:00.000 Christmas
2017 Holidays 2017-01-02 00:00:00.000 2017-01-02 00:00:00.000 New Years Day
2017 Holidays 2017-05-29 00:00:00.000 2017-05-29 00:00:00.000 Memorial Day
2017 Holidays 2017-07-04 00:00:00.000 2017-07-04 00:00:00.000 Indepenance Day
Обратите внимание, что в этой таблице нет первичного ключа.
У меня есть функция, которую я создал, чтобы определить количество дней между двумя датами, исключая праздничные дни (как указано в таблице HLD1 выше) и выходные. Хотя функция работает, как и ожидалось, для каждой используемой строки также требуется ~ 0,75 секунды, и мы пытаемся вернуть 50000 строк для последующего суммирования в Crystal Reports.
Часть функции, которая ссылается на таблицу HLD1 (и вызывает кластеризованный индекс в плане выполнения), выглядит следующим образом:
CREATE FUNCTION [dbo].[dateDiffHolidays] (
declare @START DATE
declare @END DATE
)
RETURNS INT
AS
BEGIN
SELECT @AddDays =
(select sum(datediff(dd,strdate,enddate) + 1) from hld1
where strdate between @START and @END)
+
(SELECT
(DATEDIFF(wk, @Start, @End) * 2)
+(CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END)
+(CASE WHEN DATENAME(dw, @End) = 'Saturday' THEN 1 ELSE 0 END))
RETURN @AddDays
END
GO
Конкретно первая часть. @START а также @END параметры, переданные в функцию.
Когда я проверяю план выполнения функции, все выглядит быстро, кроме этой части. Это дает мне следующую информацию:
Все источники, которые я нашел в Интернете о том, как предотвратить или исправить такое замедление, предлагают добавить индексы, не ссылаясь на определенные столбцы и т. Д., Но, поскольку я не могу изменить базу данных, я не смог найти Любая методология о том, как помочь в моей ситуации.
Какие-либо предложения?
РЕДАКТИРОВАТЬ 1:
Добавлена информация о схеме таблиц из SQL Management
РЕДАКТИРОВАТЬ 2:
Добавлен полный текст функции, на всякий случай:
CREATE FUNCTION [dbo].[dateDiffHolidays] (
@startdaytime DATETIME,
@enddaytime DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @answer INT, @START Date, @END Date, @AddDays int
SET @answer = 0
-- Strip Times
SELECT @START = dateadd(dd,0, datediff(dd,0,@StartDayTime)), @END =
dateadd(dd,0, datediff(dd,0,@EndDayTime))
SELECT @AddDays = (select sum(datediff(dd,strdate,enddatE) + 1) from hld1
where strdate between @START and @END
order by HldCode, StrDate, EndDate) + (
SELECT
(DATEDIFF(wk, @Start, @End) * 2)
+(CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END)
+(CASE WHEN DATENAME(dw, @End) = 'Saturday' THEN 1 ELSE 0 END))
-- handle end conditions
DECLARE @firstWeekDayInRange datetime, @lastWeekDayInRange datetime;
SELECT @firstWeekDayInRange = @START, @lastWeekDayInRange = @END
WHILE @firstWeekDayInRange in (select cast( DATEADD(day, t.N - 1, StrDate)
as date) as ResultDate
from HLD1 s join cteTally t on t.N <= DATEDIFF(day, StrDate, EndDate) + 1)
or datepart(dw,@firstWeekDayInRange) in (1,7)
BEGIN
SELECT @firstWeekDayInRange =
CASE
WHEN @firstWeekDayInRange in (select cast( DATEADD(day, t.N - 1, StrDate) as
date) from HLD1 s join cteTally t on t.N <= DATEDIFF(day, StrDate, EndDate)
+ 1)
or datepart(dw,@firstWeekDayInRange) in (1,7)
THEN dateadd(DAY,1,@firstWeekDayInRange)
ELSE @firstWeekDayInRange
END
END
WHILE @lastWeekDayInRange in (select cast( DATEADD(day, t.N - 1, StrDate) as
date) as ResultDate
from HLD1 s join cteTally t on t.N <= DATEDIFF(day, StrDate, EndDate) + 1)
or datepart(dw,@lastWeekDayInRange) in (1,7)
BEGIN
SELECT @lastWeekDayInRange =
CASE
WHEN @lastWeekDayInRange in (select cast( DATEADD(day, t.N - 1, StrDate) as
date) from HLD1 s join cteTally t on t.N <= DATEDIFF(day, StrDate, EndDate)
+ 1)
or datepart(dw,@lastWeekDayInRange) in (1,7)
THEN dateadd(DAY,-1,@lastWeekDayInRange)
ELSE @lastWeekDayInRange
END
END
-- add one day to answer (to count Friday) if enddate was on a weekend
SELECT @answer = @answer +
CASE
-- triggered if start and end date are on same weekend
WHEN dateDiff(DAY,@firstWeekDayInRange,@lastWeekDayInRange) < 0 THEN
(@answer * -1)
-- otherwise count the days and substract 2 days per weekend in between dates
ELSE (DateDiff(DAY, @firstWeekDayInRange, @lastWeekDayInRange) - @AddDays)
END
RETURN @answer
END
GO
3 ответа
Решение
Вы можете попробовать добавить ORDER BY пункт.
CREATE TABLE HLD1
(
HldCode nvarchar(20),
StrDate datetime,
EndDate datetime,
Rmrks nvarchar(50)
)
create unique index id_hld1 on HLD1 (HldCode, StrDate, EndDate);
GO
INSERT INTO HLD1
VALUES ('2016 Holidays', '2016-09-05 00:00:00.000', '2016-09-05 00:00:00.000', 'Labor Day'),
('2016 Holidays', '2016-11-24 00:00:00.000', '2016-11-25 00:00:00.000', 'Thanksgiving'),
('2016 Holidays', '2016-12-26 00:00:00.000', '2016-12-26 00:00:00.000', 'Christmas'),
('2017 Holidays', '2017-01-02 00:00:00.000', '2017-01-02 00:00:00.000', 'New Years Day'),
('2017 Holidays', '2017-05-29 00:00:00.000', '2017-05-29 00:00:00.000', 'Memorial Day'),
('2017 Holidays', '2017-07-04 00:00:00.000', '2017-07-04 00:00:00.000', 'Independence Day');
GO
6 rows affected
DECLARE @StrDate datetime = '2017-01-01';
DECLARE @EndDate datetime = '2018-01-01'
set statistics profile on;
SELECT HldCode, StrDate, EndDate, Rmrks
FROM HLD1
WHERE StrDate >= @StrDate
AND EndDate < @EndDate;
set statistics profile off;
GO
Выход:
HldCode | StrDate | EndDate | Rmrks
:------------ | :------------------ | :------------------ | :--------------
2017 Holidays | 02/01/2017 00:00:00 | 02/01/2017 00:00:00 | New Years Day
2017 Holidays | 29/05/2017 00:00:00 | 29/05/2017 00:00:00 | Memorial Day
2017 Holidays | 04/07/2017 00:00:00 | 04/07/2017 00:00:00 | Indepenance Day
Rows | Executes | StmtText | StmtId | NodeId | Parent | PhysicalOp | LogicalOp | Argument | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList | Warnings | Type | Parallel | EstimateExecutions
> :--- | :------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -----: | -----: | -----: | :--------- | :--------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :----------- | :--------- | :---------- | ---------: | :--------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------- | :------- | :------- | :-----------------
> 3 | 1 | SELECT HldCode, StrDate, EndDate, Rmrks<br>from HLD1<br>WHERE StrDate >= @StrDate<br>AND EndDate < @EndDate | 1 | 1 | 0 | <em>null</em> | <em>null</em> | <em>null</em> | <em>null</em> | 1 | <em>null</em> | <em>null</em> | <em>null</em> | 0.0032886 | <em>null</em> | <em>null</em> | SELECT | False | <em>null</em>
> 3 | 1 | |--Table Scan(OBJECT:([fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1]), WHERE:([fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[StrDate]>=[@StrDate] AND [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[EndDate]<[@EndDate])) | 1 | 2 | 1 | Table Scan | Table Scan | OBJECT:([fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1]), WHERE:([fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[StrDate]>=[@StrDate] AND [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[EndDate]<[@EndDate]) | [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[HldCode], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[StrDate], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[EndDate], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[Rmrks] | 1 | 0.003125 | 0.0001636 | 99 | 0.0032886 | [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[HldCode], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[StrDate], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[EndDate], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[Rmrks] | <em>null</em> | PLAN_ROW | False | 1
DECLARE @StrDate datetime = '2017-01-01';
DECLARE @EndDate datetime = '2018-01-01'
set statistics profile on;
SELECT HldCode, StrDate, EndDate, Rmrks
FROM HLD1
WHERE StrDate >= @StrDate
AND EndDate < @EndDate
ORDER BY HldCode, StrDate, EndDate;
set statistics profile off;
GO
Выход:
HldCode | StrDate | EndDate | Rmrks
:------------ | :------------------ | :------------------ | :--------------
2017 Holidays | 02/01/2017 00:00:00 | 02/01/2017 00:00:00 | New Years Day
2017 Holidays | 29/05/2017 00:00:00 | 29/05/2017 00:00:00 | Memorial Day
2017 Holidays | 04/07/2017 00:00:00 | 04/07/2017 00:00:00 | Indepenance Day
Rows | Executes | StmtText | StmtId | NodeId | Parent | PhysicalOp | LogicalOp | Argument | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList | Warnings | Type | Parallel | EstimateExecutions
:--- | :------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -----: | -----: | -----: | :----------- | :--------- | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :----------- | :--------- | :---------- | ---------: | :--------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------- | :------- | :------- | :-----------------
3 | 1 | SELECT HldCode, StrDate, EndDate, Rmrks<br>from HLD1<br>WHERE StrDate >= @StrDate<br>AND EndDate < @EndDate<br>ORDER BY HldCode, StrDate, EndDate | 1 | 1 | 0 | <em>null</em> | <em>null</em> | <em>null</em> | <em>null</em> | 1 | <em>null</em> | <em>null</em> | <em>null</em> | 0.00658116 | <em>null</em> | <em>null</em> | SELECT | False | <em>null</em>
3 | 1 | |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000])) | 1 | 2 | 1 | Nested Loops | Inner Join | OUTER REFERENCES:([Bmk1000]) | <em>null</em> | 1 | 0 | 4.18E-06 | 99 | 0.00658116 | [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[HldCode], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[StrDate], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[EndDate], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[Rmrks] | <em>null</em> | PLAN_ROW | False | 1
3 | 1 | |--Index Scan(OBJECT:([fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[id_hld1]), WHERE:([fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[StrDate]>=[@StrDate] AND [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[EndDate]<[@EndDate]) ORDERED FORWARD) | 1 | 3 | 2 | Index Scan | Index Scan | OBJECT:([fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[id_hld1]), WHERE:([fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[StrDate]>=[@StrDate] AND [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[EndDate]<[@EndDate]) ORDERED FORWARD | [Bmk1000], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[HldCode], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[StrDate], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[EndDate] | 1 | 0.003125 | 0.0001636 | 55 | 0.0032886 | [Bmk1000], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[HldCode], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[StrDate], [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[EndDate] | <em>null</em> | PLAN_ROW | False | 1
3 | 3 | |--RID Lookup(OBJECT:([fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD) | 1 | 5 | 2 | RID Lookup | RID Lookup | OBJECT:([fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD | [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[Rmrks] | 1 | 0.003125 | 0.0001581 | 61 | 0.0032831 | [fiddle_9f66021924d842d39e112d909afc0794].[dbo].[HLD1].[Rmrks] | <em>null</em> | PLAN_ROW | False | 1
dbfiddle здесь
ОБНОВИТЬ
Насколько вам нужна хранимая процедура, вы можете попробовать;
WITH (INDEX(IndexName))
CREATE FUNCTION [dbo].[dateDiffHolidays] (@START DATE, @END DATE) RETURNS INT AS BEGIN DECLARE @AddDays int; SELECT @AddDays = (SELECT sum(datediff(dd, StrDate, EndDate) + 1) FROM hld1 WITH (INDEX(HLD1_PRIMARY)) WHERE StrDate BETWEEN @START AND @END) + (SELECT (DATEDIFF(wk, @Start, @End) * 2) + (CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END) + (CASE WHEN DATENAME(dw, @End) = 'Saturday' THEN 1 ELSE 0 END)) RETURN @AddDays END
DECLARE @StrDate datetime = '2017-01-01'; DECLARE @EndDate datetime = '2018-01-01'; DECLARE @NumDays int = 0; set statistics profile on; EXEC @NumDays = [dbo].[dateDiffHolidays] @StrDate, @EndDate; set statistics profile off; SELECT @NumDays;Ряды | Выполняет | StmtText | StmtId | NodeId | Родитель | PhysicalOp | LogicalOp | Аргумент | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList | Предупреждения Тип | Параллельно | EstimateExecutions:--- |:------- |:---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -----: | -----: | -----: |:--------------- |:------------- |:---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |:------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |:----------- |:--------- |:---------- | ---------: |:--------------- |:--------------------------------------------------------------------------------------------------------------------------------- |:------- |:------- |:------- |:----------------- 1 | 1 | SELECT @AddDays = (SELECT sum(datediff(dd, StrDate, EndDate) + 1)
ОТ hld1 WITH (INDEX(HLD1_PRIMARY))
ГДЕ StrDate МЕЖДУ @START AND @END)
+
(ВЫБРАТЬ (DATEDIFF (wk, @Start, @End) * 2)
+ (СЛУЧАЙ, КОГДА ДАТА ИМЕНА (dw, @Start) = 'Воскресенье', ТОГДА 1, ЛИБО 0 КОНЕЦ)
+ (СЛУЧАЙ, КОГДА ДАТА ИМЕНА (dw, @End) = 'Суббота' ТОГДА 1 ИЛИ 0 КОНЕЦ)) | 1 | 1 | 0 | ноль | ноль | ноль | ноль | 1 | ноль | ноль | ноль | 0,00329658 | ноль | ноль | ВЫБРАТЬ | Ложь | ноль 0 | 0 | | --Расчитать скаляр (DEFINE:([Expr1006]=[Expr1003]+(датировка (неделя,CONVERT_IMPLICIT(datetimeoffset(7),[@START],0),CONVERT_IMPLICIT(datetimeoffset(7),[@END],0))*(2)+CASE WHEN, когда имя даты (день недели,[@START])= воскресенье ' THEN (1) ELSE (0) END+CASE, когда имя даты (день недели,[@END])=N'Saturday' THEN (1) ELSE (0) END))) | 1 | 2 | 1 | Вычислить скаляр | Вычислить скаляр | DEFINE:([Expr1006]=[Expr1003]+(DateDiff (неделя, CONVERT_IMPLICIT (DateTimeOffset (7), [@ START], 0), CONVERT_IMPLICIT (DateTimeOffset (7), [@ END], 0)) * (2) + СЛУЧАЙ, КОГДА ДАТА ИМЕНИ даты (день недели,[@START])= НЕС воскресенье, ТОГДА (1) ДАЛЕЕ (0) КОНЕЦ + СЛУЧАЙ, КОГДА ДАТА ДАТЫ (день недели, [@ ЭНД]) = НС субботы, ТОГДА (1) ДАЛЕЕ (0)) КОНЕЦ)) | [Expr1006]=[Expr1003]+(дата (неделя,CONVERT_IMPLICIT(datetimeoffset(7),[@START],0),CONVERT_IMPLICIT(datetimeoffset(7),[@END],0))*(2)+CASE WHEN datename(weekday,[@START])=N'Sunday' THEN (1) ELSE (0) END+CASE WHEN, когда datename(weekday,[@END])=N'Saturday' THEN (1) ELSE (0) END) | 1 | 0 | 1E-07 | 11 | 0,00329658 | [Expr1006] | ноль | PLAN_ROW | Ложь | 1 0 | 0 | | --Рассчитать скаляр (DEFINE:([Expr1003]= СЛУЧАЙ, КОГДА [Expr1012]=(0) ПОСЛЕ НУЛЬ ЕЩЕ [Expr1013] END)) | 1 | 3 | 2 | Вычислить скаляр | Вычислить скаляр | ОПРЕДЕЛИТЬ: ([Expr1003] = СЛУЧАЙ, КОГДА [Expr1012]=(0) ТОГДА НЕДОГОДНО [Expr1013] END) | [Expr1003]= СЛУЧАЙ, КОГДА [Expr1012]=(0) ТОГДА НЕДЕЙСТВИТЕЛЕНО [Expr1013] END | 1 | 0 | 0 | 11 | 0,00329648 | [Expr1003] | ноль | PLAN_ROW | Ложь | 1 1 | 1 | | - Агрегат потока (DEFINE:([Expr1012]=COUNT_BIG([Expr1007]), [Expr1013]=SUM([Expr1007])))) | 1 | 4 | 3 | Поток Агрегат | Агрегат | ноль | [Expr1012]=COUNT_BIG([Expr1007]), [Expr1013]=SUM([Expr1007]) | 1 | 0 | 2.3E-06 | 11 | 0,00329648 | [Expr1012], [Expr1013] | ноль | PLAN_ROW | Ложь | 1 0 | 0 | |--Compute Scalar(DEFINE:([Expr1007]=datediff(день,[fiddle_c7abc2eb9b3f49599be6803069c6aa56].[Dbo].[HLD1].[StrDate],[fiddle_c7abc2eb9b3b496] h6306) (1))) | 1 | 5 | 4 | Вычислить скаляр | Вычислить скаляр | DEFINE:([Expr1007]=datediff(день,[fiddle_c7abc2eb9b3f49599be6803069c6aa56].[Dbo].[HLD1].[StrDate],[fiddle_c7abc2eb9b3f49599be6803069c6a1] [1].] []. [Expr1007] = datediff (день,[fiddle_c7abc2eb9b3f49599be6803069c6aa56].[Dbo].[HLD1].[StrDate],[fiddle_c7abc2eb9b3f49599be6803069c6aa56].[D]] [HD]. [D]. 3 | 0 | 3E-07 | 11 | 0,00329418 | [Expr1007] | ноль | PLAN_ROW | Ложь | 1 3 | 1 | | - Индексное сканирование (OBJECT:([fiddle_c7abc2eb9b3f49599be6803069c6aa56].[Dbo].[HLD1].[HLD1_PRIMARY]), WHERE:([fiddle_c7abc2eb9b3f49599be6803069.DD.DB.DB) AND [fiddle_c7abc2eb9b3f49599be6803069c6aa56].[Dbo].[HLD1].[StrDate]<=[@END])) | 1 | 6 | 5 | Индекс сканирования | Индекс сканирования | OBJECT:([fiddle_c7abc2eb9b3f49599be6803069c6aa56].[Dbo]. [HLD1]. [HLD1_PRIMARY]), где: dbo].[HLD1].[StrDate]<=[@END]), FORCEDINDEX | [fiddle_c7abc2eb9b3f49599be6803069c6aa56].[dbo].[HLD1].[StrDate], [fiddle_c7abc2eb9b3f49599be6803069c6aa56].[dbo].[HLD1].[EndDate] | 3 | 0,003125 | 0,0001636 | 23 | 0,0032886 | [fiddle_c7abc2eb9b3f49599be6803069c6aa56].[dbo].[HLD1].[StrDate], [fiddle_c7abc2eb9b3f49599be6803069c6aa56].[dbo].[HLD1].[EndDate] | ноль | PLAN_ROW | Ложь | 1 | (Без названия столбца) | | ---------------: | | 108 |
dbfiddle здесь
Вы должны использовать функцию? как насчет создания календаря даты в виде таблицы?
CREATE TABLE #Datecalendar
(DateId DATE PRIMARY KEY
, IsWeekend BIT DEFAULT 0
, IsHoliday BIT DEFAULT 0
);
CREATE TABLE #HLD1
(HldCode nvarchar(20)
, StrDate datetime
, EndDate datetime
, Rmrks nvarchar(50)
);
INSERT INTO #HLD1
VALUES ('2016 Holidays', '2016-09-05 00:00:00.000', '2016-09-05 00:00:00.000', 'Labor Day'),
('2016 Holidays', '2016-11-24 00:00:00.000', '2016-11-25 00:00:00.000', 'Thanksgiving'),
('2016 Holidays', '2016-12-26 00:00:00.000', '2016-12-26 00:00:00.000', 'Christmas'),
('2017 Holidays', '2017-01-02 00:00:00.000', '2017-01-02 00:00:00.000', 'New Years Day'),
('2017 Holidays', '2017-05-29 00:00:00.000', '2017-05-29 00:00:00.000', 'Memorial Day'),
('2017 Holidays', '2017-07-04 00:00:00.000', '2017-07-04 00:00:00.000', 'Independence Day');
WITH cte AS (SELECT CAST('2016-01-01' AS DATE) AS DateId
UNION ALL
SELECT DATEADD(dd, 1, DateId)
FROM cte
WHERE DATEADD(dd, 1, DateId) <= '2019-01-01'
)
INSERT INTO #Datecalendar
(DateId
, IsWeekend
, IsHoliday
)
SELECT DateId
,CASE WHEN DATEPART(WEEKDAY,DateId) =1 OR DATEPART(WEEKDAY,DateId) = 7 THEN 1 ELSE 0 END
,CASE WHEN h.HldCode IS NOT NULL THEN 1 ELSE 0 END
FROM cte cte
LEFT JOIN #HLD1 h
ON cte.DateId BETWEEN h.StrDate AND h.EndDate
OPTION (MAXRECURSION 0);
SELECT COUNT(*)
FROM #Datecalendar
WHERE DateId BETWEEN '2016-05-06' AND '2017-02-24'
AND IsWeekend = 0
AND IsHoliday = 0;
DROP TABLE #HLD1;
DROP TABLE #Datecalendar;
Так как HLD1_PRIMARY Индекс включает в себя следующие столбцы: HldCode, StrDate, EndDate попробуйте добавить условие, которое фильтрует по HldCode к вашему совокупному выбору.
Например, если вы хотите включить только строки с HldCode = '2016 Holidays' а также HldCode = '2017 Holidays':
CREATE FUNCTION [dbo].[dateDiffHolidays] (
declare @START DATE
declare @END DATE
)
RETURNS INT
AS
BEGIN
SELECT @AddDays = (
select
sum(datediff(dd,strdate,enddate) + 1)
from
hld1
where
HldCode in ('2016 Holidays', '2017 Holidays')
and strdate between @START and @END
)
+
(
SELECT
(DATEDIFF(wk, @Start, @End) * 2)
+(CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END)
+(CASE WHEN DATENAME(dw, @End) = 'Saturday' THEN 1 ELSE 0 END)
)
RETURN @AddDays
END
ОБНОВИТЬ
Вы не получите ничего лучше, чем поиск по индексу для этого конкретного запроса.
Затем, посмотрев полный код, я подозреваю, что проблема не в этом запросе, а в циклах while, используемых для вычисления @firstWeekDayInRange а также @lastWeekDayInRange:
WHILE @firstWeekDayInRange in (
select
cast(DATEADD(day, t.N - 1, StrDate) as date) as ResultDate
from
HLD1 s
join cteTally t
on t.N <= DATEDIFF(day, StrDate, EndDate) + 1
)
or datepart(dw, @firstWeekDayInRange) in (1,7)
BEGIN
SELECT @firstWeekDayInRange =
CASE
WHEN @firstWeekDayInRange in (
select
cast(DATEADD(day, t.N - 1, StrDate) as date)
from
HLD1 s
join cteTally t on
t.N <= DATEDIFF(day, StrDate, EndDate) + 1
)
or datepart(dw,@firstWeekDayInRange) in (1,7) THEN
dateadd(DAY,1,@firstWeekDayInRange)
ELSE
@firstWeekDayInRange
END
END
В лучшем случае эти запросы приводят к сканированию кластеризованного индекса, а в худшем - к сканированию таблицы в таблице HLD1 для каждой итерации цикла, и мы мало что знаем о cteTally, так что это может быть хуже.