Hidden RBAR: Triangular Joins

Question

Post reply

Hidden RBAR: Triangular Joins

Viewing 15 posts - 91 through 105 (of 255 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Regarding moving average, here is an interesting discussion

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911

It involves a kind of triangular join with a finite number of records to join.

N 56°04'39.16"
E 12°55'05.25"

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

Good article Jeff Moden. 🙂

When I saw the topic I thought you must be the author (due to the word RBAR). I never see anyone using this term over any forum except you.

Are you the one who invented this word? 🙂

Also read this article regarding Running Total

http://sqljunkies.com/WebLog/amachanic/archive/2006/02/28/18286.aspx

Madhivanan

Failing to plan is Planning to fail

Jeff Moden SSC Guru Points: 1004175 More actions · Answer 3

Hi Madhivinan!

Long time no "see" and good to see you... and Thanks for the compliment.

Yeah... I'm responsible for the RBAR thing... publicly introduced it way back in the following...

http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

... but have been using the term since about 2003... easier to say than "ISAM" or "Procedureal Code". 😛

I've seen that article on various URL's and a couple of others on this thread have referenced those. I'm working on one of my own as a follow up to this one...

--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)

Paul DB SSC Eights! Points: 850 More actions · Answer 4

Jeff Moden (12/5/2007)
Heh... sorry about that, but at least I got your attention....:D
I should have mentioned that I'm working on an article (may end up being two) that covers a high speed method to do running totals and the like in SQL Server 2000...

So when do we get to read the set solution? The edge of my butt is getting sore sitting on the edge of my seat. 😉

Paul DB

Jeff Moden SSC Guru Points: 1004175 More actions · Answer 5

Paul DB (8/12/2008)
Jeff Moden (12/5/2007)
Heh... sorry about that, but at least I got your attention....:D
I should have mentioned that I'm working on an article (may end up being two) that covers a high speed method to do running totals and the like in SQL Server 2000...
So when do we get to read the set solution? The edge of my butt is getting sore sitting on the edge of my seat. 😉

Heh... it's been out for quite some time...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

--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)

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 6

Steve & company led you in with "T-SQL Crackerjack Jeff Moden..." on today's feature - does that mean there's a prize in every Jeff? :w00t:

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

toniupstny Hall of Fame Points: 3986 More actions · Answer 7

I am not sure if this was covered somewhere in the numerous prior pages of discussion as I had a hard time going through all of them (pardon me if it was). Please take a look at this query and help me with a couple of things:

1 - I understand that forcing with the query hint would also do so and having the clustered index certainly speeds the query. Would the "where orderid=orderid" clause in itself allow for correct results?

2 - Would this query have any problems if there were multiple threads active?

Thank you

Toni

use tempdb

drop table #tempt

go

use northwind

go

create TABLE #tempt (orderid int,

total_freight decimal(19,3), running_amt decimal(19,3), running_count int)

create clustered index IX_tempt on #tempt(orderid)

DECLARE @orderid char(9), @running_amt decimal(19,3), @running_count int

SELECT @orderid = ''

SET @running_amt = 0

SET @running_count = 0

insert #tempt

select orderid, sum(freight), running_amt=0, running_count = 0

from orders

group by orderid, freight

order by orderid

update #tempt

set @running_amt = running_amt= (@running_amt + total_freight), @running_count= running_count = @running_count + 1

from #tempt WITH( INDEX( IX_tempt ) )

where orderid = orderid

select orderid,

total_freight, running_amt, running_count

from #tempt

drop table #tempt

Charles Kincaid SSChampion Points: 13593 More actions · Answer 8

Re-read the original article. Jeff points out the problem with using the index and points out that you should use order by instead.

ATBCharles Kincaid

magarity kerns SSCrazy Points: 2763 More actions · Answer 9

Charles Kincaid (12/9/2007)
The following aside is off-topic, sort of:
I was once quoted the following paradox:
Given set A, which is a set of all sets that do not contain themselves as proper sub sets, is set A in set A?
The mathematician that gave me that was a former coworker. I think that he got his doctorate on that. He said that it was the only true paradox.

That's a modern mathematical version of the original self referential paradox:

http://en.wikipedia.org/wiki/Epimenides_paradox

magarity kerns SSCrazy Points: 2763 More actions · Answer 10

