3 ms·

I tackled this problem on appengine a while ago. Turns out that the average discussion thread has few enough comments that they can be fetched from the db in o

by csytan 16y ago

I tackled this problem on appengine a while ago. Turns out that the average discussion thread has few enough comments that they can be fetched from the db in one call (e.g. by the thread_id). This makes even deeply nested comments fairly simple to query for, and then it is quite easy to rebuild the tree in memory. I think reddit uses this method too.

seldo 16y ago
Ah, the dreaded phrase "it turns out", offering assertions with no evidence ( http://jsomers.net/blog/it-turns-out http://jsomers.net/blog/it-turns-out ) :-) I believe the author's point is that using these recursive SQL queries is more efficient, as the DB engine is inherently faster than your (presumably) scripting language. So this solution is for when the in-memory method is failing to scale.
- jbm 16y ago
  Is the DB engine really inherently faster than the scripting language? http://highscalability.com/blog/2010/3/23/digg-4000-performance-increase-by-sorting-in-php-rather-than.html http://highscalability.com/blog/2010/3/23/digg-4000-performa... Not saying necessarily that the DB engine is less efficient, but rather that it is serves as a limiting factor and anything you can offload onto other servers is a good solution (for highly-visited websites).
  - trezor 16y ago
    Is the DB engine really inherently faster than the scripting language? As for anything, the answer is "it depends". It depends on what you want to do. In this case that is grab all rows in a specific dataset. If you in any case want to grab the dataset X, consisting of a given set of rows, asking the database to figure how to fetch all rows in one operation as efficiently as possible will (or at least should on any respectable database engine) be more efficient than having the scripting language sending multiple requests which can only be optimized one by one. For reference, I've only tried recursive queries in Microsoft SQL Server, but they are pretty fucking neat from a geeky perspective.
    - matwood 16y ago
      Yeah, I was about to mention that MSSQL has had this 'feature' for years. I've used it a lot for retrieving organizational structures from the typical parent-child table structure. The best part of it, is that it is MUCH simpler than either trying to roll your own through cursors or dealing with it in whatever language outside the database.
- csytan 16y ago
  Here's the evidence: http://www.reddit.com/r/programming/comments/8q2pz/ask_reddit_how_do_you_store_a_tree_in_a/c0a2rt7 http://www.reddit.com/r/programming/comments/8q2pz/ask_reddi... In my case, Appengine does not support recursive SQL queries, so another method had to be used.

by csytan 16y ago

seldo 16y ago

Ah, the dreaded phrase "it turns out", offering assertions with no evidence ( http://jsomers.net/blog/it-turns-out http://jsomers.net/blog/it-turns-out ) :-) I believe the author's point is that using these recursive SQL queries is more efficient, as the DB engine is inherently faster than your (presumably) scripting language. So this solution is for when the in-memory method is failing to scale.

jbm 16y ago
Is the DB engine really inherently faster than the scripting language? http://highscalability.com/blog/2010/3/23/digg-4000-performance-increase-by-sorting-in-php-rather-than.html http://highscalability.com/blog/2010/3/23/digg-4000-performa... Not saying necessarily that the DB engine is less efficient, but rather that it is serves as a limiting factor and anything you can offload onto other servers is a good solution (for highly-visited websites).
- trezor 16y ago
  Is the DB engine really inherently faster than the scripting language? As for anything, the answer is "it depends". It depends on what you want to do. In this case that is grab all rows in a specific dataset. If you in any case want to grab the dataset X, consisting of a given set of rows, asking the database to figure how to fetch all rows in one operation as efficiently as possible will (or at least should on any respectable database engine) be more efficient than having the scripting language sending multiple requests which can only be optimized one by one. For reference, I've only tried recursive queries in Microsoft SQL Server, but they are pretty fucking neat from a geeky perspective.
  - matwood 16y ago
    Yeah, I was about to mention that MSSQL has had this 'feature' for years. I've used it a lot for retrieving organizational structures from the typical parent-child table structure. The best part of it, is that it is MUCH simpler than either trying to roll your own through cursors or dealing with it in whatever language outside the database.
csytan 16y ago
Here's the evidence: http://www.reddit.com/r/programming/comments/8q2pz/ask_reddit_how_do_you_store_a_tree_in_a/c0a2rt7 http://www.reddit.com/r/programming/comments/8q2pz/ask_reddi... In my case, Appengine does not support recursive SQL queries, so another method had to be used.