April 26, 2015 at 4:04 pm
Over the past year I have heard some folks around here talk about a "set based loop". What's that all about? Can someone provide an example?
-- Itzik Ben-Gan 2001
April 26, 2015 at 7:14 pm
Alan.B (4/26/2015)
Over the past year I have heard some folks around here talk about a "set based loop". What's that all about? Can someone provide an example?
There are two types that I know of.
The first type might be like you would process an Adjacency List Hierarchy. You read the top level with one select. Then you start a loop based on a rowcount > 0. The loop reads an entire level at each iteration. To wit, the loop is reading sets of rows with one iteration per set.
The other type is probably not what most people would expect. R. Barry Young coined the phrase for these "set based loops" (which is exactly what I used to call them). The phrase is "Pseudo-Cursor" and every INSERT, SELECT, UPDATE, and DELETE uses them. They are the machine language level loops behind the scenes that cause (for example) a SELECT to return a result set.
You're seriously aware of one of the more famous Pseudo-Cursors there is... a SELECT that uses a Tally Table.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2015 at 5:33 am
Jeff Moden (4/26/2015)
You're seriously aware of one of the more famous Pseudo-Cursors there is... a SELECT that uses a Tally Table.
When I first saw this type of usage I thought it was black magic...
DECLARE @Nums varchar(8000) = ''
;WITH T(n) AS (SELECT TOP(100) ROW_NUMBER() OVER (ORDER BY @@SPID) FROM sys.all_columns)
SELECT @Nums += CAST(n AS varchar) + ','
FROM T
PRINT @Nums
April 27, 2015 at 5:37 am
Gary Harding (4/27/2015)
Jeff Moden (4/26/2015)
You're seriously aware of one of the more famous Pseudo-Cursors there is... a SELECT that uses a Tally Table.When I first saw this type of usage I thought it was black magic...
DECLARE @Nums varchar(8000) = ''
;WITH T(n) AS (SELECT TOP(100) ROW_NUMBER() OVER (ORDER BY @@SPID) FROM sys.all_columns)
SELECT @Nums += CAST(n AS varchar) + ','
FROM T
PRINT @Nums
'Zactly. "Set Based Loop/Pseudo Cursor" tried and true.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2015 at 6:40 am
Jeff Moden (4/26/2015)
...The other type is probably not what most people would expect. R. Barry Young coined the phrase for these "set based loops" (which is exactly what I used to call them). The phrase is "Pseudo-Cursor" and every INSERT, SELECT, UPDATE, and DELETE uses them. They are the machine language level loops behind the scenes that cause (for example) a SELECT to return a result set.
You're seriously aware of one of the more famous Pseudo-Cursors there is... a SELECT that uses a Tally Table.
Are you talking about "nested loop" join operations?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 27, 2015 at 10:44 am
Jeff Moden (4/26/2015)
Alan.B (4/26/2015)
Over the past year I have heard some folks around here talk about a "set based loop". What's that all about? Can someone provide an example?There are two types that I know of.
The first type might be like you would process an Adjacency List Hierarchy. You read the top level with one select. Then you start a loop based on a rowcount > 0. The loop reads an entire level at each iteration. To wit, the loop is reading sets of rows with one iteration per set.
The other type is probably not what most people would expect. R. Barry Young coined the phrase for these "set based loops" (which is exactly what I used to call them). The phrase is "Pseudo-Cursor" and every INSERT, SELECT, UPDATE, and DELETE uses them. They are the machine language level loops behind the scenes that cause (for example) a SELECT to return a result set.
You're seriously aware of one of the more famous Pseudo-Cursors there is... a SELECT that uses a Tally Table.
Thanks Jeff. I has seen forum posts where you and Dwain have used that term and I was curious if there was some special kind of loop out there that I did not know about. Your answer makes perfect sense. Thanks!
-- Itzik Ben-Gan 2001
April 27, 2015 at 10:45 am
Gary Harding (4/27/2015)
Jeff Moden (4/26/2015)
You're seriously aware of one of the more famous Pseudo-Cursors there is... a SELECT that uses a Tally Table.When I first saw this type of usage I thought it was black magic...
DECLARE @Nums varchar(8000) = ''
;WITH T(n) AS (SELECT TOP(100) ROW_NUMBER() OVER (ORDER BY @@SPID) FROM sys.all_columns)
SELECT @Nums += CAST(n AS varchar) + ','
FROM T
PRINT @Nums
Ditto that. 🙂
-- Itzik Ben-Gan 2001
April 27, 2015 at 11:08 am
Eric M Russell (4/27/2015)
Jeff Moden (4/26/2015)
...The other type is probably not what most people would expect. R. Barry Young coined the phrase for these "set based loops" (which is exactly what I used to call them). The phrase is "Pseudo-Cursor" and every INSERT, SELECT, UPDATE, and DELETE uses them. They are the machine language level loops behind the scenes that cause (for example) a SELECT to return a result set.
You're seriously aware of one of the more famous Pseudo-Cursors there is... a SELECT that uses a Tally Table.
Are you talking about "nested loop" join operations?
That's an obvious example but not necessarily. The pseudo-cursors are also present for merge and hash joins as well as straight forward reads from a single table/index..
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2015 at 6:42 pm
Alan.B (4/27/2015)
Jeff Moden (4/26/2015)
Alan.B (4/26/2015)
Over the past year I have heard some folks around here talk about a "set based loop". What's that all about? Can someone provide an example?There are two types that I know of.
The first type might be like you would process an Adjacency List Hierarchy. You read the top level with one select. Then you start a loop based on a rowcount > 0. The loop reads an entire level at each iteration. To wit, the loop is reading sets of rows with one iteration per set.
The other type is probably not what most people would expect. R. Barry Young coined the phrase for these "set based loops" (which is exactly what I used to call them). The phrase is "Pseudo-Cursor" and every INSERT, SELECT, UPDATE, and DELETE uses them. They are the machine language level loops behind the scenes that cause (for example) a SELECT to return a result set.
You're seriously aware of one of the more famous Pseudo-Cursors there is... a SELECT that uses a Tally Table.
Thanks Jeff. I has seen forum posts where you and Dwain have used that term and I was curious if there was some special kind of loop out there that I did not know about. Your answer makes perfect sense. Thanks!
If you're going to start thinking about and using set-based loops, you better also learn about Halloween protection. In The Performance of Traversing a SQL Hierarchy [/url] look at the first paragraph of the section entitled "The WHILE Loop Revisited."
There are a bunch of links in there that are must reading.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 27, 2015 at 8:37 pm
dwain.c (4/27/2015)
Alan.B (4/27/2015)
Jeff Moden (4/26/2015)
Alan.B (4/26/2015)
Over the past year I have heard some folks around here talk about a "set based loop". What's that all about? Can someone provide an example?There are two types that I know of.
The first type might be like you would process an Adjacency List Hierarchy. You read the top level with one select. Then you start a loop based on a rowcount > 0. The loop reads an entire level at each iteration. To wit, the loop is reading sets of rows with one iteration per set.
The other type is probably not what most people would expect. R. Barry Young coined the phrase for these "set based loops" (which is exactly what I used to call them). The phrase is "Pseudo-Cursor" and every INSERT, SELECT, UPDATE, and DELETE uses them. They are the machine language level loops behind the scenes that cause (for example) a SELECT to return a result set.
You're seriously aware of one of the more famous Pseudo-Cursors there is... a SELECT that uses a Tally Table.
Thanks Jeff. I has seen forum posts where you and Dwain have used that term and I was curious if there was some special kind of loop out there that I did not know about. Your answer makes perfect sense. Thanks!
If you're going to start thinking about and using set-based loops, you better also learn about Halloween protection. In The Performance of Traversing a SQL Hierarchy [/url] look at the first paragraph of the section entitled "The WHILE Loop Revisited."
There are a bunch of links in there that are must reading.
I just finished reading this, what a very interesting and informative article Dwain!
-- Itzik Ben-Gan 2001
April 27, 2015 at 8:41 pm
Alan.B (4/27/2015)
dwain.c (4/27/2015)
Alan.B (4/27/2015)
Jeff Moden (4/26/2015)
Alan.B (4/26/2015)
Over the past year I have heard some folks around here talk about a "set based loop". What's that all about? Can someone provide an example?There are two types that I know of.
The first type might be like you would process an Adjacency List Hierarchy. You read the top level with one select. Then you start a loop based on a rowcount > 0. The loop reads an entire level at each iteration. To wit, the loop is reading sets of rows with one iteration per set.
The other type is probably not what most people would expect. R. Barry Young coined the phrase for these "set based loops" (which is exactly what I used to call them). The phrase is "Pseudo-Cursor" and every INSERT, SELECT, UPDATE, and DELETE uses them. They are the machine language level loops behind the scenes that cause (for example) a SELECT to return a result set.
You're seriously aware of one of the more famous Pseudo-Cursors there is... a SELECT that uses a Tally Table.
Thanks Jeff. I has seen forum posts where you and Dwain have used that term and I was curious if there was some special kind of loop out there that I did not know about. Your answer makes perfect sense. Thanks!
If you're going to start thinking about and using set-based loops, you better also learn about Halloween protection. In The Performance of Traversing a SQL Hierarchy [/url] look at the first paragraph of the section entitled "The WHILE Loop Revisited."
There are a bunch of links in there that are must reading.
I just finished reading this, what a very interesting and informative article Dwain!
Ha! One of my few that I'm afraid didn't reach a definitive conclusion, other than perhaps what Jeff (and many others) often say is that "in SQL it depends!"
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 28, 2015 at 7:40 am
You should see what happens when Halloween protection fails on an incorrectly written UPDATE when all of the conditions are just right. It's fairly unpredictable so nearly impossible to create with test tables but, when it happens in real life, some that should only take a second or two to UPDATE will suddenly slam all the CPUs it can get its hands on into the wall and takes hours to complete.
The incorrect form of UPDATE that I'm talking about is where the target table isn't included in the second FROM clause of the update when doing a joined update. Most people aren't even aware of the implicit first FROM clause of an UPDATE.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply