<p>How would you get tree-structured data from a database with the best performance? For example, say you have a folder-hierarchy in a database. Where the folder-database-row has <strong>ID</strong>, <strong>Name</strong> and <strong>ParentID</strong> columns.</p>

<p>Would you use a special algorithm to get all the data at once, minimizing the amount of database-calls and process it in code?</p>

<p>Or would you use do many calls to the database and sort of get the structure done from the database directly?</p>

<p>Maybe there are different answers based on x amount of database-rows, hierarchy-depth or whatever?</p>

<p><strong>Edit</strong>: I use Microsoft SQL Server, but answers out of other perspectives are interesting too.</p>

If you have many trees in the database, and you will only ever get the whole tree out, I would store a tree ID (or root node ID) and a parent node ID for each node in the database, get all the nodes for a particular tree ID, and process in memory.
However if you will be getting subtrees out, you can only get a subtree of a particular parent node ID, so you either need to store all parent nodes of each node to use the above method, or perform multiple SQL queries as you descend into the tree (hope there are no cycles in your tree!), although you can reuse the same Prepared Statement (assuming that nodes are of the same type and are all stored in a single table) to prevent re-compiling the SQL, so it might not be slower, indeed with database optimisations applied to the query it could be preferable. Might want to run some tests to find out.
If you are only storing one tree, your question becomes one of querying subtrees only, and the second answer applied.