Poor performance for business day calculation from aspfaq sample

[pb648174]

Greeting, below is the complete SQL taken from aspfaq.com (retrieved
from this newsgroup I believe) The query takes about two minutes to
run. Does anybody have a better set based way (sub-second response) to
determine business days?

CREATE TABLE dbo.Calendar

(

dt SMALLDATETIME NOT NULL PRIMARY KEY
CLUSTERED, -- Date value

IsWeekday BIT,
-- Is this date a weekday (M -
F)

IsHoliday BIT,
-- Is this date a holiday

Y SMALLINT,
-- Year the date falls in

FY SMALLINT,
-- Fiscal Year (needed?)

Q TINYINT,
-- Quarter date falls in

M TINYINT,
-- Numeric month of date

D TINYINT,
-- Numeric day of date

DW TINYINT,
-- Numeric DayOfWeek
(Sunda=1,Monday =2)

MonthName VARCHAR(9),
-- String name of month

DayName VARCHAR(9),
-- String name of day

W TINYINT
-- Week number

)

GO

-- Start & End Dates

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = DATEADD(d, -1, '20000101')

SET @EndDate = DATEADD(d, -1, '20300101')

-- Total number of dates to generate

DECLARE @Days INT

SET @Days = DATEDIFF(DAY, @StartDate, @EndDate)

-- Create temporary Numbers table

CREATE TABLE #Numbers

(

Number INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED

)

-- Insert a number into our temp table for each date to be generated

WHILE COALESCE(SCOPE_ IDENTITY(), 0) <= @Days

BEGIN

INSERT #Numbers DEFAULT VALUES

END

-- Generate a date for each day in our timespan

INSERT Calendar(dt)

SELECT DATEADD(DAY, Number, @StartDate)

FROM #Numbers

WHERE Number <= @Days

ORDER BY Number

-- Remove the temporary Numbers table

DROP TABLE #Numbers

GO

-- Update other columns

UPDATE dbo.Calendar SET

IsWeekday = CASE WHEN DATEPART(DW, dt) IN (1, 7) THEN 0
ELSE 1 END,

IsHoliday = 0,

Y = YEAR(dt),

FY = YEAR(dt),

Q = CASE

WHEN MONTH(dt) <= 3 THEN 1

WHEN MONTH(dt) <= 6 THEN 2

WHEN MONTH(dt) <= 9 THEN 3

ELSE 4 END,

M = MONTH(dt),

D = DAY(dt),

DW = DATEPART(DW, dt),

MonthName = DATENAME(MONTH, dt),

DayName = DATENAME(DW, dt),

W = DATEPART(WK, dt)

-- Query in question (takes almost 2 minutes to execute and return a
value)

SELECT

C.dt

FROM

Calendar C

WHERE

C.IsWeekDay = 1

AND C.IsHoliday = 0

AND 9 = (SELECT COUNT(*) FROM Calendar C2 WHERE C2.dt >=
GETDATE() AND C2.dt <= C.dt AND C2.IsWeekDay = 1 AND C2.IsHoliday = 0 )

[GeoSynch]

Here's an easy one:

SELECT
CASE
WHEN ([Date] % 7) > 1 THEN 'Business day'
ELSE 'Weekend day'
END AS "IsBusiness Day"
FROM table

The symbol % here is the modulo operator.
The remainder of dividing Date by 7 returns
0 for Saturday, 1 for Sunday and up to 6 for Friday.
Remainders from 2 to 6 correspond to Monday to Friday.

GeoSynch
"pb648174" <go****@webpaul .net> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .

Greeting, below is the complete SQL taken from aspfaq.com (retrieved
from this newsgroup I believe) The query takes about two minutes to
run. Does anybody have a better set based way (sub-second response) to
determine business days?

CREATE TABLE dbo.Calendar

(

dt SMALLDATETIME NOT NULL PRIMARY KEY
CLUSTERED, -- Date value

IsWeekday BIT,
-- Is this date a weekday (M -
F)

IsHoliday BIT,
-- Is this date a holiday

Y SMALLINT,
-- Year the date falls in

FY SMALLINT,
-- Fiscal Year (needed?)

Q TINYINT,
-- Quarter date falls in

M TINYINT,
-- Numeric month of date

D TINYINT,
-- Numeric day of date

DW TINYINT,
-- Numeric DayOfWeek
(Sunda=1,Monday =2)

MonthName VARCHAR(9),
-- String name of month

DayName VARCHAR(9),
-- String name of day

W TINYINT
-- Week number

)

GO

-- Start & End Dates

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = DATEADD(d, -1, '20000101')

SET @EndDate = DATEADD(d, -1, '20300101')

-- Total number of dates to generate

DECLARE @Days INT

