Performance Tuning Guide
-
Matt,
the nature of the task requires some kind of ranking for the rows.
So, order MUST be present in any query performing this task.
Why Jeff's method is set based?
Because:
- it opens the recordset (table in this case) once;
- it accesses each value in the recordset once;
- for any row calculations it uses only data from the same row or from memory.
The trick Jeff used allowed him to accumulate results from previous rows calculations in memory and convert the "cursoring" task to set-based one.
_____________
Code for TallyGenerator -
I'd agree with your assessment - with the exception of it being set-based. Something that grows at least linearly with the size of the data, requiring you to touch everything IN ORDER, by that fact alone cannot be called "set-based". That's the actual definition of a set (the physical order of the subcomponents in the list is irrelevant).
Now if you were to put the words "efficient" or "well designed" in there instead of "set-based" (the one in the middle) - you'd get no futher arguments from me.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I know that those 3 criteria define set-based operation.
There is nothing about ordering in there.
And I see that Jeff's solution fits those criteria.
So, it's set-based, if it does use ordering or does not.
End in the end, any set-based process processes rows in some order.
Most of the don't care about order of the rows processed.
But for this particular task ranking is relevant, that's why Jeff had to enforce some particular ordering on top of set-based solution. It did not make it less set-based, as long as it still fits mentioned criteria.
_____________
Code for TallyGenerator -
It's the many that don't understand that we've run into in many a thread. Between the two of us, it's almost like a standing joke... except it's not funny.
--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) -
First, thank you for the high compliment.
The thing that makes it "set based" is the fact that only the effect on a column was considered. Yes, it appears that a row was considered, but that's not the case. Only the effect on a given column was considered. Very fine line between the two in this particular example. Very fine... instead of "for each row" it was "for every value in the column". Hard to distinguish in this case but only the effect on the column was considered.
The real thing is, it doesn't matter... as you pointed out, it's much more effecient than most folks would have come up with. Set based or not, it represents an extreme departure as to how the problem was resolved and that's the real key... can folks use what is availble to resolve the most effecient solution possible? Usually the answer is no... they don't realize the shape of the box they're in
--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) -
By the way, if the definition of "set based" is...
- it opens the recordset (table in this case) once;
- it accesses each value in the recordset once;
- for any row calculations it uses only data from the same row or from memory.
... we're all in trouble
... cursors meet the same criteria... --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) -
Not really.
Does not fit 1st requirement.
When using cursor you open recordset as many times as many rows it contains.
And in most cases cursors are created to have a chance to grab relevant to this particular row data from other tables. So, 3rd requirement typically is not fulfilled as well.
_____________
Code for TallyGenerator -
Great idea, let me know when and where I can order that book
. -
Heh... I understand what the rules are trying to say and (actually) very much agree with them, but I'm thinking that those definitions could be seriously misconstrued by those trying to justify a cursor... guess I'm too used to fighting those types of folks...
- it opens the recordset (table in this case) once; Cursor only opens one recordset one time in the declaration. Rule met for cursor?
- it accesses each value in the recordset once; Cursor "FETCH" access each value in the recordset only once, especially if it's a "firehose" cursor. Rule met for cursor?
- for any row calculations it uses only data from the same row or from memory. Cursor fetches data from the row into memory variables to be processed. Rule met for cursor?
See what I mean?
I think just one more rule needs to be added...
--affects/produces whole multi-row columns of data in/from the recordset with a single non-correlated query.
Like I said... just being careful not to give RBAR developers any excuses...
Or, maybe I'm just being paranoid 
--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) -
I'm not sure that those 3 are the ONLY requirements, which is why you're running into those struggles.
There's something about the nature of the operation needing to be able to be performed on multiple records at the same time, and the process needs to allow for that. I believe that's the discreteness criteria. so - when you increase your parallelism, set-based processes improve more than a smiple iterative one.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
- it opens the recordset (table in this case) once; Cursor only opens one recordset one time in the declaration. Rule met for cursor?
Yes, unless you use FETCH.
Cursor creates its own recordset - snapshot of selected records at the moment when you open cursor.
Then every FETCH opens that recordset and grabs values from it.
As soon as you call FETCH - rule is breached (first call for FETCH opens recordset 2nd time, first time was when cursor was opened).
Don't think there is a point to discuss further rules if 1st one proves - cursors are not set-based.
_____________
Code for TallyGenerator -
That was the key, Serqiy... thanks ol' friend.
--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) -
-
What? You got beer?
--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) -
Patience!
It's only 5pm here.
I need to pretend working for another hour!
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply