Can I write this query without CURSORs?

Question

I am trying to find out the number of events that happened within a threshold time of timestamps found in another table for the same category. What is the fastest way to vary delta (in the case given below, delta is 5 minutes)? I just tested an approach using cursor (set a variable to 5 and then keep incrementing and executing the same query) but it is taking 10 seconds for each iteration. In my actual data, number of rows in #EVENTS is approximately equal to 100K and #CHANGES is about 500K.

My tables are as follows:

CREATE TABLE #EVENTS(Category varchar(20), Timestamp datetime)
GO

INSERT INTO #EVENTS VALUES('A', '2013-01-23 05:02:00.000')
INSERT INTO #EVENTS VALUES('A', '2013-01-23 05:04:00.000')
INSERT INTO #EVENTS VALUES('B', '2013-01-23 05:03:00.000')
INSERT INTO #EVENTS VALUES('B', '2013-01-21 05:02:00.000')
GO

CREATE TABLE #CHANGES(Category varchar(10), Timestamp datetime)
GO

INSERT INTO #CHANGES VALUES('A', '2013-01-23 05:00:00.000')
INSERT INTO #CHANGES VALUES('B', '2013-01-21 05:05:00.000')

SELECT *
FROM
(
    SELECT X.Category, X.Timestamp, Y.Timestamp BeforeT, DATEADD(MINUTE, 5, Y.Timestamp) AfterT
    FROM #EVENTS X, #CHANGES Y
    WHERE X.Category = Y.Category
) X
WHERE X.Timestamp BETWEEN BeforeT AND AfterT

DROP TABLE #CHANGES
DROP TABLE #EVENTS
GO

You do understand that implicit joins are a very poor prgramming preactice and a SQL antipattern? They are espcially bad in SQL server as teh left join implicit syntax is not supported in current versions and was incorrect in oplder versions and mixing implicit and explicit joins will often lead to incorrect results. You can also get accidental cross joins. This syntax was replaced years ago, there is no excuse for using it in this century. — HLGEM, Jan 23 '13 at 19:52
@HLGEM: Thank you for correcting me. You are right about this being deprecated. I've changed my code and for my own future reference, a relevant thread is here: http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins — Legend, Jan 23 '13 at 22:38

score 6 · Accepted Answer · answered Jan 23 '13 at 19:14

6

Is this what you are looking for? It does a cross join to a CTE that defines the deltas:

with deltas as (
     select 5 as delta union all
     select 10 union all
     select 20
)
SELECT *
FROM (SELECT e.Category, e.Timestamp, c.Timestamp BeforeT,
             DATEADD(MINUTE, deltas.delta, c.Timestamp) AfterT,
             deltas.delta
     FROM #EVENTS e join
          #CHANGES c
          on e.Category = c.Category cross join
          deltas
    ) X
WHERE X.Timestamp BETWEEN BeforeT AND AfterT

I also fixed your aliases. Queries read much better when the aliases are related to the underlying table name.

answered Jan 23 '13 at 19:14

Gordon Linoff

1,242,037
58
646
786

+1 Yes! This is brilliant~ Do you have any suggestions on how I can automatically create a delta table for longer ranges this way? – Legend Jan 23 '13 at 19:23
1

Ask this as another question, people tend not to look at answered questions – Dale M Jan 23 '13 at 19:27
@Legend . . . The easiest way is to put the values in a column in Excel and to create a formula like `=" select "&A1&" as delta union all "` in another column, copy down the formula, and edit the first and last lines to be accurate syntax. – Gordon Linoff Jan 23 '13 at 19:31

Can I write this query without CURSORs?

1 Answers1