The "Numbers" or "Tally" Table: What it is and how it replaces a loop
-
Lynn Pettis (5/4/2009)
Hearing the differences between Oracle and SQL Server, although interesting, is getting a bit old.Let's remember one crucial thing SQL Server Oracle.
Let's stop complaining about the differences and just learn to use the tools we have available. They are both different products, and there really is no reason to try and make them the same.
Lynn, I totally agree 100% on all of the above.
[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font] -
JohnG (5/4/2009)
peter (5/4/2009)
True, from an ADO client application. However, It doesn't work from T-SQL. I.e., You cannot consume a result set returned by a stored procedure within T-SQL except through a CURSOR output parameter. That is why Microsoft has been providing two different flavors of some of the system stored procedures.Sure you can... OPENQUERY. OPENROWSET. Etc. Do those routines have an implit cursor? I'm sure they do... so does a SELECT. 🙂
--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) -
JohnG (5/4/2009)
To satisfy Jeff's curiosity...Thanks, John.
About a year ago, I figure out how to make a strip function that manufactured the Tally table on the fly much as some of the CTE solutions in SQL Server do. I'll see if I can find it because, if I recall the testing we did, it was awfully fast compared to a While Loop even in Oracle. Not sure if I have it at home or left it at the job I no longer work at.
--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, John -- thank you guys, both. The tally table isn't going to work here. Oracle is giving me an ORA-3113 when I attempt to perform an Insert Into. I'm going to take a stab at using the code John put up to build a table so that I can use it in the join. This is likely a bug in Oracle, well, certainly, not likely. Even if it is bad, Oracle should have reported it during the build, or it should have executed the exception handler -- instead, it appears to kill the TNS process, causing processing to abort.
I'm sure there's a patch out there, but patching our test system won't tell me anything about the customer's system, which is even older than ours, and likely contains the same bug. So I'm going to have to rewrite the whole package to maintain consistency in coding practices.
I just wanted to say thanks, you guys have been a great help!
-rod
-
I found the code I used to gen a Tally table on the fly using what Oracle calls "Subquery Refactoring" which is a fancy name for "CTE". Add a WHERE clause to the lower outer query to limit the return.
WITH TALLY AS (
SELECT 1 just_a_column
FROM DUAL
GROUP BY CUBE(1,2,3,4,5,6,7,8,9,10)
)
SELECT ROWNUM
FROM TALLY t1, TALLY t2
--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 have a problem where a value in one row of the record set is base on the value of the previous row. I hate doing RBAR so I tried recursion. Recursion worked well until I ran into the recursive limit. Then I found this article. It gave me a new paradigm. I ended up with this little bit of code that works well. Because I would be working with an identity field with will work it's way into the millions, I set a second reference to this highly indexed table and used the second reference as my tally table.
select document.DocumentId, SUM(tally.NumPages) as beginningPage, SUM(tally.NumPages) + document.NumPages as endingPage
from Document
cross join Document tally
where tally.DocumentId < document.DocumentId
group by document.DocumentId, document.NumPages
-
fun_sunshine_summer (5/19/2009)
I have a problem where a value in one row of the record set is base on the value of the previous row. I hate doing RBAR so I tried recursion. Recursion worked well until I ran into the recursive limit. Then I found this article. It gave me a new paradigm. I ended up with this little bit of code that works well. Because I would be working with an identity field with will work it's way into the millions, I set a second reference to this highly indexed table and used the second reference as my tally table.select document.DocumentId, SUM(tally.NumPages) as beginningPage, SUM(tally.NumPages) + document.NumPages as endingPage
from Document
cross join Document tally
where tally.DocumentId < document.DocumentId
group by document.DocumentId, document.NumPages
You may want to be a little skeptical of that method. What you've done is build what's known as a "Triangular Join". See the following article for why that can be even worse than a cursor....
http://www.sqlservercentral.com/articles/T-SQL/61539/
If I knew more about the structure of your Document table and had a bit of sample data, one of us could probably show you a better way. Please see the following article for the best way to post sample data and table structure....
http://www.sqlservercentral.com/articles/Best+Practices/61537/
--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) -
[font="Verdana"]Jeff: it's a great article, and the fundamental basis for so many optimisation techniques that I have seen you and many other people talk about.
I have to say that it's only really been this year with the code presented by people such as yourself and Lyn and Barry that the concept of the tally (or "numbers") table really clicked with me. So thanks for covering an amazingly useful concept so well. 😀
[/font]
-
Yes, Jeff's article is great info, and I'm sure he'll thank you for the feedback. I'm glad we could help you understand what a powerful tool the tally or numbers table can be. I have found quite useful, and owe it to Jeff as well.
He is an awesome mentor.
-
I'd have to agree!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Bruce W Cassidy (6/11/2009)
[font="Verdana"]Jeff: it's a great article, and the fundamental basis for so many optimisation techniques that I have seen you and many other people talk about.I have to say that it's only really been this year with the code presented by people such as yourself and Lyn and Barry that the concept of the tally (or "numbers") table really clicked with me. So thanks for covering an amazingly useful concept so well. 😀
[/font]
Sorry for the delay, Bruce... I've been wicked busy and have let my email responses slide a bit. Thanks for the awsome feedback. And I absolutely agree... Barry and Lynn have come up with some pretty amazing uses for that little slice of computational heaven in the last year or so.
--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) -
Barry and Lynn... thanks for the huge compliment. You guys are gonna make me blush. 🙂
--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 (6/12/2009)
Barry and Lynn... thanks for the huge compliment. You guys are gonna make me blush. 🙂Why? Are my pork chops showing? :w00t:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
--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 finally dived in and started to try and understand how to use tally tables, and didn't take long to hit a wall. I tried the orders summary example near the bottom of your article, and modified it to work on a view I have. To begin with I stripped it down to its bare bones, as follows:
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
--===== Find the min and max dates in the range of data
SELECT @DateStart = MIN(SalesDate),
@DateEnd = MAX(SalesDate)
FROM dbo.vwTableau_GP_Shipments
SELECT t.N-1+@DateStart AS ShippedDate
FROM tempdb.dbo.Tally t
WHERE t.N-1+@DateStart <= @DateEnd
As you can see, I reduced it to simply generating the derived table. Instead of looking at Northwind.dbo.Orders, it is looking at a view I have. SalesDate is a datetime column, with no nulls.
When I execute this code I get the following error message:
Msg 8115, Level 16, State 2, Line 11
Arithmetic overflow error converting expression to data type datetime.
Any advice, anyone? I've looked and looked and can't figure it out. There are 3405 distinct salesdates in the view, and I get (in most cases!) only 3272 rows back before I get the error message. I don't expect faulty code from your articles, so the issue must be on my end, but I can't see it. thx. d lewis
Viewing 15 posts - 241 through 255 (of 511 total)
You must be logged in to reply to this topic. Login to reply