sql server语言 - 常规sql查询性能

Question

我有一个大而细的表，它记录了花费在活动上的时间。

存在Activities和RecordedTime两个表。Recorded Time保存一个日期戳，表示时间的使用日期。

我需要得到一个活动列表，这些活动在一个日期范围内只记录了时间。

目前我有代码构建一个排除列表，并将这些活动存储到一个临时表中:

DECLARE @DontInclude TABLE (ActivityID INT)
INSERT INTO @DontInclude
 SELECT DISTINCT ActivityID
 FROM RecordedTime
 WHERE DateStamp < @StartDate
INSERT INTO @DontInclude
 SELECT DISTINCT ActivityID
 FROM RecordedTime
 WHERE DateStamp > @EndDate

这样做的问题是，很多数据都在小的日期范围之外，因此时间很长。

我不能使用BETWEEN，因为它不能返回ONLY在特定日期范围内记录时间的活动。

我已经审查了估计执行计划，并创建了SQL建议的任何索引。

我的SP的这一部分仍然是瓶颈。谁能建议我还能做些什么来提高性能?

Answer 1

您想要的查询听起来像这样:

select a.*
from activities a
where not exists (select 1
                  from RecordedTime rt
                  where rt.activityId = a.activityId and
                        dateStamp < @StartDate
                 ) and
      not exists (select 1
                  from RecordedTime rt
                  where rt.activityId = a.activityId and
                        dateStamp > @EndDate
                 ) and
      exists (select 1
              from RecordedTime rt
              where rt.activityId = a.activityId 
             );

为了提高性能，您需要在RecordedTime(activityId, datestamp)上建立索引。

注意，使用三个子查询是有意为之。每个子查询都应该最优地使用索引，因此查询应该相当快。

Answer 2

您可以将插入语句合并到一个查询中以使其更高效，如下所示:

DECLARE @DontInclude TABLE (ActivityID INT)
INSERT INTO @DontInclude
 SELECT DISTINCT ActivityID
 FROM RecordedTime
 WHERE DateStamp < @StartDate OR Datestamp > @EndDate

当然，就像@Gordon Linoff提到的，在recordedtime表上添加一个非聚类索引会让它更快!

Answer 3

首先收集范围内的列表，然后删除应该排除的列表:

SELECT DISTINCT tmpId = r.ActivityID
INTO #tmp
FROM RecordedTime r
WHERE r.DateStamp >= @StartDate and r.DateStamp < @EndDate
DELETE FROM #tmp
WHERE exists(select 1 from RecordedTime r 
             where r.ActivityID = tmpID
             and (r.DateStamp < @startDate or
                  r.DateStamp > @endDate))

这应该更快，因为您只检查可能包含的排除条件("not exists");