EXPLAIN EXTENDED

CREATE SCHEMA [20090922_frame]
CREATE TABLE [20090922_frame].t_award (
        id INT NOT NULL PRIMARY KEY,
        userid INT NOT NULL,
        granted DATETIME NOT NULL,
        value VARCHAR(20) NOT NULL
)
GO
CREATE INDEX IX_award_userid_granted ON [20090922_frame].t_award (userid, granted)
BEGIN TRANSACTION
SELECT  RAND(0.20090922)
DECLARE @user INT
DECLARE @cnt INT
DECLARE @id INT
DECLARE @cdate DATETIME;
SET @user = 1
SET @id = 1
WHILE @user <= 10
BEGIN
        SET @cnt = 1
        SET @cdate = CONVERT(DATETIME, '20090922', 112)
        WHILE @cnt <= 10000
        BEGIN
                INSERT
                INTO    &#91;20090922_frame&#93;.t_award (id, userid, granted, value)
                VALUES  (@id, @user, @cdate, 'Award ' + CAST(@id AS VARCHAR))
                SET @cdate = DATEADD(second, RAND() * 450 + 50, @cdate)
                SET @cnt = @cnt + 1
                SET @id = @id + 1
        END
        SET @user = @user + 1
END
COMMIT
&#91;/sourcecode&#93;
</div>

This table contains <strong>100,000</strong> records for <strong>10</strong> users (<strong>10,000</strong> records per user).

The awards are given with random intervals from <strong>50</strong> to <strong>500</strong> seconds.

The query below does the following:

<ol>
	<li>Builds a 5-minute timeframe for each record</li>
	<li>Checks that there are at least <strong>5</strong> records in the timeframe and if positive returns all <code>id</code>'s from this timeframe.</li>
	<li>Groups distinct <code>id</code>'s and joins them back with the table</li>
</ol>


<a href="#" onclick="xcollapse('X9812');return false;"><strong>View the query, results, statistics and execution plan</strong></a>
<br />
<div id="X9812" style="display: none; ">

SELECT  am.*
FROM    (
        SELECT  DISTINCT ae.id
        FROM    [20090922_frame].t_award ab
        JOIN    [20090922_frame].t_award ae
        ON      ae.userid = ab.userid
                AND ae.granted BETWEEN ab.granted AND DATEADD(minute, 5, ab.granted)
        WHERE   (
                SELECT  COUNT(*)
                FROM    [20090922_frame].t_award ai
                WHERE   ai.userid = ab.userid
                        AND ai.granted BETWEEN ab.granted AND DATEADD(minute, 5, ab.granted)
                ) >= 5
        ) ad
JOIN    [20090922_frame].t_award am
ON      am.id = ad.id

Table 't_award'. Scan count 100009, logical reads 424139, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

SQL Server Execution Times:
   CPU time = 1124 ms,  elapsed time = 586 ms. 

  |--Parallelism(Gather Streams)
       |--Hash Match(Inner Join, HASH:([ae].[id])=([am].[id]))
            |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([ae].[id]))
            |    |--Hash Match(Left Semi Join, HASH:([ae].[userid])=([ab].[userid]), RESIDUAL:([test].[20090922_frame].[t_award].[userid] as [ae].[userid]=[test].[20090922_frame].[t_award].[userid] as [ab].[userid] AND [test].[20090922_frame].[t_award].[granted] as [ae].[granted]>=[test].[20090922_frame].[t_award].[granted] as [ab].[granted] AND [test].[20090922_frame].[t_award].[granted] as [ae].[granted]<=[Expr1010]))
            |         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([ae].[userid]))
            |         |    |--Index Scan(OBJECT:([test].[20090922_frame].[t_award].[IX_award_userid_granted] AS [ae]))
            |         |--Filter(WHERE:([Expr1006]>=(5)))
            |              |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([ab].[userid]))
            |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([ab].[userid], [ab].[granted], [Expr1014]) WITH UNORDERED PREFETCH)
            |                        |--Compute Scalar(DEFINE:([Expr1010]=dateadd(minute,(5),[test].[20090922_frame].[t_award].[granted] as [ab].[granted])))
            |                        |    |--Index Scan(OBJECT:([test].[20090922_frame].[t_award].[IX_award_userid_granted] AS [ab]))
            |                        |--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1013],0)))
            |                             |--Stream Aggregate(DEFINE:([Expr1013]=Count(*)))
            |                                  |--Index Seek(OBJECT:([test].[20090922_frame].[t_award].[IX_award_userid_granted] AS [ai]), SEEK:([ai].[userid]=[test].[20090922_frame].[t_award].[userid] as [ab].[userid] AND [ai].[granted] >= [test].[20090922_frame].[t_award].[granted] as [ab].[granted] AND [ai].[granted] <= dateadd(minute,(5),[test].[20090922_frame].[t_award].[granted] as [ab].[granted])) ORDERED FORWARD)
            |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([am].[id]))
                 |--Clustered Index Scan(OBJECT:([test].[20090922_frame].[t_award].[PK__t_award__3C94E422] AS [am]))