John Mitchell (12/10/2007)
Ramesh (12/9/2007)
In Mathematics, if one cannot prove a statement is true then it termed to be as false. In simple terms, if one says a=b and he cannot prove it then it assumed as a!=b.
Mmmm... I don't remember learning that in my Mathematics degree. What if you also can't prove that a!=b?

Then you get an Ultimate Cage Fight between a mathematician and a quantum physicist!

toniupstny Hall of Fame Points: 3986 More actions · Answer 11

(** adding: in response to Charles **)

Hmmm.... I (re)read the article Jeff pointed to as the follow-up

Heh... it's been out for quite some time...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--Jeff Moden

and frankly did not see the ORDER BY in the update statement (and still don't) that you said had to be there.

--===== Solve 2 types of Running Total and 2 types of Running Count problems

-- using a single update based on a Clustered Index at VERY high speeds.

UPDATE dbo.JBMTest

SET --===== Running Total

@PrevRunBal = RunBal = @PrevRunBal + Amount,

--===== Grouped Running Total (Reset when account changes)

@PrevGrpBal = GrpBal = CASE

WHEN AccountID = @PrevAcctID

THEN @PrevGrpBal + Amount

ELSE Amount -- Restarts total at "0 + current amount"

END,

--===== Running Count (Ordinal Rank)

@PrevRunCnt = RunCnt = @PrevRunCnt + 1,

--===== Grouped Running Total (Ordinal Rank, Reset when account changes)

@PrevGrpCnt = GrpCnt = CASE

WHEN AccountID = @PrevAcctID

THEN @PrevGrpCnt + 1

ELSE 1 -- Restarts count at "1"

END,

--===== "Anchor" and provides for "account change detection"

@PrevAcctID = AccountID

FROM dbo.JBMTest WITH (INDEX(IX_JBMTest_AccountID_Date),TABLOCKX)

So am I missing something?

Toni

Ed Thompson SSCommitted Points: 1840 More actions · Answer 12

Good article to grow by, and very much look forward to Part 2.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 13

magarity kerns (8/15/2008)That's a modern mathematical version of the original self referential paradox:

http://en.wikipedia.org/wiki/Epimenides_paradox%5B/quote%5D

The problem with "All Cretans are liars" is that we now know that liars tell truth in order to support lies. Politicians come to mind. 😀 Now a Cretan who says, "Cretans never relay truthful information" presents a more concrete logical problem.

What Dr. X (name withheld) was saying was that he found that this logical paradox exists in our current understanding of this section of mathematics. He could not find anything as solid in other sections. I think that this was foundational enough to get him his PHD.

ATBCharles Kincaid

magarity kerns SSCrazy Points: 2763 More actions · Answer 14

By the most amazing coincidence, we have just discovered a developer's query is running out of tempdb space because of the following as his join clause:

where

a.acct_nbr > b1.acct_nbr

and a.acct_nbr <= b2.acct_nbr

and a.acct_nbr = b.acct_nbr

I'm going to forward your article on triangle joins to him after we finishing beating him in the alley out back.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 15

toniupstny (8/15/2008)
(** adding: in response to Charles **)
Hmmm.... I (re)read the article Jeff pointed to as the follow-up
Heh... it's been out for quite some time...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--Jeff Moden
and frankly did not see the ORDER BY in the update statement (and still don't) that you said had to be there.
--===== Solve 2 types of Running Total and 2 types of Running Count problems
-- using a single update based on a Clustered Index at VERY high speeds.
UPDATE dbo.JBMTest
SET --===== Running Total
@PrevRunBal = RunBal = @PrevRunBal + Amount,
--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = GrpBal = CASE
WHEN AccountID = @PrevAcctID
THEN @PrevGrpBal + Amount
ELSE Amount -- Restarts total at "0 + current amount"
END,
--===== Running Count (Ordinal Rank)
@PrevRunCnt = RunCnt = @PrevRunCnt + 1,
--===== Grouped Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = GrpCnt = CASE
WHEN AccountID = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,
--===== "Anchor" and provides for "account change detection"
@PrevAcctID = AccountID
FROM dbo.JBMTest WITH (INDEX(IX_JBMTest_AccountID_Date),TABLOCKX)
So am I missing something?
Toni

Toni - the WTH ((INDEX(IX_JBMTest_AccountID_Date),TABLOCKX) "hint" is a way to force the update to use that index (i.e. process things in that order). It's about the only way to force an UPDATE to use an order, since trying to add an ORDER BY to the update statement should result in a SQL parsing error.

So - it's a back door way to "force" an order by.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?