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.  


sqlite-users mailing list

Reply via email to