tsql with out using cursors

  • Damian-167372 (11/21/2014)


    ok this what i have come up.. so you can do it

    DECLARE @tbl_var TABLE( servername varchar (100 ), row_num INT IDENTITY ( 1 , 1 ) )

    DECLARE @rowcnt INT

    DECLARE @val_servername varchar( 100 )

    DECLARE @row_move INT

    INSERT INTO @tbl_var ( servername)

    SELECT server_name from Server_List

    SET @rowcnt = @@ROWCOUNT

    SET @row_move = 1

    WHILE @rowcnt >= @row_move

    BEGIN

    SELECT @val_servername = servername

    FROM @tbl_var

    WHERE row_num = @row_move

    PRINT @val_servername

    -- my query

    SET @row_move = @row_move + 1

    END

    Two issues here. This doesn't do the same thing as what you posted. The second...and bigger issue is that a while loop is going to be slower than a cursor. Changing a cursor into a while loop is an exercise in futility. You go from one looping structure to a slower looping structure. The approach you used here is even slower yet because you have to copy the data into a table variable first. You have effectively added more overhead to the process in the name of not using a cursor. I am guessing you heard that cursors are bad so you want to not use them?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (11/21/2014)


    Damian-167372 (11/21/2014)


    Two issues here. This doesn't do the same thing as what you posted. The second...and bigger issue is that a while loop is going to be slower than a cursor. Changing a cursor into a while loop is an exercise in futility. You go from one looping structure to a slower looping structure. The approach you used here is even slower yet because you have to copy the data into a table variable first. You have effectively added more overhead to the process in the name of not using a cursor. I am guessing you heard that cursors are bad so you want to not use them?

    yes your absolutely right I agree table variable is a overhead for this query,

    but for any other large queries I can use this method slightly change to a temp table instead of Table variable then i think its ok right?

  • Damian-167372 (11/21/2014)


    yes your absolutely right I agree table variable is a overhead for this query,

    but for any other large queries I can use this method slightly change to a temp table instead of Table variable then i think its ok right?

    No, it's not. You're still using a loop when you shouldn't. Loops/cursors have their places and you posted a good example of them. A while loop shouldn't replace a cursor that's already working fine.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (11/21/2014)


    Damian-167372 (11/21/2014)


    yes your absolutely right I agree table variable is a overhead for this query,

    but for any other large queries I can use this method slightly change to a temp table instead of Table variable then i think its ok right?

    No, it's not. You're still using a loop when you shouldn't. Loops/cursors have their places and you posted a good example of them. A while loop shouldn't replace a cursor that's already working fine.

    +1 Not unless you want to intentionally slow it down. You could add a couple pieces to slightly help but I doubt it would make much difference to be honest unless you have thousands of databases you are looking at.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Luis Cazares (11/21/2014)


    Damian-167372 (11/21/2014)


    yes your absolutely right I agree table variable is a overhead for this query,

    but for any other large queries I can use this method slightly change to a temp table instead of Table variable then i think its ok right?

    No, it's not. You're still using a loop when you shouldn't. Loops/cursors have their places and you posted a good example of them. A while loop shouldn't replace a cursor that's already working fine.

    I guess you didn't read my post i said "for any other large queries" that means for a cursor that loads lots of data, then this approch should be ok?

  • Damian-167372 (11/21/2014)


    Luis Cazares (11/21/2014)


    Damian-167372 (11/21/2014)


    yes your absolutely right I agree table variable is a overhead for this query,

    but for any other large queries I can use this method slightly change to a temp table instead of Table variable then i think its ok right?

    No, it's not. You're still using a loop when you shouldn't. Loops/cursors have their places and you posted a good example of them. A while loop shouldn't replace a cursor that's already working fine.

    I guess you didn't read my post i said "for any other large queries" that means for a cursor that loads lots of data, then this approch should be ok?

    Yes, I read it. Let me explain this again. If it's a great load for a cursor, then it would be a great load for a loop. You need to change the logic, stop thinking on what to do with each row and start thinking on what you want to do with the columns. Read the following articles: The "Numbers" or "Tally" Table: What it is and how it replaces a loop By Jeff Moden[/url]

    There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction By RBarry Young[/url]

    There Must Be 15 Ways To Lose Your Cursors… Part 2 By RBarry Young[/url]

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (11/21/2014)


    Damian-167372 (11/21/2014)


    Luis Cazares (11/21/2014)


    Damian-167372 (11/21/2014)


    yes your absolutely right I agree table variable is a overhead for this query,

    but for any other large queries I can use this method slightly change to a temp table instead of Table variable then i think its ok right?

    No, it's not. You're still using a loop when you shouldn't. Loops/cursors have their places and you posted a good example of them. A while loop shouldn't replace a cursor that's already working fine.

    I guess you didn't read my post i said "for any other large queries" that means for a cursor that loads lots of data, then this approch should be ok?

    Yes, I read it. Let me explain this again. If it's a great load for a cursor, then it would be a great load for a loop. You need to change the logic, stop thinking on what to do with each row and start thinking on what you want to do with the columns. Read the following articles: The "Numbers" or "Tally" Table: What it is and how it replaces a loop By Jeff Moden[/url]

    There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction By RBarry Young[/url]

    There Must Be 15 Ways To Lose Your Cursors… Part 2 By RBarry Young[/url]

    fwiw sometimes I replace a cursor with a while loop, not because the loop is any faster, but just because it can be less code to write and look at.

    that said, you have to be careful how you write the while loop to avoid O(N^2) runtimes.

    Gerald Britton, Pluralsight courses

  • g.britton (11/21/2014)


    Luis Cazares (11/21/2014)


    Damian-167372 (11/21/2014)


    Luis Cazares (11/21/2014)


    Damian-167372 (11/21/2014)


    yes your absolutely right I agree table variable is a overhead for this query,

    but for any other large queries I can use this method slightly change to a temp table instead of Table variable then i think its ok right?

    No, it's not. You're still using a loop when you shouldn't. Loops/cursors have their places and you posted a good example of them. A while loop shouldn't replace a cursor that's already working fine.

    I guess you didn't read my post i said "for any other large queries" that means for a cursor that loads lots of data, then this approch should be ok?

    Yes, I read it. Let me explain this again. If it's a great load for a cursor, then it would be a great load for a loop. You need to change the logic, stop thinking on what to do with each row and start thinking on what you want to do with the columns. Read the following articles: The "Numbers" or "Tally" Table: What it is and how it replaces a loop By Jeff Moden[/url]

    There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction By RBarry Young[/url]

    There Must Be 15 Ways To Lose Your Cursors… Part 2 By RBarry Young[/url]

    fwiw sometimes I replace a cursor with a while loop, not because the loop is any faster, but just because it can be less code to write and look at.

    that said, you have to be careful how you write the while loop to avoid O(N^2) runtimes.

    Less code with a while loop? I disagree. I have never seen a while loop that is less code than a cursor. Here is an article from Aaron Bertrand where 4 of his colleagues tried to demonstrate how a while loop is less code.

    http://sqlblog.com/blogs/aaron_bertrand/archive/2012/01/26/the-fallacy-that-a-while-loop-isn-t-a-cursor.aspx

    The one thing we can ALL agree on for sure is that we should try to avoid RBAR if at all possible. 😉

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Just out of curiousity but how do you use a cursor without using a while loop?

  • ZZartin (11/21/2014)


    Just out of curiousity but how do you use a cursor without using a while loop?

    Under the hood a cursor is a just a while loop on steroids that is true. This discussion has been about using an explicit while loop versus a cursor. 😉

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (11/21/2014)


    ZZartin (11/21/2014)


    Just out of curiousity but how do you use a cursor without using a while loop?

    Under the hood a cursor is a just a while loop on steroids that is true. This discussion has been about using an explicit while loop versus a cursor. 😉

    I guess I was just kind of confused why someone would think that explicitly loading a temp table then looping through it with a bunch of individual selects would ever be either more efficient or easier to code than just using a cursor with FETCH NEXT calls.

  • ZZartin (11/21/2014)


    Sean Lange (11/21/2014)


    ZZartin (11/21/2014)


    Just out of curiousity but how do you use a cursor without using a while loop?

    Under the hood a cursor is a just a while loop on steroids that is true. This discussion has been about using an explicit while loop versus a cursor. 😉

    I guess I was just kind of confused why someone would think that explicitly loading a temp table then looping through it with a bunch of individual selects would ever be either more efficient or easier to code than just using a cursor with FETCH NEXT calls.

    Me too!!!

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (11/21/2014)


    ZZartin (11/21/2014)


    Sean Lange (11/21/2014)


    ZZartin (11/21/2014)


    Just out of curiousity but how do you use a cursor without using a while loop?

    Under the hood a cursor is a just a while loop on steroids that is true. This discussion has been about using an explicit while loop versus a cursor. 😉

    I guess I was just kind of confused why someone would think that explicitly loading a temp table then looping through it with a bunch of individual selects would ever be either more efficient or easier to code than just using a cursor with FETCH NEXT calls.

    Me too!!!

    Because cursors are bad for performance, I don't care if explicit while loops are worse, I don't want those cursors. :hehe:

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (11/21/2014)


    Sean Lange (11/21/2014)


    ZZartin (11/21/2014)


    Sean Lange (11/21/2014)


    ZZartin (11/21/2014)


    Just out of curiousity but how do you use a cursor without using a while loop?

    Under the hood a cursor is a just a while loop on steroids that is true. This discussion has been about using an explicit while loop versus a cursor. 😉

    I guess I was just kind of confused why someone would think that explicitly loading a temp table then looping through it with a bunch of individual selects would ever be either more efficient or easier to code than just using a cursor with FETCH NEXT calls.

    Me too!!!

    Because cursors are bad for performance, I don't care if explicit while loops are worse, I don't want those cursors. :hehe:

    LOL. Thanks for the laugh Luis.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • ZZartin (11/21/2014)


    Sean Lange (11/21/2014)


    ZZartin (11/21/2014)


    Just out of curiousity but how do you use a cursor without using a while loop?

    Under the hood a cursor is a just a while loop on steroids that is true. This discussion has been about using an explicit while loop versus a cursor. 😉

    I guess I was just kind of confused why someone would think that explicitly loading a temp table then looping through it with a bunch of individual selects would ever be either more efficient or easier to code than just using a cursor with FETCH NEXT calls.

    It's not just about efficiency, though cursors are usually the least efficient way to solve a problem. It's also about the inherent DRY-violation. Since SQL lacks modern programming constructs, you have to issue the FETCH twice. Not too bad if there are only a few variables. Absolutely, completely terrible to read/debug/modify if you have 10, 20, 30 or more (yes, I've seen that many!).

    Gerald Britton, Pluralsight courses

Viewing 15 posts - 16 through 30 (of 50 total)

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