Tally OH! An Improved SQL 8K “CSV Splitter” Function
-
ChrisM@home (5/3/2011)
Jeff, please please please write a book, without changing your style. Give us all an opportunity to repay you for what you've done for us.+10!
Yes, please do write a book...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
WayneS (5/3/2011)
ChrisM@home (5/3/2011)
Jeff, please please please write a book, without changing your style. Give us all an opportunity to repay you for what you've done for us.+10!
Yes, please do write a book...
Oddly enough on the first of every month, I search Amazon for Jeff's name hoping to find a book for my kindle 🙂
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
WayneS (5/3/2011)
ChrisM@home (5/3/2011)
Jeff, please please please write a book, without changing your style. Give us all an opportunity to repay you for what you've done for us.+10!
Yes, please do write a book...
It would be nice
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Jeff Moden (5/2/2011)
WayneS (5/2/2011)
mtassin (5/2/2011)
WayneS (5/2/2011)
BTW, since the new code was not compared to a physical, permanent tally table, I thought that I would mention that while testing this code out for you, I decided to see how the new splitter with a permanent tally table (as compared to the virtual cte tally table in the article) would compare with all of the others - it's worse than the ctetally splitter, but better than all of the others.See, here's something I still don't get... I'm sure somebody can point me to another article that explains it, but how does a CTE tally outperform a table based tally? Wouldn't all the calculations and the lack of a tight clustered index mean that the table based tally should outperform? The memory footprint of the cte tally vs the table tally is about the same, and once the table tally is in cache, that's not an issue, so I don't get it.
Mark, the only possible explanation that I can give is the elimination of the disk IO. When I ran this test, I fully expected the physical tally table to eek out the ctetally. I was pretty surprised by the results, and ran it multiple times on several VMs to test it.
I wan't going to post the results of a real Tally Table against the cteTally, but it looks like I'll have to just for the sake of being thorough.
Here's the results that I have. The "DemilitedSplit8Kpt" is the new DelimitedSplit8K function, with a permanent tally table.
10 to 20 Characters per Element:
20 to 30 Characters per Element (it looks like something else was happening on my computer when the SplitXML ran the 250 elements...):
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
From Jeff Moden: I was considering writing an article about the all-too-"secret" characters 28-31 and a couple of other goodies in "control character land". Whatcha tink? Worthwhile or not?
Yes! I'd be very interested. There's one vote anyway.
-
SQLRNNR (5/3/2011)
WayneS (5/3/2011)
ChrisM@home (5/3/2011)
Jeff, please please please write a book, without changing your style. Give us all an opportunity to repay you for what you've done for us.+10!
Yes, please do write a book...
It would be nice
Just put all his articles together and you have a book.
would be fun to have a snippet of conversations he has with his team!
-
WayneS (5/3/2011)
Here's the results that I have. The "DemilitedSplit8Kpt" is the new DelimitedSplit8K function, with a permanent tally table.10 to 20 Characters per Element:
B'narg! Whaaaa? After a point I'd have expected the Perm table to stabilize more to the memory build.
Wow.
- Craig FarrellNever 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 -
Craig Farrell (5/3/2011)
WayneS (5/3/2011)
Here's the results that I have. The "DemilitedSplit8Kpt" is the new DelimitedSplit8K function, with a permanent tally table.10 to 20 Characters per Element:
B'narg! Whaaaa? After a point I'd have expected the Perm table to stabilize more to the memory build.
Wow.
Yah... I'm now going to have to create a view named tally that does it with CTE and then quietly drop the tally table I fought so hard to put into my db.
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
Craig Farrell (5/3/2011)
B'narg! Whaaaa? After a point I'd have expected the Perm table to stabilize more to the memory build.
Wow.
Try one of the two double-CTE versions I posted above (post 1102460); the single CTE simplification does something very bad.
http://www.sqlservercentral.com/Forums/FindPost1102460.aspx
These do beat the temporary tally table version by ~13-15%.
-
Ninja's_RGR'us (5/3/2011)
SQLRNNR (5/3/2011)
WayneS (5/3/2011)
ChrisM@home (5/3/2011)
Jeff, please please please write a book, without changing your style. Give us all an opportunity to repay you for what you've done for us.+10!
Yes, please do write a book...
It would be nice
Just put all his articles together and you have a book.
would be fun to have a snippet of conversations he has with his team!
Just so long as he hasn't recently offered to clear a table 😀 because there's no Open Windows!
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Nadrek (5/3/2011)
Craig Farrell (5/3/2011)
B'narg! Whaaaa? After a point I'd have expected the Perm table to stabilize more to the memory build.
Wow.
Try one of the two double-CTE versions I posted above (post 1102460); the single CTE simplification does something very bad.
http://www.sqlservercentral.com/Forums/FindPost1102460.aspx
These do beat the temporary tally table version by ~13-15%.
Temporary tally table? I thought our issues were with people like me who have a table named dbo.Tally sitting in our database for these purposes, I'd expect if I have to populate a table with 11,000 integers that I'd lose, that's why it's pre-built.
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
Craig Farrell (5/3/2011)
WayneS (5/3/2011)
Here's the results that I have. The "DemilitedSplit8Kpt" is the new DelimitedSplit8K function, with a permanent tally table.10 to 20 Characters per Element:
B'narg! Whaaaa? After a point I'd have expected the Perm table to stabilize more to the memory build.
Wow.
Actually, I had expected the Perm table to beat the memory one.
It did stabilize in relative terms to the memory one, but significantly higher.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Nadrek (5/3/2011)
Craig Farrell (5/3/2011)
B'narg! Whaaaa? After a point I'd have expected the Perm table to stabilize more to the memory build.
Wow.
Try one of the two double-CTE versions I posted above (post 1102460); the single CTE simplification does something very bad.
http://www.sqlservercentral.com/Forums/FindPost1102460.aspx
These do beat the temporary tally table version by ~13-15%.
Why don't you take your functions, integrate them into the test script included in the References section of the article, run the tests, and post the results for all of us to enjoy?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
WayneS (5/3/2011)
Actually, I had expected the Perm table to beat the memory one.It did stabilize in relative terms to the memory one, but significantly higher.
And confounded all of us. 🙂
Though I like the CTE... if I need 100,000 values in my tally table... it's less of an issue than with a physical one. 🙂
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
ChrisM@home (5/3/2011)
Just so long as he hasn't recently offered to clear a table 😀 because there's no Open Windows!Heh... that's the first lesson I teach every team. 😀
--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)
Viewing 15 posts - 91 through 105 (of 990 total)
You must be logged in to reply to this topic. Login to reply