Can a T-SQL solution for gaps and islands run faster than a C# solution running on the client?
To be specific, let us provide some test data:
CREATE TABLE dbo.Numbers
(
n INT NOT NULL
PRIMARY KEY
) ;
GO
INSERT INTO dbo.Numbers
( n )
VALUES ( 1 ) ;
GO
DECLARE @i INT ;
SET @i = 0 ;
WHILE @i < 21
BEGIN
INSERT INTO dbo.Numbers
( n
)
SELECT n + POWER(2, @i)
FROM dbo.Numbers ;
SET @i = @i + 1 ;
END ;
GO
CREATE TABLE dbo.Tasks
(
StartedAt SMALLDATETIME NOT NULL ,
FinishedAt SMALLDATETIME NOT NULL ,
CONSTRAINT PK_Tasks PRIMARY KEY ( StartedAt, FinishedAt ) ,
CONSTRAINT UNQ_Tasks UNIQUE ( FinishedAt, StartedAt )
) ;
GO
INSERT INTO dbo.Tasks
( StartedAt ,
FinishedAt
)
SELECT DATEADD(MINUTE, n, '20100101') AS StartedAt ,
DATEADD(MINUTE, n + 2, '20100101') AS FinishedAt
FROM dbo.Numbers
WHERE ( n < 500000
OR n > 500005
)
GO
This first set of test data has exactly one gap:
SELECT StartedAt ,
FinishedAt
FROM dbo.Tasks
WHERE StartedAt BETWEEN DATEADD(MINUTE, 499999, '20100101')
AND DATEADD(MINUTE, 500006, '20100101')
The second set of test data has 2M -1 gaps, a gap between each two adjacent intervals:
TRUNCATE TABLE dbo.Tasks;
GO
INSERT INTO dbo.Tasks
( StartedAt ,
FinishedAt
)
SELECT DATEADD(MINUTE, 3*n, '20100101') AS StartedAt ,
DATEADD(MINUTE, 3*n + 2, '20100101') AS FinishedAt
FROM dbo.Numbers
WHERE ( n < 500000
OR n > 500005
)
GO
Currently I am running 2008 R2, but 2012 solutions are very welcome.
I have posted my C# solution as an answer.
Best Answer
And a 1 second solution...