Table 'Worktable'. Scan count 4, logical reads 79, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 't_award'. Scan count 8, logical reads 2639, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

SQL Server Execution Times:
   CPU time = 702 ms,  elapsed time = 380 ms. 

  |--Parallelism(Gather Streams)
       |--Merge Join(Inner Join, MERGE:([test].[20090922_frame].[t_award].[id])=([am].[id]), RESIDUAL:([test].[20090922_frame].[t_award].[id] as [am].[id]=[test].[20090922_frame].[t_award].[id]))
            |--Sort(ORDER BY:([test].[20090922_frame].[t_award].[id] ASC))
            |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([test].[20090922_frame].[t_award].[id]))
            |         |--Hash Match(Right Semi Join, HASH:([test].[20090922_frame].[t_award].[userid], [Expr1020])=([test].[20090922_frame].[t_award].[userid], [Expr1011]), RESIDUAL:([test].[20090922_frame].[t_award].[userid]=[test].[20090922_frame].[t_award].[userid] AND [Expr1011]=[Expr1020]))
            |              |--Bitmap(HASH:([test].[20090922_frame].[t_award].[userid], [Expr1020]), DEFINE:([Bitmap1032]))
            |              |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([test].[20090922_frame].[t_award].[userid], [Expr1020]))
            |              |         |--Compute Scalar(DEFINE:([Expr1020]=[Expr1003]+CONVERT_IMPLICIT(bigint,[Recr1007],0)))
            |              |              |--Nested Loops(Inner Join)
            |              |                   |--Parallelism(Distribute Streams, RoundRobin Partitioning)
            |              |                   |    |--Index Spool(WITH STACK)
            |              |                   |         |--Concatenation
            |              |                   |              |--Compute Scalar(DEFINE:([Expr1022]=(0)))
            |              |                   |              |    |--Constant Scan(VALUES:(((0))))
            |              |                   |              |--Assert(WHERE:(CASE WHEN [Expr1024]>(100) THEN (0) ELSE NULL END))
            |              |                   |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1024], [Recr1005]))
            |              |                   |                        |--Compute Scalar(DEFINE:([Expr1024]=[Expr1023]+(1)))
            |              |                   |                        |    |--Table Spool(WITH STACK)
            |              |                   |                        |--Compute Scalar(DEFINE:([Expr1006]=[Recr1005]+(1)))
            |              |                   |                             |--Filter(WHERE:(STARTUP EXPR([Recr1005]<(4))))
            |              |                   |                                  |--Constant Scan
            |              |                   |--Table Spool
            |              |                        |--Merge Join(Right Semi Join, MANY-TO-MANY MERGE:([test].[20090922_frame].[t_award].[userid], [Expr1015])=([test].[20090922_frame].[t_award].[userid], [Expr1021]), RESIDUAL:([test].[20090922_frame].[t_award].[userid]=[test].[20090922_frame].[t_award].[userid] AND [Expr1015]=([Expr1003]+(4)) AND [test].[20090922_frame].[t_award].[granted]<=[Expr1019]))
            |              |                             |--Sequence Project(DEFINE:([Expr1015]=row_number))
            |              |                             |    |--Compute Scalar(DEFINE:([Expr1027]=(1)))
            |              |                             |         |--Segment
            |              |                             |              |--Index Scan(OBJECT:([test].[20090922_frame].[t_award].[IX_award_userid_granted]), ORDERED FORWARD)
            |              |                             |--Compute Scalar(DEFINE:([Expr1021]=[Expr1003]+(4)))
            |              |                                  |--Compute Scalar(DEFINE:([Expr1019]=dateadd(minute,(5),[test].[20090922_frame].[t_award].[granted])))
            |              |                                       |--Sequence Project(DEFINE:([Expr1003]=row_number))
            |              |                                            |--Compute Scalar(DEFINE:([Expr1029]=(1)))
            |              |                                                 |--Segment
            |              |                                                      |--Index Scan(OBJECT:([test].[20090922_frame].[t_award].[IX_award_userid_granted]), ORDERED FORWARD)
            |              |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([test].[20090922_frame].[t_award].[userid], [Expr1011]), WHERE:(PROBE([Bitmap1032])=TRUE))
            |                   |--Sequence Project(DEFINE:([Expr1011]=row_number))
            |                        |--Compute Scalar(DEFINE:([Expr1031]=(1)))
            |                             |--Segment
            |                                  |--Index Scan(OBJECT:([test].[20090922_frame].[t_award].[IX_award_userid_granted]), ORDERED FORWARD)
            |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([am].[id]), ORDER BY:([am].[id] ASC))
                 |--Clustered Index Scan(OBJECT:([test].[20090922_frame].[t_award].[PK__t_award__3C94E422] AS [am]), ORDERED FORWARD)