CODEWITHSUNDEEP
sqlsql-serverjoincursors
dbStudent
dbStudent

Reputation: 5

Cursors in SQL Server: High Performance Penalty

CallTable 

CallId |    Date | Time |   MemberID |  CallDuration
12     |    02.02.2015| 13:33:54|   3245|   234 |
13     |    02.02.2015| 13:37:24|   3245|   33  |

ActivityTable 

Date*********** Time*** MemberId    ***Activity
02.02.2015***   13:31:22*** 3245*** A
02.02.2015***   13:34:54*** 3245*** B

My boss wants to know what activity members (employees) were doing (or the the previous activity ) when they received a phone call. This data is stored in two tables given above.

I am using Cursor to get each row from CallTable and then another cursor within a loop to retrieve the last activity. There are about 1 million rows in the CallTable and it is taking very long to process this. There is no Primary-Foreign key relationship based on CallID in the CallTable.

Can anyone here please suggest how can I achieve this with JOIN and avoid the use of Cursors?

Thanks in advance

Upvotes: 0

Views: 110

Answers (2)

granadaCoder
granadaCoder

Reputation: 27904

Lag and Lead may be helpful to you

http://blog.sqlauthority.com/2013/09/22/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement/

Here is a Northwind mini example.

select * , DaysSincePreviousOrder = datediff(d , PreviousValue, OrderDate)  from (

  SELECT
LAG(p.OrderDate) OVER (ORDER BY p.CustomerID, p.OrderDate) PreviousValue,
p.CustomerID, p.OrderDate,
LEAD(p.OrderDate) OVER (ORDER BY p.CustomerID , p.OrderDate) NextValue
FROM dbo.Orders p where CustomerID = 'anatr' ) as derived1
where derived1.OrderDate = (select max(OrderDate) from dbo.Orders o where o.CustomerID = derived1.CustomerID)

GO

99.9% of things can be done without a cursor. I've written one cursor in 10 years...and haven't revisited it to see if I could refactor. I think it was a "hit several database" type of thing. But that's the miniscule exception to the rule...keep pursuing "set based" and non cursor solutions.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270723

In SQL Server, you can do this using a correlated subquery or by using APPLY. For instance:

select c.*, a.activity
from calltable c outer apply
     (select top 1 a.*
      from activitytable a
      where a.memberId = c.memberId and
            a.datetime <= c.datetime
      order by a.datetime asc
     ) a;

This assumes the date/time is in the same column, which would be the proper way to store this value. If they are in different columns, similar (but more complex) logic works.

For performance, you want an index on activitytable(memberid, datetime).

Upvotes: 3

Related Questions