InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 13:38:38 GMT20http://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/16/2012)[/b][hr]If you oppose the idea that people should be allowed to explore recursive solutions in general, please explain your reasoning.[/quote]Nope... not opposed to rCTEs... just counting rCTEs. I'll be careful to mention than rCTEs aren't a problem themselves in future posts. I may even add a prolog to the article to make sure that people seriously understand the difference but I'm not likely to back down on counting rCTEs being a problem until someone finds a way to make them perform a whole lot better.Mon, 16 Jan 2012 09:04:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]Jeff Moden (1/16/2012)[/b][hr]I have no problem with the debate and I don't even mind people like you being as dogmatic it their opposition to the article as I am for it. I agree that someone may pull a rabbit out of their hat for recursive counting CTEs. But posting a recursive counting solution using the current technique will not compel others to find a better way if people don't know a problem exists to begin with.[/quote]Certainly, rCTEs are not a good way to produce a large sequence of numbers. No-one is disputing that, or saying your article is rubbish, so relax a bit on that. The danger comes in extrapolating your results, and coming to the conclusion that all rCTE solutions of somewhat similar shape fall into the same basket. If you oppose the idea that people should be allowed to explore recursive solutions in general, please explain your reasoning.[quote]Heh... and if you're going to be dogmatic in your opposition to the article, please stop accusing me of posting the same "tired old dogma" about a real performance problem that has been publically tested and documented to exist for a given technique.[/quote]I don't oppose the article, just the unsafe extrapolation as I said above. And don't take the 'dogma' reference personally; it is aimed at the argument, not the person, and you were not the first, worst, or only one to have that reaction. I am simply asking that people take the anti-rCTE rhetoric down a notch, that is all.Mon, 16 Jan 2012 08:56:46 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]Jeff Moden (1/16/2012)[/b][hr]If I recall correctly, the "ultra-fast DISTINCT" is only fast when there are very few duplicates. The recursive counting CTE causes it to become much slower than a normal DISTINCT in the presence of many duplicates.[/quote]Well memory is a funny thing isn't it? You have the relationship between speed and duplicates backwards: its best case performance occurs when there is only one distinct value (i.e. everything is duplicated). Worst case performance is on a column with only unique values. On the million-row test, rCTE DISTINCT is faster than T-SQL DISTINCT if there are 10,000 or fewer distinct values (in other words, an average 100 duplicates per value).So if your knowledge of the data means you *know* there will be more than 100 values per item (a very common case) you can be confident of a win with the rCTE. With 1,000 duplicates per item, it's 10x faster...and as your own testing showed on that thread, if there are many duplicates, the rCTE can be a [i]thousand times faster[/i]. As you said yourself, [i]"...if you know you have a lot of dupes, this is the berries!"[/i][quote]Again, we have perceived code performance based on a small number of rows (and, yes, I remember you admitting that in the same thread). Knowing that, would you actually put such code into production? I know I would not.[/quote]So you disregard every algorithm that has awesome best-case performance, and poor worst-case performance? (Check out the characteristics of QuickSort some time, if so). I don't think you really want to get into a debate about putting unreliable code into production, do you? ;-)Mon, 16 Jan 2012 08:43:29 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/16/2012)[/b][hr]Yes, but how do you know there isn't a superior recursive solution to this problem? Are you assuming there isn't? I have no problem with people linking to your article, but you have to be prepared for people like me to debate you on this.[/quote]I have no problem with the debate and I don't even mind people like you being as dogmatic it their opposition to the article as I am for it. I agree that someone may pull a rabbit out of their hat for recursive counting CTEs. But posting a recursive counting solution using the current technique will not compel others to find a better way if people don't know a problem exists to begin with.Heh... and if you're going to be dogmatic in your opposition to the article, please stop accusing me of posting the same "tired old dogma" about a real performance problem that has been publically tested and documented to exist for a given technique. ;-)Mon, 16 Jan 2012 07:29:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/16/2012)[/b][hr]As you know, there are cases where recursive CTEs provide the optimal solution. Had I read, and taken literally, your article on counting rCTEs, I might never have discovered the ultra-fast DISTINCT algorithm, for example.[/quote]If I recall correctly, the "ultra-fast DISTINCT" is only fast when there are very few duplicates. The recursive counting CTE causes it to become much slower than a normal DISTINCT in the presence of many duplicates. Again, we have perceived code performance based on a small number of rows (and, yes, I remember you admitting that in the same thread). Knowing that, would you actually put such code into production? I know I would not.Mon, 16 Jan 2012 07:22:32 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]Jeff Moden (1/16/2012)[/b][hr]Heh.. hence the need for the same "tired old dogma". Although a counting rCTE might not be a significant part of THIS problem, people need to be warned about it for the "wider" picture.I believe the opposite, Paul. He was shown an article that would show him how to test his own convictions about counting through recursion if he was so moved. I don't believe that he was even aware of the potential problems of "standard" recursive counting methods. It was pretty obvious from his reply after reading the article that he was going to use the very same method in the article. Now that he knows of the potential problems, perhaps he'll be driven to find a better way.[/quote]Yes, but how do you [i]know[/i] there isn't a superior recursive solution to this problem? Are you assuming there isn't? I have no problem with people linking to your article, but you have to be prepared for people like me to debate you on this.As you know, there are cases where recursive CTEs provide the optimal solution. Had I read, and taken literally, your article on counting rCTEs, I might never have discovered the ultra-fast DISTINCT algorithm, for example.This is the reason I object to people being quite so heavy-handed in their opposition to 'counting rCTEs'. Seeking to limit discussion and experimentation through arguments like 'yeah but newbie x might copy-and-paste the code into a critical production system' irks me no end, to be honest. It's all good as far as it goes, but let's not get silly about it.Mon, 16 Jan 2012 05:59:52 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/15/2012)[/b][hr][quote][b]Jeff Moden (1/15/2012)[/b][hr]Were you or were you not saying that it would be ok to use a counting rCTE here (for this particular problem) or not?[/quote]I was saying that the choice of method to generate the numbers required was not a significant part of solving this particular problem.[/quote]Heh.. hence the need for the same "tired old dogma". Although a counting rCTE might not be a significant part of THIS problem, people need to be warned about it for the "wider" picture.[quote]Had Bob gone on to explore the option, he might have found a superior recursive solution to the wider problem...who knows. Point is, he was discouraged from even trying.[/quote]I believe the opposite, Paul. He was shown an article that would show him how to test his own convictions about counting through recursion if he was so moved. I don't believe that he was even aware of the potential problems of "standard" recursive counting methods. It was pretty obvious from his reply after reading the article that he was going to use the very same method in the article. Now that he knows of the potential problems, perhaps he'll be driven to find a better way.Mon, 16 Jan 2012 05:14:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]Jeff Moden (1/15/2012)[/b][hr]Were you or were you not saying that it would be ok to use a counting rCTE here (for this particular problem) or not?[/quote]I was saying that the choice of method to generate the numbers required was not a significant part of solving this particular problem. Had Bob gone on to explore the option, he might have found a superior recursive solution to the wider problem...who knows. Point is, he was discouraged from even trying.Sun, 15 Jan 2012 22:05:18 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx@Paul...[quote][b]SQL Kiwi (1/11/2012)[/b][hr][quote][b]drew.allen (1/11/2012)[/b][hr][quote][b]Bob Cullen-434885 (1/10/2012)[/b][hr]I started to look at an alternative approach, using a recursive CTE to generate a continuous list of dates that covered the report range[/quote]Jeff Moden has an article that shows how badly recursive CTEs can perform for certain types of tasks and it sounds like yours may fall into that category. Check out his article [url=http://www.sqlservercentral.com/articles/T-SQL/74118/][b]Hidden RBAR: Counting with Recursive CTE's[/b][/url][/quote]It's pretty tough to imagine a list of dates long enough to make any practical difference regarding performance here. Before anyone says it, yes I know that it often makes sense to code for the 10-million row case, but that would take us into the year 4749 here. A mere 4000 rows (150ms from Jeff's article graph) is more than enough to cover a ten-year period.[/quote]Were you or were you not saying that it would be ok to use a counting rCTE here (for this particular problem) or not?Sun, 15 Jan 2012 19:27:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/13/2012)[/b][hr][quote][b]LutzM (1/13/2012)[/b][hr]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- [color="#0000FF"]copy a solution off the web and put it in production without any verification[/color].[/quote]Laudable sentiment, but I'm not convinced catering for this audience should be our primary concern :w00t:[/quote]Agreed. I might have given the wrong reason for providing the best solution I know of (that usually is neither the best solution that exist nor always a generic method). Take it one step back: we don't always know how much the scenario has been simplified by the OP to a reasonable size for a forum. Leading to the same effect: a simple solution based on the sample data might even blow the OP's system.But I guess we're in agreement here: "this audience" is not our primary concern and the preference of certain generic methods for their broad applicability and general performance characteristics is reasonable. All good from my side (just a not-so-well-thought argument on my side at the beginning). :-DSat, 14 Jan 2012 02:42:33 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]Jeff Moden (1/13/2012)[/b][hr]On one hand, you seem to support avoiding counting rCTEs and then you seem to justify their use on the other.[/quote]Yikes. No, what I objected to (reasonably clearly, as far as I can tell) was the idea that using an rCTE for the specific solution in this thread was not a reasonable thing to even try. No solution based on such an effort has even been posted because the suggestion was immediately pounced on. My concern is that dissuading people from experimentation is not something I like to see encouraged. Not everyone is a know-nothing newbie; so novel uses of things like recursive CTEs are tools that people should be aware of. If nothing else, it is a very creative use of the feature and may help people understand how they work. Having more than one tool in your toolbox is always a good thing, and understanding when and where to use which tool is also important.[quote]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.[/quote]The generation of the numbers required in my posted solution (which used an on-the-fly numbers table, in any case!) is not a [b]significant[/b] part of the problem. The process of unpivoting the data into a temporary table and generating the dynamic cross-tab is much more [b]significant[/b]. That is what I meant. I used 4,000 rows and 150ms simply as an example of how much it doesn't matter here.[quote]My problem with the counting rCTE code is that someone may use the code for something else larger. 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.[/quote]There is no counting rCTE code on this thread! There was only a suggestion that one might try to build a solution for this specific problem based on one. If a solution had been posted, we could have discussed it, and people might have learnt something about where one might consider using such a thing.Instead, we have ended up the usual debate about whether counting rCTEs should ever be used. Your posts and articles target very new users of SQL Server, whereas I target a slightly different audience. There is room for both. If I had said anywhere on this thread that counting rCTEs are a tool of first choice, I would expect a reaction to that. I did not, and yet we still have people (none of which posted a solution) trotting out the same tired old dogma, as if I had.Fri, 13 Jan 2012 22:44:11 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]Jeff Moden (1/13/2012)[/b][hr][quote][b]SQL Kiwi (1/13/2012)[/b][hr][quote][b]LutzM (1/13/2012)[/b][hr]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.[/quote]Laudable sentiment, but I'm not convinced catering for this audience should be our primary concern :w00t:[/quote]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.[/quote]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 [i]primary[/i] 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.Fri, 13 Jan 2012 22:03:57 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/12/2012)[/b][hr][quote][b]Jeff Moden (1/12/2012)[/b][hr]My apollogies... I assumed the same thing becuase of the statement "150ms is not 'significant' in the current context,"[/quote]Ok, now you've got me curious again. Do you feel 150ms [i]is[/i] significant in the context of the question in this thread? Or do you just mean that you misunderstood what I meant by 'current context'?[/quote]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.Fri, 13 Jan 2012 21:37:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/13/2012)[/b][hr][quote][b]LutzM (1/13/2012)[/b][hr]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- [color="#0000FF"]copy a solution off the web and put it in production without any verification[/color].[/quote]Laudable sentiment, but I'm not convinced catering for this audience should be our primary concern :w00t:[/quote]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.Fri, 13 Jan 2012 21:29:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]LutzM (1/13/2012)[/b][hr]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- [color="#0000FF"]copy a solution off the web and put it in production without any verification[/color].[/quote]Laudable sentiment, but I'm not convinced catering for this audience should be our primary concern :w00t:Fri, 13 Jan 2012 20:29:08 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]nick-1043370 (1/12/2012)[/b][hr]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 [i]something[/i], 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[/quote]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...Fri, 13 Jan 2012 09:28:15 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]Evil Kraig F (1/12/2012)[/b][hr][quote][b]nick-1043370 (1/12/2012)[/b][hr]Speaking for myself I'd rather get [i]something[/i], 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 [b]it's really down to me to road-test it properly before putting it live.[/b]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.[/quote]Welcome to the forums. It's nice to have you, and we need more like you. :-)[/quote]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.Thu, 12 Jan 2012 23:30:15 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]Evil Kraig F (1/12/2012)[/b][hr][quote][b]nick-1043370 (1/12/2012)[/b][hr]Speaking for myself I'd rather get [i]something[/i], 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 [b]it's really down to me to road-test it properly before putting it live.[/b]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.[/quote]Welcome to the forums. It's nice to have you, and we need more like you. :-)[/quote]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.Thu, 12 Jan 2012 23:15:55 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]nick-1043370 (1/12/2012)[/b][hr]Speaking for myself I'd rather get [i]something[/i], 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 [b]it's really down to me to road-test it properly before putting it live.[/b]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.[/quote]Welcome to the forums. It's nice to have you, and we need more like you. :-)Thu, 12 Jan 2012 17:03:22 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspxWow, 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 [i]something[/i], 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.NickThu, 12 Jan 2012 16:51:44 GMTnick-1043370http://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/12/2012)[/b][hr][i]What[/i] is exactly your point?[/quote]My point is that it's not only possible, but extremely likely to read your comments as recommending using an rCTE for [b]new development[/b] for these kinds of problems.Now I know that you would never recommend this for [u]new development[/u] 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.DrewThu, 12 Jan 2012 12:38:34 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]drew.allen (1/12/2012)[/b][hr][color="#0000FF"]That's exactly my point[/color]. 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.[/quote][i]What[/i] 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).Thu, 12 Jan 2012 10:02:50 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]Jeff Moden (1/12/2012)[/b][hr]My apollogies... I assumed the same thing becuase of the statement "150ms is not 'significant' in the current context,"[/quote]Ok, now you've got me curious again. Do you feel 150ms [i]is[/i] significant in the context of the question in this thread? Or do you just mean that you misunderstood what I meant by 'current context'?Thu, 12 Jan 2012 09:54:28 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]ChrisM@home (1/12/2012)[/b][hr][quote][b]SQL Kiwi (1/12/2012)[/b][hr]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.[/quote]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 [i]isn't[/i] the case - but it is sufficiently woolly to be interpreted that way. Apologies, mate.[/quote]My apollogies... I assumed the same thing becuase of the statement "150ms is not 'significant' in the current context,"Thu, 12 Jan 2012 09:28:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/12/2012)[/b][hr][quote][b]drew.allen (1/12/2012)[/b][hr]...the OP doesn't have ANY code in place and is looking for the best solution...[/quote]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.[/quote]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.DrewThu, 12 Jan 2012 09:23:56 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]drew.allen (1/12/2012)[/b][hr]...the OP doesn't have ANY code in place and is looking for the best solution...[/quote]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.Thu, 12 Jan 2012 08:22:15 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/12/2012)[/b][hr]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.[/quote]The problem is that your point depends on an assumption that is not only not warranted, but actually contraindicated by the context of the discussion as a whole. Your argument depends on the fact that the rCTE is already in place and the question is whether to rewrite it, when it seems fairly obvious to me that the OP doesn't have ANY code in place and is looking for the best solution. Given the tenacity with which you have held your position despite the fact that it is fairly clearly new development, I don't see how anyone can reach any other conclusion.Even if your assumption were correct, the rCTE performs so badly compared to the other methods, I can't imagine a situation where you would choose not to rewrite the code at all. I would always choose to rewrite this code, although it may not have a high priority.DrewThu, 12 Jan 2012 08:06:16 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]ChrisM@home (1/12/2012)[/b][hr]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 [i]isn't[/i] the case - but it is sufficiently woolly to be interpreted that way. Apologies, mate.[/quote]No worries.Thu, 12 Jan 2012 07:14:19 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/12/2012)[/b][hr]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.[/quote]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 [i]isn't[/i] the case - but it is sufficiently woolly to be interpreted that way. Apologies, mate.Thu, 12 Jan 2012 07:10:39 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspxJeff 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.Thu, 12 Jan 2012 06:56:18 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]Jeff Moden (1/12/2012)[/b][hr][quote][b]SQL Kiwi (1/11/2012)[/b][hr]It's pretty tough to imagine a list of dates long enough to make any practical difference regarding performance here. Before anyone says it, yes I know that it often makes sense to code for the 10-million row case, but that would take us into the year 4749 here. A mere 4000 rows (150ms from Jeff's article graph) is more than enough to cover a ten-year period.[/quote]Spot on... what about the next guy that uses the code to generate individual dates according to Start/End date pairs for several thousand rows?I don't see the merit in ever using a counting rCTE for such a thing... the code takes longer to write, is physically longer and more complex than the other methods, and is guaranteed to have a performance problem if someone were to ever leverage the code for a larger purpose.[/quote]Sorry Paul, but I'm inclined to agree with Jeff on this one. Using a rCTE to generate a simple sequence of numbers (or dates) is wasteful of resources when you can get the rows (and maybe the numbers too) elsewhere at a lower cost and code complexity. I'm not saying rCTE's shouldn't be used for anything other than resolving hierarchies - as you know too well - just not for this purpose. Not even a mere 4000 rows. It's up to us to keep folks informed of where to use a particular tool or trick, and where not to - otherwise it's only a matter of time before someone posts up a performance problem with a "counting rCTE" within a loop, or worse. But hey - at least it will be easy to fix :-DThu, 12 Jan 2012 06:45:48 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/11/2012)[/b][hr]It's pretty tough to imagine a list of dates long enough to make any practical difference regarding performance here. Before anyone says it, yes I know that it often makes sense to code for the 10-million row case, but that would take us into the year 4749 here. A mere 4000 rows (150ms from Jeff's article graph) is more than enough to cover a ten-year period.[/quote]Spot on... what about the next guy that uses the code to generate individual dates according to Start/End date pairs for several thousand rows?I don't see the merit in ever using a counting rCTE for such a thing... the code takes longer to write, is physically longer and more complex than the other methods, and is guaranteed to have a performance problem if someone were to ever leverage the code for a larger purpose.Thu, 12 Jan 2012 06:25:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/11/2012)[/b][hr][quote][b]drew.allen (1/11/2012)[/b][hr]I disagree.[/quote]No worries.[quote]Granted, if this method was being used in production code you would have to consider the context to determine whether it was worth rewriting the code to improve performance...[/quote]Exactly.[quote]...but that's not the case here.[/quote]Why not?[/quote]I have found that, although the original author of a given piece of code may, indeed, carefully consider the context in which it is being used (ie. limited number of rows), the NEXT person to run across the code may not. Although you'd like to think that someone leveraging an existing piece of code would do a little testing, they usually do not because they 1) wouldn't be looking for the code if they knew more about SQL Server and T-SQL methods and 2) they're desperate for a solution. Such desperation is normally accompanied by a schedule crunch and they won't test it in a larger environment because they don't have the time.I've also found that supposed limits on a number of rows is frequently incorrect.With all of that in mind, I try to protect folks who may not know and may be in a time crunch or simply won't take the time to do a little verification of performance on their own by reminding them that a counting CTE is frequently worse than a While loop and that, considering how many other methods there are to solve the problem correctly, should be avoided at all cost.Besides... why would anyone [i]intentionally [/i]write performance challenged code?Thu, 12 Jan 2012 06:15:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]stephen99999 (1/11/2012)[/b][hr]Wouldnt a date table work in this situation as well? sort of like what we find in data warehouses?[/quote]Yes it would.Wed, 11 Jan 2012 13:12:01 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspxWouldnt a date table work in this situation as well? sort of like what we find in data warehouses?Wed, 11 Jan 2012 13:08:44 GMTstephen99999http://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]drew.allen (1/11/2012)[/b][hr]I disagree.[/quote]No worries.[quote]Granted, if this method was being used in production code you would have to consider the context to determine whether it was worth rewriting the code to improve performance...[/quote]Exactly.[quote]...but that's not the case here.[/quote]Why not?Wed, 11 Jan 2012 09:50:28 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/11/2012)[/b][hr]You might have missed that I quoted the performance impact directly from the article "(150ms from Jeff's article graph)", which is one I have read many times (see the article's discussion thread for my comments). 150ms is not 'significant' in the current context, and it is 46ms on my laptop in any case:[/quote]I disagree. 150ms is [b]THIRTY[/b] times worse than the next worse algorithm. I think that's pretty significant. I can see no reason to recommend incorporating this method into new code when we know how badly it performs.Granted, if this method was being used in production code you would have to consider the context to determine whether it was worth rewriting the code to improve performance, but that's not the case here.DrewWed, 11 Jan 2012 08:51:56 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]drew.allen (1/11/2012)[/b][hr]If you read the article, you'll see that even the mere 4000 row CTE has a significant performance impact. That's how badly recursive CTEs can perform.[/quote]You might have missed that I quoted the performance impact directly from the article "(150ms from Jeff's article graph)", which is one I have read many times (see the article's discussion thread for my comments). 150ms is not 'significant' in the current context, and it is 46ms on my laptop in any case:[code="sql"]SET STATISTICS TIME ON;WITH R AS (SELECT 1 AS N UNION ALL SELECT R.N + 1 FROM R WHERE R.N <= 4000)SELECT * INTO #bucket FROM R OPTION (MAXRECURSION 0)SET STATISTICS TIME OFF;[/code]Wed, 11 Jan 2012 08:14:58 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]SQL Kiwi (1/11/2012)[/b][hr][quote][b]drew.allen (1/11/2012)[/b][hr][quote][b]Bob Cullen-434885 (1/10/2012)[/b][hr]I started to look at an alternative approach, using a recursive CTE to generate a continuous list of dates that covered the report range[/quote]Jeff Moden has an article that shows how badly recursive CTEs can perform for certain types of tasks and it sounds like yours may fall into that category. Check out his article [url=http://www.sqlservercentral.com/articles/T-SQL/74118/][b]Hidden RBAR: Counting with Recursive CTE's[/b][/url][/quote]It's pretty tough to imagine a list of dates long enough to make any practical difference regarding performance here. Before anyone says it, yes I know that it often makes sense to code for the 10-million row case, but that would take us into the year 4749 here. A mere 4000 rows (150ms from Jeff's article graph) is more than enough to cover a ten-year period.I congratulate Bob for finding my error and pursuing an alternative line of enquiry. My concern is that we shouldn't be [i]too[/i] keen in promoting The One True Way to generate numbers such that we end up stifling innovation and exploration.[/quote]If you read the article, you'll see that even the mere 4000 row CTE has a significant performance impact. That's how badly recursive CTEs can perform.DrewWed, 11 Jan 2012 07:46:25 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1231401-392-1.aspx[quote][b]drew.allen (1/11/2012)[/b][hr][quote][b]Bob Cullen-434885 (1/10/2012)[/b][hr]I started to look at an alternative approach, using a recursive CTE to generate a continuous list of dates that covered the report range[/quote]Jeff Moden has an article that shows how badly recursive CTEs can perform for certain types of tasks and it sounds like yours may fall into that category. Check out his article [url=http://www.sqlservercentral.com/articles/T-SQL/74118/][b]Hidden RBAR: Counting with Recursive CTE's[/b][/url][/quote]It's pretty tough to imagine a list of dates long enough to make any practical difference regarding performance here. Before anyone says it, yes I know that it often makes sense to code for the 10-million row case, but that would take us into the year 4749 here. A mere 4000 rows (150ms from Jeff's article graph) is more than enough to cover a ten-year period.I congratulate Bob for finding my error and pursuing an alternative line of enquiry. My concern is that we shouldn't be [i]too[/i] keen in promoting The One True Way to generate numbers such that we end up stifling innovation and exploration.Wed, 11 Jan 2012 07:08:49 GMTPaul White