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

Rick Welykochy rick at praxis.com.au
Sun Dec 18 23:05:00 GMT 2005


Bill Moseley wrote:

> On Sun, Dec 18, 2005 at 02:29:16PM +1100, Rick Welykochy wrote:
> 
>>3. I have played with columns( Essential => all cols ) but observe very 
>>little
>>   improvement (i.e. reduction) in the number of SQLs.
> 
> 
> Are you saying that after a search that fetching a column from a CDBI
> object triggers another select from the database for that column even
> if the column is in Essential?

Not really. First off, the SQL logging is vast, i.e. thousands of
SQLs in some instances. I have yet to delve deep down into the quagmire
to see what is happeneing exactly, but I fear the %Live_Objects mechanism
is flakey.

Example:

Let's say we have one hundred rows the refer to foreign key 86
in the units table. The units table has a (id,name) pair. I will
see one hundred SQL requests for (id=86,name) from the database!
I find this strange if the corresponding Unit object has already
been _flesh()'d out once.

Note that in the case of this Unit stuff, the only Essential
column is the primary key. Would this explain the behaviour?

I can either spend more of my client's time and money digging deeper
into why this is happening in CDBI, or move on and optimise. I have
chosen the latter, since I need to do that anyway.


> Take a look at CDBI::Sweet.  There's a few things that might help.
> With Sweet you can specify joins in your query.  And with Sweet you can
> also define custom SQL which is helpful if you are doing any aggregate
> functions.
> 
> If you have not already done this, you can create views and define new
> CDBI classes for those.  That can help reduce the number of trips to
> the database.
'
Thanks. Will have a look.


cheers
rick

-- 
_________________________________
Rick Welykochy || Praxis Services

Virtue is more to be feared than vice, because its excesses are not subject
to the regulation of conscience.
      -- Adam Smith




More information about the ClassDBI mailing list