On Sat, 21 Sep 2013 00:28:15 +0100 Simon Slavin <[email protected]> wrote:
> Your extremely long SELECT with its sixteen COALESCEs and eight LEFT > OUTER JOINs suggests that you have a schema which doesn't really suit > the 'shape' of your data. Another hint pointing in the same > direction is numbered database names. I think you might simplify > your SELECT, reduce your data footprint, and speed up your query by > rethinking your schema. If I could add to that, nearly all the rigamarole stems from the parent/child relationship table. These are nearly always wrong, and I see no implication of such a relationship in the schema provided. So, the first question is whether or not that table is justified. If so, the second question is whether or not 8 is a magic number, because the query will not reveal the 9th level of nesting. To process a recursive query, write the level 0 results to a temporary table, and join that table back to the original query to insert the next level. Repeat as necessary until no rows are returned, then select from the temporary table. To ensure correctness, use a transaction. --jkl _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
