Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

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
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?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (11)
As of 2014-07-22 12:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (113 votes), past polls