[CDBI] How well does Class::DBI scale?

Perrin Harkins perrin at elem.com
Sun Dec 18 16:42:27 GMT 2005

On Sun, 2005-12-18 at 14:29 +1100, Rick Welykochy wrote:
> That said, the code I have inherited is tight, compact and the programmer
> really knew how to use Class::DBI well. There is nary an SQL statement in
> sight

That's not a good sign to me.  O/R mappers are only good at generating
fairly simple queries.  Doing things all in perl without any hand-coded
SQL usually means no one has looked at the performance yet.

> My job is to optimise these, i.e. unravel the goodness that Class::DBI
> objects are doing, write several (LARGE) SQL statements to get the
> same result sets, and then map those back into Class::DBI using the
> construct() method.

Good plan.

> My question to the list: has anyone else run into these problems (too
> many SQLs) and if so, have they found an automatable solution?

It is possible that switching to Rose::DB::Object would help, since the
benchmarks for it are better than any other perl O/R mapper, but writing
straight DBI as you are now is still much faster than that.  I think
you're doing it the right way.

> 2. I have reverse engineered Class::DBI and observed the caching behaviour
>     and am not too happy with it. For no apparent reason, sometimes CDBI
>     goes back to the database when a perfectly good and completely
>     "__flesh()'d" object is already in the database. This is especially
>     annoying in sth_to_objects() when never seems to cache things very well.

Some of this has to do with supporting use of database functions on
columns, but I agree, it's lame.

The other source of slowness when working with large result sets in
Class::DBI is the glacial speed of accessor methods.  You may want to
investigate some of the speed hacks on perlmonks.org or look at the code
yourself.  Obviously tuning the SQL is a bigger issue though.

- Perrin

More information about the ClassDBI mailing list