SET @Days = DATEDIFF(DAY, @StartDate, @EndDate)

-- Create temporary Numbers table

CREATE TABLE #Numbers

(

Number INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED

)

-- Insert a number into our temp table for each date to be generated

WHILE COALESCE(SCOPE_ IDENTITY(), 0) <= @Days

BEGIN

INSERT #Numbers DEFAULT VALUES

END

-- Generate a date for each day in our timespan

INSERT Calendar(dt)

SELECT DATEADD(DAY, Number, @StartDate)

FROM #Numbers

WHERE Number <= @Days

ORDER BY Number

-- Remove the temporary Numbers table

DROP TABLE #Numbers

GO

-- Update other columns

UPDATE dbo.Calendar SET

IsWeekday = CASE WHEN DATEPART(DW, dt) IN (1, 7) THEN 0
ELSE 1 END,

IsHoliday = 0,

Y = YEAR(dt),

FY = YEAR(dt),

Q = CASE

WHEN MONTH(dt) <= 3 THEN 1

WHEN MONTH(dt) <= 6 THEN 2

WHEN MONTH(dt) <= 9 THEN 3

ELSE 4 END,

M = MONTH(dt),

D = DAY(dt),

DW = DATEPART(DW, dt),

MonthName = DATENAME(MONTH, dt),

DayName = DATENAME(DW, dt),

W = DATEPART(WK, dt)

-- Query in question (takes almost 2 minutes to execute and return a
value)

SELECT

C.dt

FROM

Calendar C

WHERE

C.IsWeekDay = 1

AND C.IsHoliday = 0

AND 9 = (SELECT COUNT(*) FROM Calendar C2 WHERE C2.dt >=
GETDATE() AND C2.dt <= C.dt AND C2.IsWeekDay = 1 AND C2.IsHoliday = 0 )

[pb648174]

We already have the isWeekday column in the table - what we need to
know is, how do I add 9 business days to a particular date efficiently?

[Ross Presser]

On 12 Apr 2005 15:44:07 -0700, pb648174 wrote:

Greeting, below is the complete SQL taken from aspfaq.com (retrieved
from this newsgroup I believe) The query takes about two minutes to
run. Does anybody have a better set based way (sub-second response) to
determine business days? [snip] -- Query in question (takes almost 2 minutes to execute and return a
value)

SELECT
C.dt
FROM
Calendar C
WHERE
C.IsWeekDay = 1
AND C.IsHoliday = 0
AND 9 = (SELECT COUNT(*) FROM Calendar C2 WHERE C2.dt >=
GETDATE() AND C2.dt <= C.dt AND C2.IsWeekDay = 1 AND C2.IsHoliday = 0 )

It looks to me like you're looking for the day that is nine business days
from today. At least, once I let the query above run for the two minutes, I
got April 26th when I ran it today (April 13th).

If that's so, then you *know* ahead of time that that day will be greater
than today and less than 60 days from today, right? Unless there's some
weird span of sixty consecutive holidays, anyway. So you can add that fact
to the main where clause, and that will speed it up mightily:

SELECT
C.dt
FROM
Calendar C
WHERE
C.IsWeekDay = 1
AND C.IsHoliday = 0
AND C.dt BETWEEN GETDATE() AND DATEADD(d,60,GE TDATE())
AND 9 = (
SELECT COUNT(*) FROM Calendar C2
WHERE C2.dt >= GETDATE()
AND C2.dt <= C.dt
AND C2.IsWeekDay = 1
AND C2.IsHoliday = 0)

Another thing you can do is to add indexes to the IsWeekDay and IsHoliday
columns (which requires changing them from BIT to TINYINT, since BIT can't
be indexed). Or even add a covering index on (DT, IsWeekDay, IsHoliday).
But that won't be necessary if you bound the main SELECT as I did -- I got
subsecond performance for the query above.

[pb648174]

That does execute much faster, but 50 is an arbitray number, and since
I will need to use it to schedule events up to one year or even
multiple years in the future, that won't work. In particular, for a set
of scheduled tasks which have a startdate and a lagdays column, I need
to calculate the end date for those tasks based on the above calendar
with business days/holidays entered. How would I do that with the above
query without the "bounding" limitation, returned as a set and with
decent performance?

[David Portas]

Here are some ideas:

1. This is fast but unfortunately in SQL2000 it can't be parameterized
without dynamic SQL:

SELECT MAX(dt)
FROM
(SELECT TOP 9 dt
FROM Calendar
WHERE dt >= CURRENT_TIMESTA MP
AND isweekday = 1
AND isholiday = 0
ORDER BY dt) AS T

2. This one can be parameterized but I wouldn't generally recommend it
because it relies on undocumented behaviour:

