|Syntactic Confectionery Delight|
Re^2: Database Accessor Classesby ryantate (Friar)
|on Mar 06, 2006 at 23:47 UTC||Need Help??|
Class::DBI is not a very efficient database user
More specifically, CDBI can make a lot of database calls in cases where the programmer is trying to write a fast-and-dirty prototype, does not want to write custom SQL or is naive on how CDBI works.
In particular, CDBI does not do three-way joins to load objects related through many-to-many relationships. Such operations are inefficient in CDBI without custom SQL.
For example: If an actor has_many movies and a movie has_many actors, via a jump table actor_movie, and you do $actor->movies, this is one SQL execution in the database. But that only gets you the id of each movie. If you want to get the title for each of those movies, that's one SQL execution *per movie object*, which can be many db hits.
Other ORMs like Ruby's ActiveRecord can be made to easily do three-way joins in this situation, which cuts the SQL execution back to just one statement and one resultset.
CDBI internals are being redone and easy three-way-joins are supposed to happen eventually via a plugin, but as far as I know this has not yet happened. There is Class::DBI::Sweet, but even its authors consider it far enough from mainstream CDBI to be too funky to have a big future.
They advocate using DBIx::Class, but the docs there just don't do it for me yet. Not clear and simple enough -- module soup.