空白和孤岛:客户端解决方案与T查询
空白和孤岛:客户端解决方案与T查询
提问于 2013-04-04 18:05:52
用于间隙和孤岛的the解决方案能否比运行在客户端上的C#解决方案运行得更快？
具体来说，让我们提供一些测试数据：
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第一组测试数据正好有一个缺口：
SELECT StartedAt ,
FinishedAt
FROM dbo.Tasks
WHERE StartedAt BETWEEN DATEADD(MINUTE, 499999, '20100101')
AND DATEADD(MINUTE, 500006, '20100101')第二组测试数据有2M -1的间隙，每两个相邻的间隔之间都有一个缺口：
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目前我运行的是2008年R2，但2012年的解决方案非常受欢迎。我已经发布了我的C#解决方案作为一个答案。
回答 4
Database Administration用户
发布于 2013-04-04 18:11:42
以下C#代码解决了这个问题：
var connString =
"Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;Application Name=Benchmarks;";
var stopWatch = new Stopwatch();
stopWatch.Start();
using (var conn = new SqlConnection(connString))
{
conn.Open();
var command = conn.CreateCommand();
command.CommandText = "dbo.GetAllTaskEvents";
command.CommandType = CommandType.StoredProcedure;
var gaps = new List<string>();
using (var dr = command.ExecuteReader())
{
var currentEvents = 0;
var gapStart = new DateTime();
var gapStarted = false;
while (dr.Read())
{
var change = dr.GetInt32(1);
if (change == -1 && currentEvents == 1)
{
gapStart = dr.GetDateTime(0);
gapStarted = true;
}
else if (change == 1 && currentEvents == 0 && gapStarted)
{
gaps.Add(string.Format("({0},{1})", gapStart, dr.GetDateTime(0)));
gapStarted = false;
}
currentEvents += change;
}
}
File.WriteAllLines(@"C:\Temp\Gaps.txt", gaps);
}
stopWatch.Stop();
System.Console.WriteLine("Elapsed: " + stopWatch.Elapsed);此代码调用此存储过程：
CREATE PROCEDURE dbo.GetAllTaskEvents
AS
BEGIN ;
SELECT EventTime ,
Change
FROM ( SELECT StartedAt AS EventTime ,
1 AS Change
FROM dbo.Tasks
UNION ALL
SELECT FinishedAt AS EventTime ,
-1 AS Change
FROM dbo.Tasks
) AS TaskEvents
ORDER BY EventTime, Change DESC ;
END ;
GO在以下时间内，它在2米间隔内找到并打印一个空白，即温缓存：
1 gap: Elapsed: 00:00:01.4852029 00:00:01.4444307 00:00:01.4644152在以下时间内，它在2米间隔内找到并打印出2米-1间隙，温缓存：
2M-1 gaps Elapsed: 00:00:08.8576637 00:00:08.9123053 00:00:09.0372344 00:00:08.8545477这是一个非常简单的解决方案--我花了10分钟才开发出来。一位刚毕业的大学毕业生可以想出这个主意。在数据库方面，执行计划是一个简单的合并连接，只需很少的CPU和内存。
编辑:为了真实起见，我在不同的盒子上运行客户端和服务器。
Database Administration用户
发布于 2013-04-04 19:42:04
还有1秒的解决方案..。
;WITH cteSource(StartedAt, FinishedAt)
AS (
SELECT s.StartedAt,
e.FinishedAt
FROM (
SELECT StartedAt,
ROW_NUMBER() OVER (ORDER BY StartedAt) AS rn
FROM dbo.Tasks
) AS s
INNER JOIN (
SELECT FinishedAt,
ROW_NUMBER() OVER (ORDER BY FinishedAt) + 1 AS rn
FROM dbo.Tasks
) AS e ON e.rn = s.rn
WHERE s.StartedAt > e.FinishedAt
UNION ALL
SELECT MIN(StartedAt),
MAX(FinishedAt)
FROM dbo.Tasks
), cteGrouped(theTime, grp)
AS (
SELECT u.theTime,
(ROW_NUMBER() OVER (ORDER BY u.theTime) - 1) / 2
FROM cteSource AS s
UNPIVOT (
theTime
FOR theColumn IN (s.StartedAt, s.FinishedAt)
) AS u
)
SELECT MIN(theTime),
MAX(theTime)
FROM cteGrouped
GROUP BY grp
ORDER BY grpDatabase Administration用户
发布于 2013-04-04 19:10:45
这是一个在4秒内运行的解决方案。
WITH cteRaw(ts, type, e, s)
AS (
SELECT StartedAt,
1 AS type,
NULL,
ROW_NUMBER() OVER (ORDER BY StartedAt)
FROM dbo.Tasks
UNION ALL
SELECT FinishedAt,
-1 AS type,
ROW_NUMBER() OVER (ORDER BY FinishedAt),
NULL
FROM dbo.Tasks
), cteCombined(ts, e, s, se)
AS (
SELECT ts,
e,
s,
ROW_NUMBER() OVER (ORDER BY ts, type DESC)
FROM cteRaw
), cteFiltered(ts, grpnum)
AS (
SELECT ts,
(ROW_NUMBER() OVER (ORDER BY ts) - 1) / 2 AS grpnum
FROM cteCombined
WHERE COALESCE(s + s - se - 1, se - e - e) = 0
)
SELECT MIN(ts) AS starttime,
MAX(ts) AS endtime
FROM cteFiltered
GROUP BY grpnum;页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/39272
复制相关文章
相似问题
腾讯云开发者
