CODEWITHSUNDEEP
sqlsql-servertemp-tables
David Thielen
David Thielen

Reputation: 32884

Why is this select so slow (SqlServer)?

We have 3 nested selects that are each creating temporary table. The outer two go very fast. But the inner one (below takes about 1/4 second sometimes to execute. It's creating a table with 7 rows, each holding a date:

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

The params are set with a DateTime variable so the cast shouldn't be significant. And the populating should be trivial and fast. So any ideas why it's slow?

And is there a better way to do this? We need to get back a result set that is 7 dates in this range.

thanks - dave

Upvotes: 0

Views: 105

Answers (3)

Andre Gompelman
Andre Gompelman

Reputation: 44

Rather than using a variable such as @temp use a temp table (#) instead. The query analyser doesn't do optimizing well when using @temp.

Upvotes: 0

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

Wouldn't this work? Loops/cursors are slow in SQL Server compared to set operations.

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;

Subquery will generate a sequence of numbers from 0 to n (amount of objects you have in database, it's always going to be more than 7, and if not, you can just CROSS JOIN inside).

Then just use DATEADD to add these generated numbers. And finally limit amount of days you want to add in your WHERE clause.

And if you're going to use this quite often, you can wrap it up in a Inline Table-Valued Function.

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

And then query it like that:

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

Result in both cases:

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

Yet another useful tool is a Numbers table. It can be created just like that (source: 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

Then you would not have to use ROW_NUMBER() and your function could be as follows:

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

This is going to work like a charm and Numbers table could be reused in many other scenarios where you need a sequence of numbers to do some sort of calculations.

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46203

It shouldn't take more than a millisecond to run your script. There must be a server issue that requires investigation.

That said, this operation can be done as a more efficient set-based operation instead of looping. The example below uses a CTE to generate the number sequence. A utility numbers table facilitates set-based processing like this so I suggest you create a permanent table with a sequence of numbers (with number as the primary key) to improve performance further. 

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;

Upvotes: 1

Related Questions