MySQL stored procedure vs. multiple selects

Here's my scenario:

I've got a table of (let's call them) nodes. Primary key on each one is simply "node_id".

I've got a table maintaining a hierarchy of nodes, with only two columns: parent_node_id and child_node_id.

The hierarchy is maintained in a separate table because nodes can have an N:N relationship. That is to say, one node can have multiple children, and multiple parents.

If I start with a node and want to get all of its ancestors (i.e. everything higher up the hierarchy), I could either do several selects, or do it all in one stored procedure.

Anyone with any practical experience with this question know which one is likely to have the best performance? I've read things online that recommend both ways.


"which one is likely to have the best performance? " : No one can know ! The only thing you can do is try both and MEASURE. That's sadly enough the main answer to all performance related questions... except in cases where you clearly have a O(n) difference between algorithms.

And, by the way, "multiple parents" does not make a hierarchy (otherwise I would recommend to read some books by Joe Celko) but a DAG (Direct Acyclic Graph) a much harder beast to tame...

Need Your Help

Why won't my WPF style trigger work to only show the horizontal scroll bar when the mouse is over my ScrollViewer?

c# .net wpf xaml scrollviewer

I want to create a ScrollView that only shows the horizontal scroll bar when the mouse is over it, so I tried the following (note how I simply try to trigger the HorizontalScrollBarVisibility to Auto

How to make a page remain and not redirect when downloading a file?

java javascript jsp

I have a JSP that has a link, which, when clicked, downloads a file. My problem is that when I click on the link, it goes to blank page and remains there. I would either like for it to return to th...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.