Slow running "ranking" problem

Question

Slow running "ranking" problem

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Heh... it's funny. I was going to suggest the same things about the method I use. I maintain the adjacency list (with a detector to prevent loops) and regen the nested sets hierarchy when there are changes (which is now incredibly fast using the construction method I identify in the both articles).

Don't even think about trying to add, move, or delete in nested sets. Maintain the adjacency list and regen the nested sets after changes. Best of both worlds.

--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)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

To add to what Jeff said, it's really not that hard to write a bunch of stored procedures for the nested set model. I published a few of these in various places, but you need a routine to renumber the left and right structure numbers, remove an entire subtree, insert an entire subtree, remove a single node and then promote the deleted node's subordinates, do some counts and whatever else you happen to need. The integer math is very simple, consisting of multiplying or dividing by two and incrementing or decrementing.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

jcelko212 32090 wrote:

To add to what Jeff said, it's really not that hard to write a bunch of stored procedures for the nested set model. I published a few of these in various places, but you need a routine to renumber the left and right structure numbers, remove an entire subtree, insert an entire subtree, remove a single node and then promote the deleted node's subordinates, do some counts and whatever else you happen to need. The integer math is very simple, consisting of multiplying or dividing by two and incrementing or decrementing.

It's much easier to maintain the hierarchy using an Adjacency List and just rebuild the Nested Sets from that. I recently tested the methods in the "Hierarchies on Steroids" articles on my newer laptop... it'll rebuild the Nested Sets for a million node Hierarchy in 19 seconds instead of the previous 54 seconds (which smoked all other methods even then).

--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)