April 21, 2015 at 7:53 am
Hugo Kornelis (4/21/2015)
PHYData DBA (4/21/2015)
Hugo Kornelis (4/21/2015)
I don't like the script, and I like the advice to use WHILE instead of CURSOR even less....This can be "fixed" by first storing the results of the query in a temporary table with an additional IDENTITY column, but that adds more statements to the code (defying the "short code" argument of the author) and slows down performance.
Hugo,
Why create a temporary table with an Identity column? That was a way, but not the best way since 2005.
The ROW_NUMBER command was made for this.
https://technet.microsoft.com/en-us/library/ms186734(v=sql.90).aspx
If you intend to add a ROW_NUMBER() to the query inside the WHILE loop of the code in the article, then ... please don't. SQL Server will have to evaluate the ROW_NUMBER() every iteration of the function. For the first few iterations you MAY find yourself lucky and get a plan that only finds the first n rows, but even then, you are wasting a huge amount of performance.
If you intend to use ROW_NUMBER() instead of identity to populate the temporary table, then again ... please don't. IDENTITY works just as well for this purpose, but faster. The difference is not as big as with using ROW_NUMBER() in the loop, but there still is a difference.
By the way, this whole discussion is rather theoretic, since the best option is to avoid iteration at all, and the second best iteration is to use a cursor with the correct options (FAST_FORWARD if the data size may exceed available cache; STATIC READ_ONLY if the data will always be small enough to fit in cache even when the system is under load). So you would never use this option anyway. 😉
I would love to see the script and stats that prove Creating and populating Temporary table with an Identity column is faster and uses less resources than a one time execution of a select using ROW_Number.
That was the point.
Anything else you attach to it is of your own making.
April 21, 2015 at 8:02 am
PHYData DBA (4/21/2015)
Hugo Kornelis (4/21/2015)
PHYData DBA (4/21/2015)
Hugo Kornelis (4/21/2015)
I don't like the script, and I like the advice to use WHILE instead of CURSOR even less....This can be "fixed" by first storing the results of the query in a temporary table with an additional IDENTITY column, but that adds more statements to the code (defying the "short code" argument of the author) and slows down performance.
Hugo,
Why create a temporary table with an Identity column? That was a way, but not the best way since 2005.
The ROW_NUMBER command was made for this.
https://technet.microsoft.com/en-us/library/ms186734(v=sql.90).aspx
If you intend to add a ROW_NUMBER() to the query inside the WHILE loop of the code in the article, then ... please don't. SQL Server will have to evaluate the ROW_NUMBER() every iteration of the function. For the first few iterations you MAY find yourself lucky and get a plan that only finds the first n rows, but even then, you are wasting a huge amount of performance.
If you intend to use ROW_NUMBER() instead of identity to populate the temporary table, then again ... please don't. IDENTITY works just as well for this purpose, but faster. The difference is not as big as with using ROW_NUMBER() in the loop, but there still is a difference.
By the way, this whole discussion is rather theoretic, since the best option is to avoid iteration at all, and the second best iteration is to use a cursor with the correct options (FAST_FORWARD if the data size may exceed available cache; STATIC READ_ONLY if the data will always be small enough to fit in cache even when the system is under load). So you would never use this option anyway. 😉
I would love to see the script and stats that prove Creating and populating Temporary table with an Identity column is faster and uses less resources than a one time execution of a select using ROW_Number.
That was the point.
Anything else you attach to it is of your own making.
My comments are all in the context of this forum thread, the discussion of the article that uses a while loop to iterate over a result set. My comments were that the posted code relies on a consecutive increasing integer being required for that.
So I compare various ways of using ROW_NUMBER to run the while loop versus using IDENTITY to run the while loop.
And, again, a rather academic discussion, since there are better alternatives anyway.
EDIT: Fixed a typo ("content" instead of "context")
April 21, 2015 at 8:07 am
Hugo Kornelis (4/21/2015)
quote]
As I said... Of your own making.
Nothing to do with what I was saying.
Not wrong. Just not what I was talking about.
Nice that you notice the difference.
April 21, 2015 at 8:51 am
PHYData DBA (4/21/2015)
Hugo Kornelis (4/21/2015)
So I compare various ways of using ROW_NUMBER to run the while loop versus using IDENTITY to run the while loop.quote]
As I said... Of your own making.
Nothing to do with what I was saying.
Not wrong. Just not what I was talking about.
Nice that you notice the difference.
You might want to reread the discussion. Original article includes a WHILE loop that relies on a continuous increasing row number. I suggest ways of supplying that. You ask why I use identity and not row_number, because identity is "not the best way since 2005". I respond to that question, explaining why in this situation identity is in fact the best way. And then you strip part of my response out of its context and challenge that fragment. Nothing in the posts after that is of my own making - I only put my statements back in the context where you took them from.
I have no idea why you think that I ever wrote or implied that "Creating and populating Temporary table with an Identity column is faster and uses less resources than a one time execution of a select using ROW_Number". I most certainly did not, nor do I think that this is in any way relevant to the topic of this forum thread.
April 21, 2015 at 10:19 am
Hugo Kornelis (4/21/2015)
PHYData DBA (4/21/2015)
Hugo Kornelis (4/21/2015)
So I compare various ways of using ROW_NUMBER to run the while loop versus using IDENTITY to run the while loop.quote]
As I said... Of your own making.
Nothing to do with what I was saying.
Not wrong. Just not what I was talking about.
Nice that you notice the difference.
You might want to reread the discussion. Original article includes a WHILE loop that relies on a continuous increasing row number. I suggest ways of supplying that. You ask why I use identity and not row_number, because identity is "not the best way since 2005". I respond to that question, explaining why in this situation identity is in fact the best way. And then you strip part of my response out of its context and challenge that fragment. Nothing in the posts after that is of my own making - I only put my statements back in the context where you took them from.
I have no idea why you think that I ever wrote or implied that "Creating and populating Temporary table with an Identity column is faster and uses less resources than a one time execution of a select using ROW_Number". I most certainly did not, nor do I think that this is in any way relevant to the topic of this forum thread.
This is a lot of words that mean nothing, explain less, and contradict some of your other words.
Did you read through all the posts here?
Did you read the one where I include a select statement that returns the same information as the Cursor and while loops in the article?
Have an ordinary day Hugo.
April 21, 2015 at 11:05 am
PHYData DBA (4/21/2015)
Hugo Kornelis (4/21/2015)
PHYData DBA (4/21/2015)
Hugo Kornelis (4/21/2015)
So I compare various ways of using ROW_NUMBER to run the while loop versus using IDENTITY to run the while loop.quote]
As I said... Of your own making.
Nothing to do with what I was saying.
Not wrong. Just not what I was talking about.
Nice that you notice the difference.
You might want to reread the discussion. Original article includes a WHILE loop that relies on a continuous increasing row number. I suggest ways of supplying that. You ask why I use identity and not row_number, because identity is "not the best way since 2005". I respond to that question, explaining why in this situation identity is in fact the best way. And then you strip part of my response out of its context and challenge that fragment. Nothing in the posts after that is of my own making - I only put my statements back in the context where you took them from.
I have no idea why you think that I ever wrote or implied that "Creating and populating Temporary table with an Identity column is faster and uses less resources than a one time execution of a select using ROW_Number". I most certainly did not, nor do I think that this is in any way relevant to the topic of this forum thread.
This is a lot of words that mean nothing, explain less, and contradict some of your other words.
Did you read through all the posts here?
Did you read the one where I include a select statement that returns the same information as the Cursor and while loops in the article?
Have an ordinary day Hugo.
I hope the takeaway from all this is the original point that cursors and while loops should not be used unless absolutely necessary.
Edit: Like Andrew pointed out, maintenance routines are iterative. Mine are the same way. For data, however, the vast majority of tasks can be done in a set-based way.
April 21, 2015 at 1:44 pm
PHYData DBA (4/21/2015)
This is a lot of words that mean nothing, explain less, and contradict some of your other words.Did you read through all the posts here?
Did you read the one where I include a select statement that returns the same information as the Cursor and while loops in the article?
Have an ordinary day Hugo.
Whatever. I am done with this discussion. Let's return to SQL - much more fun.
April 22, 2015 at 7:04 am
Hugo Kornelis (4/21/2015)
PHYData DBA (4/21/2015)
This is a lot of words that mean nothing, explain less, and contradict some of your other words.Did you read through all the posts here?
Did you read the one where I include a select statement that returns the same information as the Cursor and while loops in the article?
Have an ordinary day Hugo.
Whatever. I am done with this discussion. Let's return to SQL - much more fun.
So happy you understood that point better than the last one.
November 29, 2016 at 8:25 am
Cursor or No Cursor.
This is a pretty old war that has been played out a lot on various discussions. Do I use cursors, yes. Do I use cursors on large data sets, hell no!! My typical use of cursors is looping through a small data set to perform some sort of maintenance action, example being loop through DBs to run back ups, custom update stat jobs etc... If you use the cursor with the default settings then yes its a hog, but define what parts of the cursor you want to use. This video from PASS with Kevin Kline and Aaron Bertrand explains in detail. skip over to 20:50 and Aaron will show via a demo how this works.
https://www.youtube.com/watch?v=XUCxQkFoqpw
Michael
thesurfingdba.weebly.com
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply