Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

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

by shlomif (Beadle)
on Oct 21, 2005 at 11:19 UTC ( #501959=note: print w/ replies, xml ) Need Help??

in reply to Re: Optimizing Tree Hierarchies with DBD::SQLite2
in thread 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 (Pope) 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); };

    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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://501959]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (2)
As of 2015-10-10 19:00 GMT
Find Nodes?
    Voting Booth?

    Does Humor Belong in Programming?

    Results (257 votes), past polls