Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor-Killing: Accessing Data in the Next Row


Cursor-Killing: Accessing Data in the Next Row

Author
Message
Ed Pollack
Ed Pollack
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 513
Comments posted to this topic are about the item Cursor-Killing: Accessing Data in the Next Row
Megan Brooks
Megan Brooks
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 146
Yikes, it would never even occur to me to use a cursor in that situation (but I know there are those that do). I have always used a self-join. Pre-2005, in situations where the previous-period key was not a simple arithmetic calculation I would do something like use a correlated subquery to find the most recent period before the current one.
Pedro Gomes-379488
Pedro Gomes-379488
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 58
It's good to know about LAG and LEAD, that I didn't know of, but I use the approach below when faced with that kind of problem and don't have performance issues:

SELECT
T2.InvID, T2.Date, T2.NewLocationID AS LocationID, COUNT(*) AS ItemIdent INTO #Temp
FROM
Table AS T1
INNER JOIN Table AS T2 ON T1.InvID = T2.InvID AND T2.Date >= T1.Date
GROUP BY T2.InvID, T2.Date, T2.NewLocationID

SELECT T1.LocationID, DATEDIFF(d, T1.Date, T2.Date) AS QtyDaysInLocation
FROM #Temp AS T1 LEFT JOIN #Temp AS T2 ON T1.InvID = T2.InvID AND T1.ItemIdent = T2.ItemIdent - 1
danielk1
danielk1
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 185
Do not normally direct others to sites from the one the forum is on, this is another good explanation and illustration of LEAD and LAG: http://blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9764 Visits: 18462
Pedro Gomes-379488 (5/27/2014)
It's good to know about LAG and LEAD, that I didn't know of, but I use the approach below when faced with that kind of problem and don't have performance issues:

SELECT
T2.InvID, T2.Date, T2.NewLocationID AS LocationID, COUNT(*) AS ItemIdent INTO #Temp
FROM
Table AS T1
INNER JOIN Table AS T2 ON T1.InvID = T2.InvID AND T2.Date >= T1.Date
GROUP BY T2.InvID, T2.Date, T2.NewLocationID

SELECT T1.LocationID, DATEDIFF(d, T1.Date, T2.Date) AS QtyDaysInLocation
FROM #Temp AS T1 LEFT JOIN #Temp AS T2 ON T1.InvID = T2.InvID AND T1.ItemIdent = T2.ItemIdent - 1

Be careful with this as it's considered hidden RBAR and can be even worse than a cursor. Read the following article on it. http://www.sqlservercentral.com/articles/T-SQL/61539/


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Ed Pollack
Ed Pollack
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 513
There are certainly many ways to accomplish this and end up with the same results---but performance is the key in deciding what to do. LEAD and LAG are very, very efficient---and their value increases if you need other window functions over the same window, as the window itself can be shared across each.

Definitely test each case to determine how many seeks/scans are being performed and how IO looks---especially if you are upgrading to SQL Server 2012 and have new tools at your disposal that can be applied to older code.

A correlated subquery or cursor/WHILE solution may be adequate for small data sets, but data can change over time, so caution should be exercised before making changes now that could end up being a hindrance in the future.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9764 Visits: 18462
I forgot to mention. Thank you for the article Ed. It's nice,I wish that I could use it more, but we haven't migrated to 2012 (or 2014). I have to stay with the ROW_NUMBER() version as it seems to be the best option after the LEAD and LAG option you presented.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Pedro Gomes-379488
Pedro Gomes-379488
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 58
Thanks for the advice. My query is a little more controlled than the cases exposed in the article.

I use an equal join and only >= inside the same ID, which reduces the product.

Besides, I rarely use subqueries in the SELECT clause, only the in the FROM clause. I know it doesn't make any difference but the former seems to be more keen to getting out of control than the latter. By using subqueries in the FROM clause you get more concious of the cardinalities involved in your joins.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9764 Visits: 18462
Don't be so sure about the control that you expect by the limit on the ID. The rows are still being generated and you can easily check it with the actual execution plan. With a few thousand rows, the actual number of rows generated can be huge.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
BarneyL
BarneyL
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 971
CELKO (5/27/2014)
I would use a different data model that has a correct temporal interval (start_date, end_date).


I agree completely Joe. However I find myself using this technique a lot in order to get someone else's data into that correct model.
You always have to start from where you are rather than where you'd like to be.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search