Cursor-Killing: Accessing Data in the Next Row

Question

Cursor-Killing: Accessing Data in the Next Row

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 1

Interesting that this comes up just days after I had to perform this very type of operation. I was determined to not use a loop especially given that I also have to call a function that determines date difference in seconds for a particular customer's business hours (TheValue) and just a straight calc for the difference in seconds for the two dates (TheValue2). Does anyone see an opportunity for improvement in the code below? I'm sure the sub-query isn't the most efficient way to do this. The update statement will typically operate on fewer than 20 rows at any given time, so scaling up to thousands or more isn't a concern.

update D set TheValue=dbo.UF_date_diff_Customer (@Host_prefix,D.Event_DTTM, (select min(t2.Event_DTTM) from @DateMath t2 where t2.RowNum>D.RowNum and Event_Type='Resume')),

TheValue2=datediff(ss,D.Event_DTTM, (select min(t2.Event_DTTM) from @DateMath t2 where t2.RowNum>D.RowNum and Event_Type='Resume'))

from @DateMath D where Event_Type='Suspend'

Cheers

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

Can't wait to move up to 2012 and take advantage of the LEAD and LAG.

Mike01 SSChampion Points: 11395 More actions · Answer 3

jfogel (12/18/2015)
Interesting that this comes up just days after I had to perform this very type of operation. I was determined to not use a loop especially given that I also have to call a function that determines date difference in seconds for a particular customer's business hours (TheValue) and just a straight calc for the difference in seconds for the two dates (TheValue2). Does anyone see an opportunity for improvement in the code below? I'm sure the sub-query isn't the most efficient way to do this. The update statement will typically operate on fewer than 20 rows at any given time, so scaling up to thousands or more isn't a concern.
update D set TheValue=dbo.UF_date_diff_Customer (@Host_prefix,D.Event_DTTM, (select min(t2.Event_DTTM) from @DateMath t2 where t2.RowNum>D.RowNum and Event_Type='Resume')),
TheValue2=datediff(ss,D.Event_DTTM, (select min(t2.Event_DTTM) from @DateMath t2 where t2.RowNum>D.RowNum and Event_Type='Resume'))
from @DateMath D where Event_Type='Suspend'

Need a little more info than what is here. What is the UF doing? What does the data in @DateMath look like? What is expected results? The 20 rows or fewer can be deceiving if there is an issue and suddenly it has to process 20,000;-)

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/