Help needed filling in values based on previous row

Question

Post reply

Help needed filling in values based on previous row

Viewing 5 posts - 16 through 19 (of 19 total)

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

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 1

Marianne, I haven't got time to go over the execution plans at the moment, but you may shave a bit of time off by eliminating the update. Try nesting another isnull.

---------------------------------------------------------------------------

select --row_number() over (order by d.accountID,d.date) as rowID,

d.*, isnull(p.pointsEarnedTotal

,isnull((select top 1 pointsEarnedTotal

from tempdb.dbo.pointBalTotal

where accountID = d.accountID

and transactionDate < d.date

and pointsEarnedTotal is not null

order by transactionDate desc),0)) as pointsEarnedTotal

------------------------------------------------------------------------------

-- Instead of

------------------------------------------------------------------------------

,(select top 1 pointsEarnedTotal

from tempdb.dbo.pointBalTotal

where accountID = d.accountID

and transactionDate < d.date

and pointsEarnedTotal is not null

order by transactionDate desc)) as pointsEarnedTotal

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Garadin (10/15/2008)

While my subquery seems to fit all that criteria, I'm not sure that it's creating a triangular join in this case. The subquery method is blazingly fast, it was one of a dozen different things I tried when I was evaluating your running totals method last week, and it was the only one that actually came close to equaling it. It may be that the triangular join is created, but because all the fields are covered by the clustered index, it's quick enough that it's not a big factor.

My original tests on this in a comparison: http://qa.sqlservercentral.com/Forums/Topic582878-338-1.aspx#bm583453

Heh... dang it. I was hoping I deleted my post on the possibility of triangular joins before anyone saw it. While it's still a bunch of "tiny" triangular joins and could still probably be beaten by some straight through set based code, I agree... I've seen these smaller correlated subqueries run awfully fast. With that in mind, I decided I just wanted info from Marianne so I could setup some tests to see what is actually up with the correlated sub-queries.

Thanks for the feedback, Seth. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 3

Hey Jeff, are you going to post your analysis here? If not, please post back to this forum and let us know whether it's an article, another thread, or what.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

bhovious (10/16/2008)

Hey Jeff, are you going to post your analysis here?

Absolutely!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Garadin One Orange Chip Points: 29613 More actions · Answer 5

Jeff Moden (10/16/2008)

Garadin (10/15/2008)

While my subquery seems to fit all that criteria, I'm not sure that it's creating a triangular join in this case. The subquery method is blazingly fast, it was one of a dozen different things I tried when I was evaluating your running totals method last week, and it was the only one that actually came close to equaling it. It may be that the triangular join is created, but because all the fields are covered by the clustered index, it's quick enough that it's not a big factor.

My original tests on this in a comparison: http://qa.sqlservercentral.com/Forums/Topic582878-338-1.aspx#bm583453

Heh... dang it. I was hoping I deleted my post on the possibility of triangular joins before anyone saw it. While it's still a bunch of "tiny" triangular joins and could still probably be beaten by some straight through set based code, I agree... I've seen these smaller correlated subqueries run awfully fast. With that in mind, I decided I just wanted info from Marianne so I could setup some tests to see what is actually up with the correlated sub-queries.

Thanks for the feedback, Seth. 🙂

Hehe, yep. I happened to catch it right after you posted it. So I responded, and then I was thrown completely and almost deleted my post again, as I thought I was somehow on the wrong thread. Then I looked at the time of your post and realized you had pulled a little switcheroo =). Eager to see the outcome of your tests.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]