CODEWITHSUNDEEP
sqlsql-serverperformancet-sql
user1244893
user1244893

Reputation: 105

General SQL Query Performance

I have a large but slim table that records time spent on activities.

Two tables exist Activities and RecordedTime. Recorded Time holds a date stamp signifying the day the time was spent.

I have a need to get a list of activities that only have time recorded against them in a date range.

Currently I have code which builds an exclusion list and stores those activities into a temporary table:

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

The trouble with this is that alot of data lies outside of small date ranges and therefore a long time.

I cant use BETWEEN as it doesn't bring back activities that have ONLY had time recorded within the specific date range.

I've reviewed the Estimate Execution Plan and created any indexes SQL suggested.

This portion of my SP is still the bottleneck. Can any suggest what other changes I can to improve performance?

Upvotes: 0

Views: 47

Answers (3)

johnjps111
johnjps111

Reputation: 1170

How about first gathering a list of ones in the range, then removing the ones that should be excluded:

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))

This should be quicker since you only check exclusion conditions ("not exists") on the ones that might be included; rather than running "not exists" on everything in the table.

Upvotes: 0

FutbolFan
FutbolFan

Reputation: 13723

You could combine the insert statement into one query really to make it more efficient like this:

DECLARE @DontInclude TABLE (ActivityID INT)

INSERT INTO @DontInclude
 SELECT DISTINCT ActivityID
 FROM RecordedTime
 WHERE DateStamp < @StartDate OR Datestamp > @EndDate

Ofcourse, like @Gordon Linoff mentions, adding a non-clustered index on your recordedtime table would make it quite faster!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The query that you want sounds like this:

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 
             );

For performance, you want an index on RecordedTime(activityId, datestamp).

Note that the use of three subqueries is quite intentional. Each subquery should make optimal use of the indexes, so the query should be fairly fast.

Upvotes: 2

Related Questions