Yes, I cut down the other table to -1 and 0. If there's anything else I could try, very happy to 🙂

I partitioned the rank function by a SeriesId as...

Well, in my application it seems about the same as the Outer Apply method. I only need the previous (not the next) day's value, for 5000 series of about 1000...

Hi Mohammad. I can run this on my data - right now it looks like it's picking out the current, previous and next 'date' or seq-nbr? What I'd need is...

I'd probably only consider a .NET approach if I could get savings of 30% or so somehow. My while loop only has a few iterations so I'm wondering about this...

Thanks - that's reassuring... so it's not worthwhile passing things out to some procedural language that's good at loops somehow?

Thanks - I just gave that a try to see if the query plan was any different but it seems identical to what I'm doing. Running a longer test now...

Just wanted to return to say I tried using Outer Apply as described and it was indeed faster for me too. Thanks all for your help.

That's very interesting David - thank you for investigating. Now to try and understand... 🙂

Thanks - I read Paul's articles (skimmed the trickier bits) and it seems that the optimiser recognises a nested loop when it sees it... but I don't see that it...

Thanks - Mr Charlie says Outer Apply might allow more efficient index use:

charles.gildawie (8/20/2010)

---

It's a good introduction for ROW_NUMBER but the query plan it generates is a little horrible. I...

OK, confession time. Reading Charles' Outer Apply suggestion I can't see why this isn't a RBAR solution. Doesn't the Outer Apply do exactly what a subquery of the type 'Top...

I got this CTE approach working for a related case - my first not-entirely-trivial query! Thanks David and all who have contributed via these comments. Next stage is to try...