On Thu, 16 Oct 2014 09:05:51 +1100 Michael Falconer <michael.j.falco...@gmail.com> wrote:
> we just wonder if there is a better way to perform this search in > SQL. Is there a general technique which is superior either in speed, > efficiency or load bearing contexts? The simple answer is No, because SQL is a specification, not an implementation. Different systems implement it differently and therefore perform differently. Any "general technique" affecting performance belongs to the implementation per se, not the SQL, which is a logical construction. SQLite itself has changed its performance characteristics over the course of its development. For that reason, any question of performance has to be answered in terms of a particular implementation, even its specific version, and the OS and hardware it's running on. That said, there is reason to suppose that a single-table design would be more efficient. If the queries can be expressed with recursion and the indexes lead to efficient searches, the query optimizer has less work to do. It has fewer permutations to consider, and the search is apt to touch fewer pages. The analysis tools of the system you're using should be able to confirm or deny that supposition. I would remind your fellows, though, that efficiency is not all. The utility of a model (that is, the database design) is measured by how well, to its purpose, it describes the real world. Any model that must be changed as that reality changes in predictable ways isn't really much of a model; it turns the designer into a component of the model. By recognizing all trees as one, you generalize your model and make it do work you are now doing yourself (manually, or in application logic). By any measure, that makes it a better model. HTH. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
