Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

The Set-Based Limit Expand / Collapse
Author
Message
Posted Saturday, December 20, 2008 12:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:39 PM
Points: 33,155, Visits: 15,291
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
Post #623503
Posted Saturday, December 20, 2008 7:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
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. :P

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #623529
Posted Sunday, December 21, 2008 11:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:39 PM
Points: 33,155, Visits: 15,291
Thanks and great comments.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #623631
Posted Sunday, December 21, 2008 1:20 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 2:45 AM
Points: 21,397, Visits: 9,610
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.
Post #623644
Posted Sunday, December 21, 2008 1:37 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #623646
Posted Sunday, December 21, 2008 2:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 36,944, Visits: 31,446
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #623650
Posted Sunday, December 21, 2008 2:05 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #623651
Posted Sunday, December 21, 2008 2:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 2:45 AM
Points: 21,397, Visits: 9,610
Ah!, good point Barry... so how about your mother!!!!

:D
Post #623658
Posted Sunday, December 21, 2008 4:53 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Ninja's_RGR'us (12/21/2008)
... so how about your mother!!!! :D

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."
Post #623665
Posted Sunday, December 21, 2008 7:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 7,135, Visits: 15,140
RBarryYoung (12/21/2008)
Ninja's_RGR'us (12/21/2008)
... so how about your mother!!!! :D

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


----------------------------------------------------------------------------------
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?
Post #623686
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse