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

Matt S Trout dbix-class at trout.me.uk
Tue Dec 20 01:24:06 GMT 2005

On Tue, Dec 20, 2005 at 11:41:29AM +1100, Rick Welykochy wrote:
> The SQL behaviour is:
> 1. SELECT id FROM units WHERE id = 86
> 2. SELECT name FROM units WHERE id =86
> Unfortunately, this will not reduce the total number of SQL statements
> by very much. Here is what I am dealing (just gathered some more stats
> today):
> Sql    Objects   Seconds
> 4375   49279      141.328
> 4367   54147      143.885
> 4246   1480281   1964.542
> 3295   38589      110.198
> 3071   30974       28.225
> 2971   64833       27.455
> But! I will do everything possible to reduce the the SQL counts above.
> (The above table lists the worst offenders first).
> So,
> 1. review and re-specify the Essential columns
>    ... and I am wondering if it might be best to simply load all
>    non-blob columns and be done with it
> 2. manually re-write all joins were possible

You could also try Class::DBI::Sweet and use the 'prefetch' option to
load has_a queries in a single query, plus (selectively) the resultset
cache to maintain entire ->search()es in memory (and a shared cache).

Of course, if you're refactoring substantially, it's but a short step to
just port the code to DBIx::Class which has the same features and much less
perl overhead, and not *so* much longer a step to Rose::DB::Object which
is rather more different but faster still (speed here in terms of perl
overhead; the SQL optimisation achievable with each is probably fairly
similar provided we're comparing Sweet and not plain Class::DBI).

     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +

More information about the ClassDBI mailing list