This isn't a question as much as an observation that might be interesting for others, too, though I'd share.
Having a table building a tree by each item referencing its parent: // Table Items CREATE TABLE [Items] ( [iRowID] INTEGER PRIMARY KEY, [iParentRowID] integer NULL, ... // some more columns, nothing special ); // Indices on Table Items CREATE INDEX [dbidx_Items_parent] ON [Items] ([iParentRowID]); I have two pieces of code that recurse through all nodes (starting at some root node), and I was startled by the immense performance difference. The "better" version - using prepared statements, querying only a subset of information - was more than 10 times slower than the dumb brute-force one that built a giant string diagnostic on the way. I tacked the difference down to the order of queries, in pseudocode: Recurse_Fast($row) := SELECT * WHERE rowid=$row for each (childid in (SELECT rowID WHERE parentRowID=$row)) Recurse(childid) Recurse_Slow($row) SELECT * WHERE rowid=$row for each (childid in (SELECT rowID WHERE parentRowID=$row)) children.push_back(childid) for each (childid in children) Recurse(childid) i.e. the slow version first gets all the child node id's before recursing into them. (To note: it is not the additional vector allocation, the significant time is spent in sqlite3_step.) In both cases, we have the same number of queries, the number of child nodes per node is typically small, and the performance difference is very resistant to changes such as changing the selected subset of columns, using prepared statements etc. I presume it is a caching issue, simply because of the significant gap. ----- As said, no real question, but if someone has some insights on the why, I'd be interested. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
