What is a "set-based loop?"

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

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • 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


    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)
    Intro to Tally Tables and Functions

  • 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

  • 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


    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)
    Intro to Tally Tables and Functions

  • 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

  • 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!

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • 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. 🙂

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • 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


    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)
    Intro to Tally Tables and Functions

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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!

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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


    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)
    Intro to Tally Tables and Functions

Viewing 12 posts - 1 through 11 (of 11 total)

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