CODEWITHSUNDEEP
sql-server
Sello Ralethe
Sello Ralethe

Reputation: 3

How can I speed up a query that uses a cursor?

I'm trying to build a SQL query that does a DateDiff on two subsequent rows in a table of timestamps. The query shows how many milliseconds are in between the timestamps. My initial solution uses a cursor to get the rows. However, this is slow especially since I'm dealing with rows in excess of 170k. Here's the query:

declare cur cursor for
select timestamp from TimeStamps
declare @firststamp datetime2
declare @secondstamp datetime2
fetch next from cur into @firststamp
while (@@FETCH_STATUS = 0)
begin
    fetch next from cur into @secondstamp
    print(DateDiff(millisecond, @firststamp, @secondstamp))
    set @firststamp = @secondstamp
end
close cur
deallocate cur

Is there a way I can speed this up? Also, is there an alternative query I can write to get the desired results?

Upvotes: 0

Views: 109

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

In SQL Server 2012+, you can do this using lag():

select t.*,
       datediff(millisecond, lag(timestamp) over (order by timestamp),
                timestamp) as diff_ms
from t;

Based on the syntax of the query in the question, I am ignoring the database tag.

Upvotes: 3

Related Questions