November 21, 2014 at 10:09 am
Damian-167372 (11/21/2014)
ok this what i have come up.. so you can do itDECLARE @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/
November 21, 2014 at 10:23 am
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?
November 21, 2014 at 10:35 am
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.
November 21, 2014 at 10:43 am
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/
November 21, 2014 at 10:44 am
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?
November 21, 2014 at 10:55 am
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]
November 21, 2014 at 11:09 am
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
November 21, 2014 at 12:46 pm
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.
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/
November 21, 2014 at 12:49 pm
Just out of curiousity but how do you use a cursor without using a while loop?
November 21, 2014 at 12:53 pm
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/
November 21, 2014 at 1:13 pm
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.
November 21, 2014 at 1:23 pm
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/
November 21, 2014 at 1:36 pm
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:
November 21, 2014 at 1:40 pm
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/
November 21, 2014 at 2:00 pm
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