为什么选择这么慢(SQLServer)

Question

我们有3个嵌套的选择，每个选择都创建临时表。外部两个走得很快。但是内部的(下面需要大约需要1/4秒(才能执行。它创建了一个具有7行的表，每行都有一个日期：

declare @StartDate datetime
declare @EndDate datetime
select @StartDate = cast(@Weeks_Loop_TheDate as date),  @EndDate = cast((@Weeks_Loop_TheDate + 6) as date)
declare @temp3  table
(
TheDate datetime
)
while (@StartDate<=@EndDate)
begin
insert into @temp3
values (@StartDate )
select @StartDate=DATEADD(dd,1,@StartDate)
end
select * from @temp3

使用DateTime变量设置了参数，因此铸件不应显着。填充应该是微不足道和快速的。所以有什么想法为什么很慢？

有更好的方法吗？我们需要取回此范围内7个日期的结果集。

谢谢-Dave

Answer 1

这不起作用吗？与设置操作相比，SQL Server的循环/光标速度很慢。

DECLARE @StartDate DATE = '2017-05-03';
SELECT DATEADD(DAY, RowNr, @StartDate)
FROM (SELECT ROW_NUMBER () OVER (ORDER BY object_id) - 1 AS RowNr FROM sys.objects) AS T
WHERE T.RowNr < 7;

子查询将生成一个从0到n的数字序列(您在数据库中拥有的对象数量，始终将大于7，如果不是，则可以在内部使用CROSS JOIN(。

然后只使用DATEADD添加这些生成的数字。最后限制您要在WHERE子句中添加的天数。

，如果您要经常使用此功能，则可以将其包装在内联表值函数中。

CREATE FUNCTION dbo.DateTable (
  @p1 DATE,
  @p2 INT)
RETURNS TABLE
AS RETURN
    SELECT DATEADD(DAY, RowNr, @p1) AS TheDate
    FROM (SELECT ROW_NUMBER () OVER (ORDER BY object_id) - 1 AS RowNr FROM sys.objects) AS T
    WHERE T.RowNr < @p2;
GO

然后像这样查询：

SELECT *
FROM dbo.DateTable ('2017-05-03', 7);

在两种情况下都会导致：

+------------+
|  TheDate   |
+------------+
| 2017-05-03 |
| 2017-05-04 |
| 2017-05-05 |
| 2017-05-06 |
| 2017-05-07 |
| 2017-05-08 |
| 2017-05-09 |
+------------+

另一个有用的工具是Numbers表。可以像这样创建它(来源：http：//dataeducation.com/you-require-a-numbers-table/(：

CREATE TABLE Numbers
(
    Number INT NOT NULL,
    CONSTRAINT PK_Numbers 
        PRIMARY KEY CLUSTERED (Number)
        WITH FILLFACTOR = 100
)
INSERT INTO Numbers
SELECT
    (a.Number * 256) + b.Number AS Number
FROM 
    (
        SELECT number
        FROM master..spt_values
        WHERE 
            type = 'P'
            AND number <= 255
    ) a (Number),
    (
        SELECT number
        FROM master..spt_values
        WHERE 
            type = 'P'
            AND number <= 255
    ) b (Number)
GO

那么您不必使用ROW_NUMBER()，您的功能可能如下：

ALTER FUNCTION dbo.DateTable (
  @p1 DATE,
  @p2 INT)
RETURNS TABLE
AS RETURN
    SELECT DATEADD(DAY, Number, @p1) AS TheDate
    FROM Numbers
    WHERE Number < @p2;
GO

这将像魅力一样工作，而Numbers表可以在许多其他情况下重复使用，其中您需要一系列数字来进行某种计算。

Answer 2

运行您的脚本不应花费一毫秒。必须有一个需要调查的服务器问题。

也就是说，此操作可以作为更有效的基于设置的操作而不是循环进行。下面的示例使用CTE生成数字序列。实用程序数表促进了类似的基于设定的处理，因此我建议您创建一个具有数字序列(以数字为主要键(的永久表，以进一步提高性能。

DECLARE @StartDate date = @Weeks_Loop_TheDate;
WITH numbers(n) AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) - 1 FROM (VALUES(0),(0),(0),(0),(0),(0),(0)) AS a(b)
    )
SELECT DATEADD(day, n, @StartDate)
FROM numbers
ORDER BY n;

Answer 3

而不是使用@Temp之类的变量，而是使用temp表(＃(。查询分析仪使用@Temp。