SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Set-Based Limit


The Set-Based Limit

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)

Group: Administrators
Points: 83649 Visits: 19223
Comments posted to this topic are about the item The Set-Based Limit

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117526 Visits: 41454
Well, well! Glad to see that the grand misunderstanding as to what is "Set Based" is and is not, prevails. Odd, too, that I've advocated the use of a particular hint for set based running totals, only to be scoffed at, and now some of those same folks are getting on the bandwagon. Tongue

So, here we go again... what is "Set Based" and what is not?

Contrary to popular belief, "Set Based" does NOT mean "all in one query" and not even "all in one view"... at least to me, it doesn't. To me, "Set Based" means "processing one or more sets of information until a final result set is achieved" and "within a single query, that usually means that each row should be "touched" only once as part of a set".

Hmmm... "processing one or more sets of information"... sounds procedureal... is it? Some folks would say so and that's ok with me because that's not RBAR.

Notice, too, that not everything that appears to be set based, actually is. Take this example...

USE NorthWind

SELECT x.OrderID,
x.Freight,
(SELECT SUM(y.Freight)
FROM dbo.Orders y
WHERE y.OrderID <= x.OrderID) AS RunningTotal,
(SELECT COUNT(y.Freight)
FROM dbo.Orders y
WHERE y.OrderID <= x.OrderID) AS RunningCount
FROM dbo.Orders X
ORDER BY x.OrderID



There's no WHILE loop and no Cursor... certainly that must be set based? If you think so, then take a look at the following article and see why it is not and how it can cripple a server with a relatively small number of rows...

Hidden RBAR: Triangular Joins

As you said, "The issue here isn't the set-based solution, it's a poorly built solution. It's a lack of knowledge and understanding of basic query structure and optimizer functionality,"... truer words could not have been stated.

Last but not least, I get a little ticked when people say that set based code is more difficult to read than "procedural" code (they really mean RBAR code). They're obviously talking about things like correlated subqueries and cross-apply's and the like, neither of which necessarily constitute set based code. The whole idea of set based code is to keep things simple and proper set based methods usually show that way in code, as well. Consider the simple act of counting from 1 to 10...

...using a loop...
--===== Create and preset a loop counter
DECLARE @Counter INT
SET @Counter = 1

--===== Count from 1 to 10
WHILE @Counter <= 10
BEGIN
SELECT @Counter
SET @Counter = @Counter + 1
END



...using set based...
--===== Count from 1 to 10
SELECT N
FROM dbo.Tally
WHERE N <= 10



Even one of the most difficult things in the world to do in SQL, do a running update where each row is based on calculations done including the previous row, can quickly be done in a set based manner using "pseudo cursors" and a "hint"... for more on those, see the following article...

Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5

Steve, you mentioned.... "In this post, Linchi mentions that sometimes simply following the advice of always building a set-based solution leads to code that might not perform as well as a procedural solution".

Heh... I'll admit that "simply following the advice" of others may sometimes lead you down the primrose path, but finding the CORRECT set based solution will likely not lead you to something that doesn't perform as well. There are very, very few exceptions. As you said, "I would agree with that, but I'd mention that the problem with most peoples' code is that they don't really understand what or why they write something a certain way." That's 100% spot-on, Steve...

So far as the "set based limit" goes... here's my quote for the day... "Computers (and T-SQL) are an imagination limited tool... If you have a limited imagination, you will limit the tool." For me, there is no such thing as a "set based limit".

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)

Group: Administrators
Points: 83649 Visits: 19223
Thanks and great comments.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37731 Visits: 9671
Tssss,tsss, you had to go ahead and wake up the tigger... and I was hoping for a quiet xmas!!!

I bet a small dollar that this will be the new longest thread on this site.
timothyawiseman
timothyawiseman
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 Visits: 920
I agree with Ninja, this will likely quickly become one of the longest threads on the site.

With that said, I personally have never seen a sql procedural solution which is better than a properly done and optimized set based solution. If anyone can provide an example, I would be very interested.

Two things of note though are that it is sometimes faster to code the procedural solution than it is to code the set based one, largely due to habit. It is sometimes the case that that savings in programmer time is far more valuable than the processing speed given up, especially if it is a script being written for one time use. The other is that I do not consider adding a query hint to be moving into procedural programming.

It may be true that using a query hint partially breaks a purely declarative model, but it is still dealing with set, still being written in an otherwise declarative fashion, and still dealing with the hardware at a relatively high level. Whether or not they are purely declarative, they can definitely provide tremendous performance benefits, and I particularly use with (nolock) and (noexpand) on a regular basis.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117526 Visits: 41454
timothyawiseman (12/21/2008)
Two things of note though are that it is sometimes faster to code the procedural solution than it is to code the set based one, largely due to habit. It is sometimes the case that that savings in programmer time is far more valuable than the processing speed given up, especially if it is a script being written for one time use.


I'm not sure why people think that way... if you practice piano, you don't practice hitting the wrong notes. Once someone knows it, writing set based code is typically faster than writting procedural code if for no other reason than it's usually shorter.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19426 Visits: 9518
Ninja's_RGR'us (12/21/2008)
Tssss,tsss, you had to go ahead and wake up the tigger... and I was hoping for a quiet xmas!!!

I bet a small dollar that this will be the new longest thread on this site.

Nah. People don't enjoy arguing about professional technical matters nearly as much as they enjoy complaining about other people.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37731 Visits: 9671
Ah!, good point Barry... so how about your mother!!!!

BigGrin
RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19426 Visits: 9518
Ninja's_RGR'us (12/21/2008)
... so how about your mother!!!! BigGrin

Uh, she died on a remote planet after being beaten and starved to death by Sandpeople, so out of anger I killed every man woman and child in their village. Is that a problem?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Matt Miller (4)
Matt Miller (4)
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16073 Visits: 18786
RBarryYoung (12/21/2008)
Ninja's_RGR'us (12/21/2008)
... so how about your mother!!!! BigGrin

Uh, she died on a remote planet after being beaten and starved to death by Sandpeople, so out of anger I killed every man woman and child in their village. Is that a problem?


I am betting she makes you take that helmet off at the dinner table...Smile

----------------------------------------------------------------------------------
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search