Class::DBI performance issues

[prev] [thread] [next] [Date index for 2004/12/06]

Hi,

I am trying to reduce the number of queries in situations that I use a
lot. I didn't find an answer in the documentation or in class-dbi.com

An Artist has many CDs and a CD has many Tracks. Lets say each artist
has about 10 CDs and each CD has about 15 tracks. If I want to know to
total number of seconds an artist has composed and some status info, I
would do something like

1: my $artist = Artist->retrieve( 17 );
2: print "artist ".$artist->name."\n";
3: my $secs = 0;
4: foreach my $cd ( $artist->cds ) {
5:     print "  cd ".$cd->name."\n";
6:     foreach my $track ( $cd->tracks ) {
7:        print "    track ".$track->name."\n";
8:        $secs += $track->duration_in_seconds;
9:     }
10: }

I think the following queries will be done (per line):
2 -> 1 query to get the artist (because of lazy population)
4 -> 1 query to get all cd ids
5 -> 10: one per cd (or is this data retrieved during $artist->cds()?
        Or does that depend on whether cd.name is an essential column?)
6 -> 10: get all track ids
7 -> 10*15: for each track, get the name
8 -> none, assuming track.name and track.duration_in_seconds
        are in the same column group.

Am I right, or am I overseeing things? This could be done in one query like

SELECT
    cd.name, track.name, track.duration_in_seconds
FROM
    Artist a, CD cd, Track t
WHERE
    a.id=17 AND cd.artist = a.id AND t.cd = cd.id;

I think I understood that Class::DBI uses a cache for remembering object, so

Artist->retrieve(17)->name()
Artist->retrieve(17)->country()

would only take one query. If that is the case, it would be nice to load
all objects before I run the code mentioned above. Something like

Artist->retrieve(17)->load( 'cds', 'tracks' );

I would like to implement something like that, but I think I do need a
method like sth_to_multiple_objects() to do that. Any suggestions?

Thanks, Jasper