Re^2: Optimizing Tree Hierarchies with DBD::SQLite2

<<<
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.

Comment on Re^2: Optimizing Tree Hierarchies with DBD::SQLite2

Replies are listed 'Best First'.
Re^3: Optimizing Tree Hierarchies with DBD::SQLite2 by Corion (Patriarch) on Oct 21, 2005 at 11:35 UTC
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]

Replies are listed 'Best First'.

Re^3: Optimizing Tree Hierarchies with DBD::SQLite2
by Corion (Patriarch) on Oct 21, 2005 at 11:35 UTC

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]