in reply to Optimizing Tree Hierarchies with DBD::SQLite2

Without seeing your queries, I'm not sure what slows you down. Bear in mind that SQLite will use at most one index for any given query - so you should look at the generated query plan for your queries to determine if the indices you gave are actually used.

From how I interpret the documentation, SQLite will also benefit from an early reduction of the JOIN size, that is, you need to order the JOINs of tables yourself so that the initial resultset is as small as possible.

Of course, you can likely pre-cache the browsing hierarchy and optimize it for fast reads (and slow writes) by employing some triggers on the main table and the normalized browsing relationship table, and create the flattened tree with the triggers. SQLite supports triggers, but MySQL does not, so if you're planning on porting this to other systems, beware.

What problems did you find with SQLite3 that SQLite2 doesn't have ?

  • Comment on Re: Optimizing Tree Hierarchies with DBD::SQLite2

Replies are listed 'Best First'.
Re^2: Optimizing Tree Hierarchies with DBD::SQLite2
by shlomif (Beadle) on Oct 21, 2005 at 11:19 UTC

    <<<
    Bear in mind that SQLite will use at most one index for any given query - so you should look at the generated query plan for your queries to determine if the indices you gave are actually used.
    >>>

    Hmmm... that could be a big problem.

    My queries are the following: from the ID fetch the children in the canonical categorization and the browsing categorization. I'm doing it using the primitives given by DBD::SQLite2 and Class::DBI. From the IDs of the different categories I also refer to their names.

    <<<

    What problems did you find with SQLite3 that SQLite2 doesn't have ?
    >>>

    Check this bug report.

[reply]

      I once wrote my on categorization thing in a normalized fashion and I found that once I moved queries away from Class::DBI and into SQL JOINs, the results became much faster. This of course meant adding code like the following to the classes:

      __PACKAGE__->set_sql(related_items => <<SQL)
SELECT __PRIMARY__ from __TABLE__
INNER JOIN relations ON child = __TABLE__.__PRIMARY__
WHERE relations.parent = ?
SQL

sub related_items {
  my ($self) = @_;
  return $self->search_related_items($self->id);
};

      [download]

      and much uglier JOINs if you are constructing nested sets or junctive queries. I have, for the time being, moved to a denormalized database and found writing the queries much nicer. Performance is acceptable, that is, interactive, for the single user of the system (me).

      If you'll be going the route of JOINing the relation tables, consider writing an extension to SQL::Abstract - I wrote me an (yet unreleased) extension to handle GROUP BY and HAVING clauses, and it worked well for me.

[reply]
[d/l]

In Section Seekers of Perl Wisdom