4 ms·
I have never had great performance with recursive queries in SQL Server, does postgre perform better in this respect? I have seen a few posts on HN regarding r
by hobs 12y ago
I have never had great performance with recursive queries in SQL Server, does postgre perform better in this respect?
I have seen a few posts on HN regarding recursive CTEs recently and I shy away from them because it seems like an additional invocation for each different recursive lookup and a lot of the queries can be rewritten and potentially optimized using more traditional approaches.
- fleetfox 12y agoTake a look at this: http://cramer.io/2010/05/30/scaling-threaded-comments-on-django-at-disqus/ http://cramer.io/2010/05/30/scaling-threaded-comments-on-dja...
- hobs 12y agoTo summarize for anyone reading this thread, and thank you fleetfox for posting this: "Yes, you heard that right, the SQL statement is 5x more performant at the database level alone." I will have to do more testing and see what I can accomplish.
- aragot 12y agoI've had positive experience with Postgresql, but I haven't personnaly witnessed how it behaves in production. In my opinion there aren't good alternatives to a recursive query: - Send one request for each level of hierarchy? Worst of all. - Stored procedure? I don't think it would be optimised compared to a recursive query. See also: http://stackoverflow.com/questions/5861272/postgresql-with-recursive-performance http://stackoverflow.com/questions/5861272/postgresql-with-r...
- ximeng 12y agoChanging data structure is one approach: http://stackoverflow.com/questions/935098/database-structure-for-tree-data-structure?lq=1 http://stackoverflow.com/questions/935098/database-structure...
- dspillett 12y agoI've generally seen recursive queries in SQL Server perform as well as I expected or sometimes slightly better, so I'm happy to use them (rather than a stored procedure for example which have, on the occasions I've done proper tests, turned out to be less well performing if only by a little) where I need to read from structures that require it. Where I have control of the design though, I still prefer to create structures that don't* require recursive queries*, even considering this means a little extra logic on insert/update/delete. Depending on circumstance (and for these examples assuming a tree or forest, not a more general graph) this structural difference is either storing the full ID path of each node or a matrix of "node Z is under node Y by one level, node Z is under node X by two levels, node Y is under node X by one level, ...), instead of (or as well as) the naive tree structure. Well written operations on these structures perform better than a recursive query, sometimes significantly so, but of course it is a trade-off as you have to accept the extra space (and potentially RAM) needed and extra processing on node changes, additions, deletes, & moves.
- crescentfresh 12y agoUgh, CTE's to construct a tree/graph in SQL Server brings back terrible memories. I'd estimate 25% of our time at one point was spent building aggressive caching in front of it due to the awful performance as more nodes were created. On a different product now (using PG) and guess what has come up? We need tree nodes! My first thought: anything but CTEs. ltree, materialized paths, neo4j, anything but CTEs. They've just burned me so badly before I'm too afraid to try them in PG.
- jimktrains2 12y agoI worked at a place that had recursive ACLs. We used CTEs and it was pretty darn quick. We only had on the order of a million records, though, so ymmv.
- hobs 12y agoHow quick is quick? (ms?) What kind of hardware were you running? (all data in ram?) Just wondering, still interested in the topic!