Get month names ordered using recursion
-
the sqlist (3/13/2012)
CTE is nice at times but we have to keep in mind that it doesn't work on all platforms so I would avoid itI hope you don't mind me being a bit blunt, myself. Programming to the lowest common denominator is the same as having a fine color display scientific calculator and limiting yourself only to the 4 basic math functions simply because you might run into a calculator having only those 4 functions.
True code portability is for anything more than simple C.R.U.D. and some simple code is a myth. Don't limit yourself to "4 functions" even if you know your going to have to port the code. Make the code all that it can be.
--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) -
Tony Palmeri (3/13/2012)
I have a question. When you use "ROW_NUMBER" against an actual table, doesn't the query processor actually have to physically access that table, maybe do some disk reads, even if you aren't explicitly using any of the fields from that table? This is what bothers me about that 'alternative' solution to a TallyTable. Not that a TallyTable is any better. I still don't understand why T-SQL doesn't already have a built-in fuction that essentially creates an in-memory TallyTable on the fly, efficiently and algorithmically. I still like my suggestion earlier, a Table-Valued function built-in to SQL server that has all the features of a traditional ForNext loop. I say "built-in" to SQL Server, only because I am guessing that a UserDefined function might be terribly inefficient for some reason. If SQL Server natively had the algorithm to generate such a table efficiently in-memory on the fly without requiring Disk Access, it seems the ideal solution to me, which obviously has many applications.Actually, such a built in function has been suggested on CONNECT.
And, yes, I agree... "touching" a table will cause reads which is why many folks use Ben-Gan's cross-joined CTE's to produce Tally-like structures with lightning speed in memory without creating any reads.
Still and depending on what you're doing, the use of a Tally table can produce lightning quick results (especially after it caches for repeated use) and still have thousands of times fewer reads than "counting" rCTEs and While Loops. Same goes for if you built one on the fly using Row_Number(). Touch a table, make some reads... but with great speed and a whole lot less than a "counting" rCTE or "counting" While Loops.
--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) -
Thanks, Karthikeyan, for posting this. It's been an interesting discussion, and I learned quite a bit.
Best wishes,
Phil Factor -
Jeff Moden (3/14/2012)
the sqlist (3/13/2012)
CTE is nice at times but we have to keep in mind that it doesn't work on all platforms so I would avoid itI hope you don't mind me being a bit blunt, myself. Programming to the lowest common denominator is the same as having a fine color display scientific calculator and limiting yourself only to the 4 basic math functions simply because you might run into a calculator having only those 4 functions.
True code portability is for anything more than simple C.R.U.D. and some simple code is a myth. Don't limit yourself to "4 functions" even if you know your going to have to port the code. Make the code all that it can be.
No, I don't mind at all, be blunt all you want, it's free forum :-). Keep in mind though that to this day we still learn to count using our fingers and not logarithmic tables. When it comes to rocket science then it's another story but "to kill a fly you don't need a cannon". That is my point and I am sure you actually got it.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
-
My point is that you do need to kill the flys or the resulting swarm will result in posts like "Why is my database running so slow?" Besides, given a choice, all else being equal, would you chose something that ran slower and more resource intensive than the other?
Mind the pennies and the dollars will take care of themselves.
--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) -
Jeff Moden (3/15/2012)
My point is that you do need to kill the flys or the resulting swarm will result in posts like "Why is my database running so slow?" Besides, given a choice, all else being equal, would you chose something that ran slower and more resource intensive than the other?Mind the pennies and the dollars will take care of themselves.
Answer one question please. Do you thing in this case the recursive CTE is recommendable?
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
-
Simple, faster and still doesn't require tables.
select monthNumber
, DATENAME(MONTH,DATEADD(MONTH,monthNumber,0)- 1) MonthName
from ( values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12) ) as Months(monthNumber)
-
tobe (3/16/2012)
Simple, faster and still doesn't require tables.select monthNumber
, DATENAME(MONTH,DATEADD(MONTH,monthNumber,0)- 1) MonthName
from ( values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12) ) as Months(monthNumber)
That's cool. What is this!? :w00t:
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
-
That's cool. What is this!?
A variant of the code we've all been posting. This one looks neat but only runs on 2008, and according to my timings, runs at the same speed as all the other non-CTE versions. The CTE version runs 50% slower.
Best wishes,
Phil Factor -
the sqlist (3/15/2012)
Jeff Moden (3/15/2012)
My point is that you do need to kill the flys or the resulting swarm will result in posts like "Why is my database running so slow?" Besides, given a choice, all else being equal, would you chose something that ran slower and more resource intensive than the other?Mind the pennies and the dollars will take care of themselves.
Answer one question please. Do you thing in this case the recursive CTE is recommendable?
Apologies for the delay. No. I don't recommend using a recursive CTE for the objective stated in the article or any objective that requires incremental counting (sequencing, whatever you want to call it) using a recursive CTE. Although there are places where recursive CTEs are quite effective, recursive CTEs that count isn't one of those places no matter how small the count may be.
For an article with a bunch of pretty pictures on how bad counting with a recursive CTE, even for very small counts, really is, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/74118/
Here's a teaser from the article. The "red skyrocket" of a line on the left of the graph shows how comparatively slow recursive counting CTEs actually are compared to 3 other methods. The article also contains downloadable code in the "Resources" section at the bottom of the article so you can run your own tests on your own machines.
--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) -
This is pretty fast too:
SELECT n = (n1 + n2 + n3 + n4)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
Cheers
ChrisM
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM@Work (3/19/2012)
This is pretty fast too:SELECT n = (n1 + n2 + n3 + n4)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
Cheers
ChrisM
How do you control the range of values on such a thing, Chris? For example, what if the largest value you wanted from that is "13"?
--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) -
Jeff Moden (3/22/2012)
ChrisM@Work (3/19/2012)
This is pretty fast too:SELECT n = (n1 + n2 + n3 + n4)
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
Cheers
ChrisM
How do you control the range of values on such a thing, Chris? For example, what if the largest value you wanted from that is "13"?
Hi Jeff, I'll have to look into it at home - this gig is 2k5 only 🙁
I did a lot of work on exactly this subject, attempting to limit the output rowcount according to a variable.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Hi Jeff, I'll have to look into it at home - this gig is 2k5 only 🙁
I did a lot of work on exactly this subject, attempting to limit the output rowcount according to a variable.
This works, although I guess not in 2k5?
declare @highestValue as int = 13;
SELECT top (@highestValue + 1) n = (n1 + n2 + n3 + n4) [..]
... or maybe that is not what you meant?
>L<
-
Lisa Slater Nicholls (3/23/2012)
Hi Jeff, I'll have to look into it at home - this gig is 2k5 only 🙁
I did a lot of work on exactly this subject, attempting to limit the output rowcount according to a variable.
This works, although I guess not in 2k5?
declare @highestValue as int = 13;
SELECT top (@highestValue + 1) n = (n1 + n2 + n3 + n4) [..]
... or maybe that is not what you meant?
>L<
This is exactly what was meant Lisa - however, when you look at the EP, how many rows are actually generated? This may well be the maximum amount, which is subsequently filtered. The point Jeff's getting at is that many of the tools available for performing this task will only generate as many rows as are required - there's no filtration. That makes for a much cheaper tool.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 91 through 105 (of 129 total)
You must be logged in to reply to this topic. Login to reply