4 ms·

Retrieving the list will involve a lot of random seeks. A database is not that great at it versus scans.

• spiffytech 6y ago
  Fortunately modern SQL dialects that support recursive CTEs make the syntax for doing this in a single query approachable . I haven't found good data on how performant recursive CTEs are at scale, but it's surely better than doing the loop with round trips to the database inside your application code.
  • barrkel 6y ago
    Recursive CTEs are an excellent way to write a very slow query. You save the cost of network round trips, parsing the query, optimizing the join order etc., but your recursive CTE is otherwise executed once per iteration. For trees, that's typically the depth; for linked lists, it'll be the length of the list. In practice they both suck, and will be trivially outperformed by almost any other technique.
    • andreareina 6y ago
      A linked list is a degenerate tree, after all