January 12, 2012 at 8:22 am
drew.allen (1/12/2012)
...the OP doesn't have ANY code in place and is looking for the best solution...
The thread starter was not Bob Cullen (the chap playing around with an alternative solution using recursive CTEs). The thread was started by nick-1043370, and solved umpteen posts ago with the code I posted. Bob spotted the off-by-one error, which I thanked him for. Notice that my full solution used an on-the-fly numbers table, not a recursive CTE.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 12, 2012 at 9:23 am
SQL Kiwi (1/12/2012)
drew.allen (1/12/2012)
...the OP doesn't have ANY code in place and is looking for the best solution...The thread starter was not Bob Cullen (the chap playing around with an alternative solution using recursive CTEs). The thread was started by nick-1043370, and solved umpteen posts ago with the code I posted.
That's exactly my point. Bob proposed the rCTE as a viable solution to the original poster's problem. That is the context. Whether Bob himself has the code installed in any of his production systems is outside of the context. You're argument assumes Bob's environment when the actual context is the OP's environment.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 12, 2012 at 9:28 am
ChrisM@home (1/12/2012)
SQL Kiwi (1/12/2012)
Jeff and Chris,You've missed the point I was making, and have chosen to assume that I am either (a) not aware of the performance issues involved; or (b) that I was offering general advice that it is ok to use rCTEs to generate numbers tables on the fly. I would have thought you both knew me better than that.
Paul, I assumed that you were suggesting that it's ok if the number of rows is not only small but is restricted, and I must admit I was more than a little taken aback. On re-reading the whole thread I can see that this isn't the case - but it is sufficiently woolly to be interpreted that way. Apologies, mate.
My apollogies... I assumed the same thing becuase of the statement "150ms is not 'significant' in the current context,"
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2012 at 9:54 am
Jeff Moden (1/12/2012)
My apollogies... I assumed the same thing becuase of the statement "150ms is not 'significant' in the current context,"
Ok, now you've got me curious again. Do you feel 150ms is significant in the context of the question in this thread? Or do you just mean that you misunderstood what I meant by 'current context'?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 12, 2012 at 10:02 am
drew.allen (1/12/2012)
That's exactly my point. Bob proposed the rCTE as a viable solution to the original poster's problem. That is the context. Whether Bob himself has the code installed in any of his production systems is outside of the context. You're argument assumes Bob's environment when the actual context is the OP's environment.
What is exactly your point? I am genuinely not following your line of reasoning here. Do you think my posted solution is not a viable solution, or are you saying that a recursive CTE could not form part of a viable solution to this specific problem? I also don't understand these references to Bob's environment - have you misread something along the way? Again, I am genuinely unable to follow you here (and I am trying to).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 12, 2012 at 12:38 pm
SQL Kiwi (1/12/2012)
What is exactly your point?
My point is that it's not only possible, but extremely likely to read your comments as recommending using an rCTE for new development for these kinds of problems.
Now I know that you would never recommend this for new development and that you're aware of the performance problems as the number of rows grows, but the average programmer who stumbles across this thread in a web search wouldn't be aware of those issues and could reasonably assume that it's perfectly fine to use rCTEs to solve these kinds of problems.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 12, 2012 at 4:51 pm
Wow, lots of discussion on the whys and wherefores here guys. Nice to have the big guns discussing the optimal solution to my question! 🙂
Got to say though, the solution works great for me, and I'd hate to think that when asking a question people who could propose a solution hesitated about responding with anything less than a perfectly optimised, ultimately scalable one. Speaking for myself I'd rather get something, work out whether it works in the context of the problem, and then re-pose the question if necessary. If someone else suggests a more optimal solution in the thread then fabulous, but the point is that it's all great stuff and it's really down to me to road-test it properly before putting it live.
Of course the responses are not just for my benefit but for the community at large, but I think I'd feel in the same boat if I were searching forums for a solution too.
Thanks again for all your comments folks.
Nick
January 12, 2012 at 5:03 pm
nick-1043370 (1/12/2012)
Speaking for myself I'd rather get something, work out whether it works in the context of the problem, and then re-pose the question if necessary. If someone else suggests a more optimal solution in the thread then fabulous, but the point is that it's all great stuff and it's really down to me to road-test it properly before putting it live.Of course the responses are not just for my benefit but for the community at large, but I think I'd feel in the same boat if I were searching forums for a solution too.
Welcome to the forums. It's nice to have you, and we need more like you. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 12, 2012 at 11:15 pm
Evil Kraig F (1/12/2012)
nick-1043370 (1/12/2012)
Speaking for myself I'd rather get something, work out whether it works in the context of the problem, and then re-pose the question if necessary. If someone else suggests a more optimal solution in the thread then fabulous, but the point is that it's all great stuff and it's really down to me to road-test it properly before putting it live.Of course the responses are not just for my benefit but for the community at large, but I think I'd feel in the same boat if I were searching forums for a solution too.
Welcome to the forums. It's nice to have you, and we need more like you. 🙂
Seconded.
By the way, I'm all for recommending 'best practices' and stuff, but sometimes the reaction to people mentioning cursors or rCTEs borders on zealotry. Cursors and WHILE loops are sometimes the best solution too. Yes they are.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 12, 2012 at 11:30 pm
Evil Kraig F (1/12/2012)
nick-1043370 (1/12/2012)
Speaking for myself I'd rather get something, work out whether it works in the context of the problem, and then re-pose the question if necessary. If someone else suggests a more optimal solution in the thread then fabulous, but the point is that it's all great stuff and it's really down to me to road-test it properly before putting it live.Of course the responses are not just for my benefit but for the community at large, but I think I'd feel in the same boat if I were searching forums for a solution too.
Welcome to the forums. It's nice to have you, and we need more like you. 🙂
I'm no big gun, just a coder - but this really stood out and demanded comment. Well done Nick, this is exactly how the forum works best.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 13, 2012 at 9:28 am
nick-1043370 (1/12/2012)
Wow, lots of discussion on the whys and wherefores here guys. Nice to have the big guns discussing the optimal solution to my question! 🙂Got to say though, the solution works great for me, and I'd hate to think that when asking a question people who could propose a solution hesitated about responding with anything less than a perfectly optimised, ultimately scalable one. Speaking for myself I'd rather get something, work out whether it works in the context of the problem, and then re-pose the question if necessary. If someone else suggests a more optimal solution in the thread then fabulous, but the point is that it's all great stuff and it's really down to me to road-test it properly before putting it live.
Of course the responses are not just for my benefit but for the community at large, but I think I'd feel in the same boat if I were searching forums for a solution too.
Thanks again for all your comments folks.
Nick
The main reason some of us go beyond a "it works in the context" solution is because there are people out there who -unlike your great attitude- copy a solution off the web and put it in production without any verification. Even worse, when such an attempt leads to poor performance, they might complain about a bad solution.
To avoid that, scaleable solutions are a preferred method. As well is the optimization of a suitable, but less scaleable solution. If there were only people like you around, we might have become less pedantic since we'd know the answer to a specific question would be put in the right perspective...
January 13, 2012 at 8:29 pm
LutzM (1/13/2012)
The main reason some of us go beyond a "it works in the context" solution is because there are people out there who -unlike your great attitude- copy a solution off the web and put it in production without any verification.
Laudable sentiment, but I'm not convinced catering for this audience should be our primary concern :w00t:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 13, 2012 at 9:29 pm
SQL Kiwi (1/13/2012)
LutzM (1/13/2012)
The main reason some of us go beyond a "it works in the context" solution is because there are people out there who -unlike your great attitude- copy a solution off the web and put it in production without any verification.Laudable sentiment, but I'm not convinced catering for this audience should be our primary concern :w00t:
These people need the help even if they don't realize it. I certainly wouldn't give them something that's performance challenged when something better exists.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2012 at 9:37 pm
SQL Kiwi (1/12/2012)
Jeff Moden (1/12/2012)
My apollogies... I assumed the same thing becuase of the statement "150ms is not 'significant' in the current context,"Ok, now you've got me curious again. Do you feel 150ms is significant in the context of the question in this thread? Or do you just mean that you misunderstood what I meant by 'current context'?
Heh... I'm not sure what you meant anymore. 🙂 On one hand, you seem to support avoiding counting rCTEs and then you seem to justify their use on the other. Because of previous misunderstandings, let me say that I realize the 150ms solution was for the generation of 4,000 dates (well beyond the requirements of this thread) and the number of milliseconds to generate the dates for the problem on this thread seem trivial to most folks. I may also have misread what you mean by "significant" so I'll try to clear that up, as well.
To me, 150ms to gen only 4,000 rows is a "significant" problem. Using that same code to gen only 12 rows is still a "significant" problem. It's absolutely true that the amount of time the code takes to run for 12 rows is "not significant" to most folks and will fit very well within anyone's expections for the code.
My problem with the counting rCTE code is that someone may use the code for something else larger. I don't want someone like the OP to come away from his post thinking "Wow! That (counting rCTE) worked great. I can think of dozens of other places to use such a thing." If the OP then uses such a thing to gen 10 rows for each day in a Start/End Date range contained in each row (a common request, lately), it'll work "within expectations". If the OP then turns around and uses it on a large batch of rows, you and I both know there will be a significant performance problem especially if such a batch is executed repeatedly throughout the day. Combine that problem with other snippets where the "Wow!" factor of a counting rCTE was applied, and your system has major performance problems where there should have been none.
Again, It's not that the code would cause a performance problem on this particular thread because, for most, it won't appear to be. I worry about folks taking something like a counting rCTE from a thread like this and using it somewhere else that requires more scalability.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2012 at 10:03 pm
Jeff Moden (1/13/2012)
SQL Kiwi (1/13/2012)
LutzM (1/13/2012)
The main reason some of us go beyond a "it works in the context" solution is because there are people out there who -unlike your great attitude- copy a solution off the web and put it in production without any verification.Laudable sentiment, but I'm not convinced catering for this audience should be our primary concern :w00t:
These people need the help even if they don't realize it. I certainly wouldn't give them something that's performance challenged when something better exists.
I doubt few people post deliberately inferior solutions (I know I try not to).
By "these people" and "them", are you referring to the person that posted the original question, or Joe Random that happens across the solution via a web search and puts it into production without any form of analysis or testing? A goal of only posting code that is guaranteed to always work well in such situations seems unrealistic to me, and so should not be a primary concern.
On the other hand, it is clearly reasonable to prefer certain generic methods for their broad applicability and general performance characteristics. All things being equal, it also makes sense to post code that stands the best chance of not blowing Joe Random's system up; but to state it again: this would not be my primary concern. If this is all you meant, then we are in agreement.
There again, taking the position that people should never consider using certain constructs, under any circumstances, seems overly prescriptive, and perhaps even a little arrogant. Innovation and experimentation should be encouraged in my view, not stifled by dogma and zelotry. There is no such thing as the perfect solution for all circumstances that would justify such a position.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 31 through 45 (of 55 total)
You must be logged in to reply to this topic. Login to reply