aspfaq样本的营业日计算表现不佳 [英] Poor performance for business day calculation from aspfaq sample

问题描述

问候，下面是从aspfaq.com获取的完整SQL（我相信从这个新闻组中检索到了
）查询大约需要两分钟才能运行
。有没有人有更好的基于集合的方式（亚秒响应）来确定工作日？


创建表dbo.Calendar


（


dt SMALLDATETIME NOT NULL PRIMARY KEY

CLUSTERED， - 日期值


IsWeekday BIT，

- 这个日期是工作日（M -

F）


IsHoliday BIT，
- 这个日期是假日


Y SMALLINT，

- 日期年份


FY SMALLINT，

- 财政年度（需要？）


Q TINYINT，

- 季度日期落入


M TINYINT，

- 数字月份


D TINYINT，

- 数字日期


DW TINYINT，

- 数字DayOfWeek

（Sunda = 1，星期一= 2）


MonthName VARCHAR（9），

- 字符串月份名称


DayName VARCHAR（9），

- 字符串当天名称


W TINYINT

- 周数


）


GO


- 开始&结束日期


DECLARE @StartDate DATETIME


DECLARE @EndDate DATETIME


SET @StartDate = DATEADD（d，-1，'''20000101''）


SET @EndDate = DATEADD（d，-1，''20300101''）


- 生成日期总数


DECLARE @Days INT


SET @Days = DATEDIFF（DAY，@ StartDate ，@ EndDate）


- 创建临时Numbers表

CREATE TABLE #Numbers


（


编号INT IDENTITY（1,1）PRIMARY KEY CLUSTERED


）


- - 为我们生成的每个日期在我们的临时表中插入一个数字


当COALESCE（SCOPE_IDENTITY（），0）< = @Days


BEGIN


INSERT #Numbers默认值


结束


- 生成日期我们时代的每一天


INSERT日历（dt）


SELECT DATEADD（DAY，Number， @StartDate）


来自#Numbers


WHERE数字< = @Days


订购按号码


- 删除临时号码表


DROP TABLE #Numbers


GO


- 更新其他栏目


UPDATE dbo.Calendar SET


IsWeekday = CASE WHEN DATEPART（DW，dt）IN（1,7）然后0

ELSE 1结束，


IsHoliday = 0，


Y =年（dt），


FY =年（dt），


Q = CASE


当月（dt）< = 3那么1


当月（dt）< = 6那么2


当月（dt）< = 9那么3


ELSE 4结束，


M =月（dt），


D = DAY（dt），

DW = DATEPART（DW，dt），


MonthName = DATENAME（MONTH，dt），


DayName = DATENAME（DW，dt），


W = DATEPART（WK，dt） ）


- 有问题的查询（tak几乎2分钟执行并返回

值）


SELECT


C.dt


来自


日历C


WHERE


C .IsWeekDay = 1


AND C.IsHoliday = 0

AND 9 =（SELECT COUNT（*）FROM Calendar C2 WHERE C2.dt > =

GETDATE（）和C2.dt< = C.dt AND C2.IsWeekDay = 1 AND C2.IsHoliday = 0）

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 )

推荐答案

这里很简单：


SELECT

CASE

何时（[日期]％7）> 1那么''营业日''

ELSE''周末''

结束ASIsBusinessDay

FROM table


这里的符号％是模数运算符。

将Date除以7的余数返回

0表示星期六，1表示星期日和星期五最多6个。

2到6的剩余时间对应于星期一到星期五。


GeoSynch

" pb648174" <去**** @ webpaul.net>在消息中写道

news：11 ********************** @ l41g2000cwc.googlegr oups.com ...

Here''s an easy one:

SELECT
CASE
WHEN ([Date] % 7) > 1 THEN ''Business day''
ELSE ''Weekend day''
END AS "IsBusinessDay"
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.googlegr oups.com...

问候，下面是从aspfaq.com获取的完整SQL（我相信从这个新闻组中检索到的）查询大约需要两分钟才能运行。有没有人有更好的基于集合的方式（亚秒响应）来确定工作日？

创建表dbo.Calendar



dt SMALLDATETIME NOT NULL PRIMARY KEY
CLUSTERED， - 日期值

IsWeekday BIT，
- 这个日期是工作日（M -
F）

IsHoliday BIT，
- 这个日期是个假期吗

Y SMALLINT，
- 年份日期落在

FY SMALLINT，
- 财政年度（需要？）

Q TINYINT，
- 季度日期落在

M TINYINT，
- 数字月份日期

D TINYINT，
- 数字日期

DW TINYINT，
- Numeric DayOfWeek
（Sunda = 1，星期一= 2）

MonthName VARCHAR（9），
- 月份的字符串名称

DayName VARCHAR（9）， - String name of day

W TINYINT
- 周数



GO
- 开始&结束日期

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = DATEADD（d，-1，'''20000101'' ）

SET @EndDate = DATEADD（d，-1，''20300101''）

- 生成日期总数

DECLARE @Days INT

SET @Days = DATEDIFF（DAY，@ StartDate，@ EndDate）

- 创建临时Numbers表

创建表#Number



编号INT IDENTITY（1,1）主要集群



- 插入每个日期生成临时表的数字

COALESCE（SCOPE_IDENTITY（），0）< = @Days

BEGIN
INSERT #Numbers默认值

结束

- 在我们的时空中生成每一天的日期

INSERT日历（dt）
SELECT DATEADD（DAY，Number，@ StartDate）

FROM #Numbers

WHERE Number< = @Days

ORDER BY Number

- 删除临时Numbers表

DROP TABLE #Numbers

GO

- 更新其他栏目

更新dbo.Calendar SET

IsWeekday = DATEPART（DW，dt）IN时的情况（ 1,7）然后0
ELSE 1结束，

IsHoliday = 0，

Y =年（dt），

风云=年（dt），

Q =案例

当月（dt）< = 3那么1

每个月（dt）< ; = 6那么2

当月（dt）< = 9那么3

4结束，

M =月（dt） ，

D = DAY（dt），

DW = DATEPART（DW，dt），

MonthName = DATENAME（MONTH，dt），

DayName = DATENAME（DW，dt），

W = DA TEPART（WK，dt）


- 查询有问题（执行时间差不多2分钟并返回
值）

SELECT

C.dt

日历C



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）

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 )

我们已经有了表中的isWeekday列 - 我们需要知道的是，如何有效地将9个工作日添加到特定日期？

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?

4月12日2005 15:44:07 -0700，pb648174写道：

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

问候语，下面是从aspfaq.com获取的完整SQL（从我认为的新闻组中检索到的）查询运行大约需要两分钟。有没有人有更好的基于集合的方式（亚秒响应）来确定工作日？
[snip] - 查询有问题（执行时间差不多2分钟并返回
值）

SELECT
C.dt
FROM
日历C
WHERE
C.IsWeekDay = 1
AND C.IsHoliday = 0
AND 9 =（SELECT COUNT（*）FROM Calendar C2 WHERE C2.dt> ; =
GETDATE（）和C2.dt< = C.dt AND C2.IsWeekDay = 1 AND C2.IsHoliday = 0）

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 )

它看起来对我来说，就像你正在寻找9个工作日的那一天

从今天开始。至少，一旦我让上面的查询运行了两分钟，我今天（4月13日）运行它时，4月26日获得
。


如果就是这样，那么你*提前知道*那天会比今天更加美元b $ b比今天还要少60天，对吗？不管怎么说，除非有一些连续60个假期的奇怪的b / b
。所以你可以将这个事实

添加到main where子句中，这将大大加快它的速度：


SELECT

C .dt

FROM

日历C

WHERE

C.IsWeekDay = 1

AND C.IsHoliday = 0

和C.dt之间GETDATE（）和DATEADD（d，60，GETDATE（））

AND 9 =（

SELECT COUNT（*）FROM Calendar C2

WHERE C2.dt> = GETDATE（）

AND C2.dt< = C.dt

AND C2.IsWeekDay = 1

AND C2.IsHoliday = 0）


你可以做的另一件事是添加索引到IsWeekDay和IsHoliday

列（需要将它们从BIT更改为TINYINT，因为BIT不能将
编入索引）。或者甚至在（DT，IsWeekDay，IsHoliday）上添加一个覆盖索引。

但是如果你像我一样绑定主SELECT那就没有必要 - 我得到了
$ b上面查询的$ b亚秒级性能。

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,GETDATE())
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.

这篇关于aspfaq样本的营业日计算表现不佳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！