4 ms·
This seems to be taking advantage of a specific Oracle feature whose performance I have no idea about, there are suggestions that Common Table Expressions could
by sequence7 12y ago
This seems to be taking advantage of a specific Oracle feature whose performance I have no idea about, there are suggestions that Common Table Expressions could do the same sort of thing but since CTE performance is generally horrible I'd be concerned that you get nice syntax and horrible performance. Does anyone have a suggestion as to how you could do something similar across different DBs without basically killing performance or am I missing something here?
- dragonwriter 12y agoIn the read-mostly case, you can use an adjacency list, use a query with a CTE to generate a heirarchy in a materialized view which is updated when you write to the main table, and query against the materialized view. You still pay the performance cost for the CTE, but only on writes, not every read. This doesn't help in a write-heavy use case.
- rosser 12y agoRecursive CTEs work quite well in PostgreSQL, and the performance isn't at all what I'd call "horrible".
- jeffdavis 12y agoI recommend actually trying it (e.g. PostgreSQL CTE) on a problem you are trying to solve, if you haven't already. People tend to report the bad cases, and stay silent for the good cases, which can contribute to a sense of fear of the feature. It could have started from a real issue, or it could just be someone trying to solve the traveling salesman problem with fake data and then blogging when it's slow.