With regards to performance, I'd be looking at whether it's the database
or the code which is causing the delay...
If you don't have appropriate indexing, or the query doesn't work well
on your particular database, then it's going to be painfully slow no
matter how well DBIC handles it.
Benoit Plessis wrote:
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]/
_______________________________________________
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]/
