Hidden RBAR: Triangular Joins

Question

Hidden RBAR: Triangular Joins

Viewing 15 posts - 106 through 120 (of 255 total)

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

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

Jeff Moden (8/12/2008)
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/

Ed Thompson (8/15/2008)
Good article to grow by, and very much look forward to Part 2.

In an effort to prevent the edges of other people's butts from getting sore, can you (or the editor) please modify the Hidden RBAR article to refer/link to the Advanced Querying one you posted? 😀

Paul DB

kathy o Mr or Mrs. 500 Points: 588 More actions · Answer 2

I've never really gotten a handle on execution plans and was hoping to use the code in the article to understand them a bit more. However, the code in the last part of the article (about the execution plan) didn't run. Did I miss something?

Also does anyone know of a great resource for understanding execution plans?

Thanks!

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

toniupstny

Hall of Fame

Points: 3986

August 15, 2008 at 11:02 am

#858881

Matt, thank you very much for the explanation.

Toni

Steve Jones - SSC Editor SSC Guru Points: 734605 More actions · Answer 4

I'm not sure which article Jeff meant for the followup, perhaps this one:http://www.sqlservercentral.com/articles/Test+Data/61572/

You can click on the author's name and get a list of all his articles.

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

kathyoshea (8/15/2008)
I've never really gotten a handle on execution plans and was hoping to use the code in the article to understand them a bit more. However, the code in the last part of the article (about the execution plan) didn't run. Did I miss something?
Also does anyone know of a great resource for understanding execution plans?
Thanks!

Check out Grant Fritchey's book - http://www.red-gate.com/specials/Grant.htm

That should get you started. 😛

---------------------------------------------------------
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."

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 6

Charles Kincaid (8/15/2008)
Re-read the original article. Jeff points out the problem with using the index and points out that you should use order by instead.

Actually, in the Running Total article, I said that you have to use an Order By kinda tongue in cheek... if it's a clustered index and you use an order by, the sort won't even show up on the execution plan because the Order By is ignored. Forcing the clustered index with a hint is absolutely the fastest way to put things in the same order as the clustered index. But, don't try it with non-clustered index... you can end up with a Merry-go-round sort.

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

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 7

toniupstny (8/15/2008)
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

1. The "WHERE OrderID = OrderID" would do the sort just fine IF it forced the use of the clustered index. However, I'm not sure that's guaranteed to happen. Using an index hint on a clustered index or an Order By are the only two ways I know of to guarantee the sort order.

2. I don't think so... problems do occur if parallelism is spawned, though. And, I haven't tried it on a partitioned view, but rumor has it the method doesn't work on those. I'll have to do a test sometime when I get time.

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

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 8

jcrawf02 (8/15/2008)
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:

Heh... no... just surprizes... 😉

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

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 9

Ed Thompson (8/15/2008)
Good article to grow by, and very much look forward to Part 2.

Thanks Ed... the followup is at the following URL...

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)

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 10

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.

Heh... Charles is referring to the tongue in cheek comment I made in the article...

You absolutely must (not really, you'll see) use an ORDER BY

... and must have missed the statement in parentheses.

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

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 11

Joe Celko (8/15/2008)
Very nice explanation of the problem!
Right now, I'd do running totals in a report writer with SQL Server. But my current job uses DB2, so I have SUM(..) OVER (PARTITION BY.. ORDER BY.. RANGE..) available to me.
I am still learning tricks with the Window clause (that is the correct name for OVER() in Standard SQL). I want to play with things I can load into the aggregate function to get some fancy stats in pure SQL.

Thanks for the compliment, Joe.

Heh... yeah, I wish the SUM() OVER() "Window clause" worked correctly in SQL Server... wouldn't be any need for such trickery in T-SQL then.

Thanks for the feedback! 🙂

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

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 12

magarity kerns (8/15/2008)
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.

Heh... Don't beat him in the alley... take him out for a nice pork chop dinner. Of course, you're going to feed him the pork chops... with a slingshot! 😛

I don't know what the purpose of his query was... if it has anything to do with generating a running total, or maybe a rank in 2k, take a look at the follow up article...

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)

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 13

kathyoshea (8/15/2008)
I've never really gotten a handle on execution plans and was hoping to use the code in the article to understand them a bit more. However, the code in the last part of the article (about the execution plan) didn't run. Did I miss something?
Also does anyone know of a great resource for understanding execution plans?
Thanks!

Oh man... I can't believe that... this is the second time the article has been published and I never noticed that bad code before. It's a copy/paste problem on my part. I'll fix it shortly and maybe Steve can republish it.

The problem is in the data generation code... here's what both pieces of code were supposed to look like together...

--===== Create a 10,000 row test table...

SELECT TOP 10000

SomeValue = NEWID()

INTO dbo.TableC

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== List the items with a running count

SELECT RunCount = (SELECT COUNT(*)

FROM TableC cs

WHERE cs.SomeValue <= c.SomeValue),

c.SomeValue

FROM TableC c ORDER BY RunCount

So far as a good book on execution plans, see Grant Fritchey's book on the subject. See the following URL for more...

http://www.simple-talk.com/sql/performance/execution-plan-basics/

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

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 14

Steve Jones - Editor (8/15/2008)
I'm not sure which article Jeff meant for the followup, perhaps this one:http://www.sqlservercentral.com/articles/Test+Data/61572/
You can click on the author's name and get a list of all his articles.

Maybe, this one on running totals...

http://www.sqlservercentral.com/Forums/Topic430004-203-3.aspx?Update=1

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

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 15

Paul DB (8/15/2008)
In an effort to prevent the edges of other people's butts from getting sore, can you (or the editor) please modify the Hidden RBAR article to refer/link to the Advanced Querying one you posted? 😀

LOL... sure...

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