DECLARE @days INTEGER, @dt DATETIME

SET @days = 9
SET ROWCOUNT @days

SELECT @dt = dt
FROM Calendar
WHERE dt >= CURRENT_TIMESTA MP
AND isweekday = 1
AND isholiday = 0
ORDER BY dt

SELECT @dt

3. Extending Ross's suggestion, it shouldn't be difficult to calculate
a sensible upper bound for the query, even based on larger date ranges:

SELECT C.dt
FROM Calendar C
WHERE C.isweekday = 1
AND C.isholiday = 0
AND C.dt BETWEEN GETDATE() AND DATEADD(d,@days *0.30+60.0,GETD ATE())
AND 9 = (
SELECT COUNT(*) FROM Calendar C2
WHERE C2.dt >= GETDATE()
AND C2.dt <= C.dt
AND C2.IsWeekDay = 1
AND C2.IsHoliday = 0)

I think this last method is the best option, together with Ross's
suggestions on index improvements.

--
David Portas
SQL Server MVP
--

[pb648174]

And given that I have a table Called ScheduleTask with columns
StartDate & Duration, how do I for a group of rows, calculate the item
with the largest end date if the duration is based on the above
business days?

Why did you use 30%? Shouldn't it be 70% since I assume you are taking
out what you are guessing will be the weekends and holidays and adding
in a buffer of 60 days?

[Ross Presser]

On 14 Apr 2005 07:09:32 -0700, pb648174 wrote:

That does execute much faster, but 50 is an arbitray number, and since
I will need to use it to schedule events up to one year or even
multiple years in the future, that won't work. In particular, for a set
of scheduled tasks which have a startdate and a lagdays column, I need
to calculate the end date for those tasks based on the above calendar
with business days/holidays entered. How would I do that with the above
query without the "bounding" limitation, returned as a set and with
decent performance?

If you need to get sets, I think the best idea is to number the
business-days in the calendar table.

ALTER dbo.Calendar
ADD BusinessDayNum INT NOT NULL DEFAULT (0)

UPDATE dbo.calendar
SET BusinessDayNum = (
SELECT COUNT(*) FROM calendar C2
WHERE c2.isWeekday=1 and c2.isHoliday=0
AND C2.dt <= Calendar.dt
)
WHERE isWeekday=1 AND isHoliday=0

CREATE INDEX idx_Cal_BDN ON dbo.Calendar (BusinessDayNum )

Now you can do this:

CREATE TABLE #myTable
( startDate DATETIME NOT NULL, DurationDays INT NOT NULL)

INSERT #myTable VALUES ('2003-05-14',10)
INSERT #myTable VALUES ('2003-05-15',12)
INSERT #myTable VALUES ('2003-05-16',14)
INSERT #myTable VALUES ('2004-05-14',10)
INSERT #myTable VALUES ('2004-05-15',12)
INSERT #myTable VALUES ('2004-05-16',14)

SELECT T.startDate, T.DurationDays, C2.dt "endDate"
FROM dbo.Calendar C2, dbo.Calendar C1, #myTable T
WHERE C1.DT = T.startDate
AND C2.BusinessDayN um = C1.BusinessDayN um + T.DurationDays

startDate DurationDays endDate
------------------------- ------------ --------------------
2003-05-14 00:00:00.000 10 2003-05-28 00:00:00
2003-05-15 00:00:00.000 12 2003-06-02 00:00:00
2003-05-16 00:00:00.000 14 2003-06-05 00:00:00
2004-05-14 00:00:00.000 10 2004-05-28 00:00:00
2004-05-15 00:00:00.000 12 2000-01-18 00:00:00
2004-05-16 00:00:00.000 14 2000-01-20 00:00:00

Unfortunately, you will need to have ALL of your holidays set before you do
this, and if your holidays change you should redo the UPDATE.

[pb648174]

That's why this won't work... We will actually be storing the holidays
per user in a separate table. We are basically trying to duplicate MS
Project functionality and are now thinking we should do it all in
application logic instead of SQL, i.e. port in all the task data, do
all the calculations and then do a couple hundred updates for all of
the task data. SQL gurus, please show me a way to not have to do that...

[James Goodwin]

"pb648174" <go****@webpaul .net> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

That does execute much faster, but 50 is an arbitray number, and since
I will need to use it to schedule events up to one year or even
multiple years in the future, that won't work. In particular, for a set
of scheduled tasks which have a startdate and a lagdays column, I need
to calculate the end date for those tasks based on the above calendar
with business days/holidays entered. How would I do that with the above
query without the "bounding" limitation, returned as a set and with
decent performance?

Maybe try something like:
AND C.dt BETWEEN startdate AND DATEADD(d,3*dur ation,startdate )

Good Luck,
Jim