mister.magoo (11/12/2012)
I have made a revision and I would be interested if you could take a look at it and let me know what you think?It replaces the recursive CTE with my favourite toy - the Identity Hack.
Either they publish the article earlier where you hail from or you caught the article discussion link on the forums. I wasn't expecting any replies tonight, Magoo!
I've not tested your modifications, yet, but I did look them over and that's a VERY interesting hack on the rCTE that you've built. And, yeah, I can see that being a lot quicker and less resource intensive than an rCTE. Very well done and I'm looking forward to testing that bit of SQL prestidigitation out sometime tomorrow.
Thank you very much for the addition to the code and for the feeedback. It's always a pleasure to see you in action.
--Jeff Moden
Change is inevitable... Change for the better is not.
Just like the old cartoon, I've got to say... "Ah, Magoo! You've done it again!"
Here are the run results using the same machine I used in testing for the article for a million nodes using your fine addition.
Building the initial table and SortPath...
There are 1000000 rows in dbo.Hierarchy
Cumulative Duration = 00:00:14:420
Building the Nested Sets...
1000000 rows have been updated to Nested Sets
If the rowcounts don't match, there may be orphans.
Cumulative Duration = 00:00:42:023
Building the indexes...
Cumulative Duration = 00:00:47:330
===============================================
RUN COMPLETE
===============================================
In whole numbers, that a rock solid 13% improvement. Well done, Magoo!
My only question now is, is it the "Identity Hack" that did it or is it the fact that you used a While Loop to replacce the rCTE (and we've previously proven that such loops will frequenctly beat rCtEs). I'll give it a try tomorrow.
Speaking of that, tomorrow is only a half hour a way so I need to hit the hay or the people at work are going to have to put up with a really cranky ol' DBA tomorrow. Thanks again for the code, Magoo.
--Jeff Moden
Change is inevitable... Change for the better is not.
Jeff Moden (11/12/2012)
Just like the old cartoon, I've got to say... "Ah, Magoo! You've done it again!"Here are the run results using the same machine I used in testing for the article for a million nodes using your fine addition.
Building the initial table and SortPath...
There are 1000000 rows in dbo.Hierarchy
Cumulative Duration = 00:00:14:420
Building the Nested Sets...
1000000 rows have been updated to Nested Sets
If the rowcounts don't match, there may be orphans.
Cumulative Duration = 00:00:42:023
Building the indexes...
Cumulative Duration = 00:00:47:330
===============================================
RUN COMPLETE
===============================================
In whole numbers, that a rock solid 13% improvement. Well done, Magoo!
My only question now is, is it the "Identity Hack" that did it or is it the fact that you used a While Loop to replacce the rCTE (and we've previously proven that such loops will frequenctly beat rCtEs). I'll give it a try tomorrow.
Speaking of that, tomorrow is only a half hour a way so I need to hit the hay or the people at work are going to have to put up with a really cranky ol' DBA tomorrow.
Thanks again for the code, Magoo.
To the best of my knowledge, the identity has no effect on speed directly, but it allows us to "remember" a value between statements without using a variable, which in turn means the while loop can operate with just one statement... That is where the advantage is gained.
If you have to perform operations in the while loop to manipulate a variable it becomes costly.
The result of doing the inserts in this way is a series of set based inserts with no extra overhead.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Nice article, Jeff.
Ye good old fashioned "take controle and beat the system hands down" still does it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
A very nice article jeff, Its given me a few Ideas on how to change one of the Hierarchy builders I have, just need to find the down time to test it.
Looking forward to future installments.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
...whole books, hundreds of chapters, and thousands of Internet articles have been written on the subject and it's impossible for me to cover all of the aspects of the subject in a single article...
...I have to assume that you already know what the Adjacency List and Nested Sets structures are and how they are used...
Can anyone recommend a primer article or two on Adjacency List and Nested Sets structures?
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
GPO (11/13/2012)
...whole books, hundreds of chapters, and thousands of Internet articles have been written on the subject and it's impossible for me to cover all of the aspects of the subject in a single article...
...I have to assume that you already know what the Adjacency List and Nested Sets structures are and how they are used...
Can anyone recommend a primer article or two on Adjacency List and Nested Sets structures?
I don't normally reommend books but the most complete book on all 3 types of hierarchies (Adjacency List, Materialized Hierarchical Path, and Nested Sets) was written by Joe Celko. It's titled "Trees and Hierarchies in SQL for Smarties". His second edition of the book is out and available in places like Amazon.com.
I word of warning. Mr. Celko didn't write the book specifically for SQL Server. He writes in mostly ANSI Standard code. Although his examples are easy to understand, you will have to redact some of his code to make it work in SQL Server. The book is a pretty easy read so that shouldn't be a problem for most folks.
I'll also say that, in the first edition anyway, he doesn't have code to create huge hierarchies to do performance testing with. I don't know if he's added some code for that in the second edition or not because I've not read it. Feel free to use the one from this article if you'd like.
--Jeff Moden
Change is inevitable... Change for the better is not.
ALZDBA (11/13/2012)
Nice article, Jeff.Ye good old fashioned "take controle and beat the system hands down" still does it.
Thanks, Johan. The Tally Table does make a pretty good stick for beating the system good an proper.
--Jeff Moden
Change is inevitable... Change for the better is not.
Jason-299789 (11/13/2012)
A very nice article jeff, Its given me a few Ideas on how to change one of the Hierarchy builders I have, just need to find the down time to test it.Looking forward to future installments.
Thank you for the feedback, Jason. The article mentioned in the "p.s." comes out on Thursday, 15 November ( 2 days from now).
I'd be interested in knowing what your ideas are, if you have the time.
--Jeff Moden
Change is inevitable... Change for the better is not.
mister.magoo (11/13/2012)
Jeff Moden (11/12/2012)
Just like the old cartoon, I've got to say... "Ah, Magoo! You've done it again!"Here are the run results using the same machine I used in testing for the article for a million nodes using your fine addition.
Building the initial table and SortPath...
There are 1000000 rows in dbo.Hierarchy
Cumulative Duration = 00:00:14:420
Building the Nested Sets...
1000000 rows have been updated to Nested Sets
If the rowcounts don't match, there may be orphans.
Cumulative Duration = 00:00:42:023
Building the indexes...
Cumulative Duration = 00:00:47:330
===============================================
RUN COMPLETE
===============================================
In whole numbers, that a rock solid 13% improvement. Well done, Magoo!
My only question now is, is it the "Identity Hack" that did it or is it the fact that you used a While Loop to replacce the rCTE (and we've previously proven that such loops will frequenctly beat rCtEs). I'll give it a try tomorrow.
Speaking of that, tomorrow is only a half hour a way so I need to hit the hay or the people at work are going to have to put up with a really cranky ol' DBA tomorrow.
Thanks again for the code, Magoo.
To the best of my knowledge, the identity has no effect on speed directly, but it allows us to "remember" a value between statements without using a variable, which in turn means the while loop can operate with just one statement... That is where the advantage is gained.
If you have to perform operations in the while loop to manipulate a variable it becomes costly.
The result of doing the inserts in this way is a series of set based inserts with no extra overhead.
Ah... I missed that completely last night. Thanks, Magoo. I've been meaning to do a deeper dive on your "Identity Hack" since the first time you brought it up on the forums. I definitely need to take a closer look.
As a side bar, you should write a "spackle" article on the subject. As my boss wwould say, it's a "spec-hacular" trick.
--Jeff Moden
Change is inevitable... Change for the better is not.
Stick???
I'd say this article is more like a bat; and a really good tool to hammer this example into the minds of some data model dudez i need work with to develop an SNMP MIB database
Thanks for sharing !!!
Theo
Theo Ekelmans (11/13/2012)
Stick???I'd say this article is more like a bat; and a really good tool to hammer this example into the minds of some data model dudez i need work with to develop an SNMP MIB database
Thanks for sharing !!!
Theo
Heh... if you like what can be done with a bat, then you'll really like the 16# sledge hammer you'll find in the followup article on hierarchies coming out this Thursday (15 Nov 2012), Theo. It shows another bit of "black arts" magic that preaggregates the million node hierarchy to provide most of the answers to questions that you might ever ask of a hierarchy and it only takes 8 seconds longer.
Thanks for the feedback, Theo. I'd also be interested in a more detailed version of what you're doing for your SNMP MIB database. It sounds like a great deal of fun!
--Jeff Moden
Change is inevitable... Change for the better is not.
LOL, gimme thor class hammer!!!
The project i'm working on is a roof of concept SNMP MIB database, that is so fast it can do (near)realtime SNMP data packet to SQL data conversion
Just in case your new to the extensible SNMP MIB tree:
If E-mail addresses were OIDs... user@ordina.org would have been something like: user@ordina.enterprises.private.internet.dod.org.iso
or in non-translated notation: user@99999.1.4.1.6.3.1
except that we write the top-most part at the left: 1.3.6.1.4.1.99999.117.115.101.114
An OID is just a unique key (within one managed device) for one piece of information Ensures vendors don't have conflicting OIDs
OID corresponds to a label .1.3.6.1.2.1.1.5 => sysName (in windows this is the servername)
The complete (translated) path: .iso.org.dod.internet.mgmt.mib-2.system.sysName
The info needed to get from one to an other is "mib (database) files", that are supplied with each new device / software version.
The provided MIB files all have a part of the ginormous jigsaw MIB tree, and that needs to be searched between 500 to 20.000 times a minute.
Fun project indeed
Viewing 15 posts - 1 through 15 (of 99 total)
You must be logged in to reply to this topic. Login to reply