hi everyone,
I'm knew to DBIx::Class (discovered with the Catalyst framework) but
i think it's terrible !
Anyway i encountered two gotchas while using the thing and here they are:
1/ Relationships.
Let's say we have 3 tables and two relation:
table1.rel1 is a *might_have* on table2
table2.rel2 is a *belongs_to* on table3
when doing someting like that:
$schema->resultset('Table1') -> search (undef, { prefetch => {
'table2' => 'table3' } });
the generated SQL code will be something like that:
/FROM table1 LEFT JOIN table2 ON (table1.f1 = table2.f1) JOIN table3
ON (table2.f2 = table3.f2)
/The problem (at least with MySQL 5) is the following: If they are
entries in table1 with no corresponding
entries in table2 (witch is the purpose of *might_have* iiuc) the second
JOIN on table3 will remove thoses
entries from the resulting set.
Eg:
SELECT COUNT(*) FROM table1 LEFT JOIN table2 => 3000 entries
SELECT COUNT(*) FROM table2 JOIN table3 => 300 entries
then SELECT COUNT(*) FROM table1 LEFT JOIN table2 JOIN table3
will give 300 replies
2/ Performance weirdness
When i understood the previous fact i cuted my big search with prefetch
on 10 tables as far as i can prefetch
without 'losing' datas. Resulting in 5/6 more SQL queries for each loop.
=> this took 1min40s to process the whole dataset.
my $rs = $erp->resultset('Table1')->search(undef,
{ prefetch => ['rel1', { rel2 => [ 'rel3',
'rel4', 'rel5', 'rel6', ], }, ], });
while (my $e = $rs -> next) {
....;
foreach my $s ($e->rel2->rel6()) {
$datas = $s->rel7->nom();
if (my $b = $s->rel8()) {
$datas = ( $b->rel9->nom() , $b->rel10->nom(),
$b->rel11->nom() );
}
}
}
}
So i tried to optimize this by creating another search+prefetch
resultset on the last 5 tables which is build
once per loop using result for the first set. There is the funny thing,
it is slower than the first case by 70 sec (2min50s).
my $rs = $erp->resultset('Table1')->search(undef,
{ prefetch => ['rel1', { rel2 => [ 'rel3',
'rel4', 'rel5', 'rel6', ], }, ], });
while (my $e = $rs -> next) {
....;
my $rs2 = $erp->resultset('Table7')->search({ "me.num" =>
$e->rel2->num_table7() },
{ prefetch => ['rel7', { rel8 => [ 'rel9',
'rel10', 'rel11' ] },]});
while (my $licence = $rs -> next()) {
$datas = $s->rel7->nom();
if (my $b = $s->rel8()) {
$datas = ( $b->rel9->nom() , $b->rel10->nom(),
$b->rel11->nom() );
}
}
}
}
I'm using perl and DBIx::Class as of debian/etch release (5.8.8-7 and
0.07003-1)
If u need mode informations feel free to ask.
Regards,
benoit
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
