[Dbix-class] Weird RelationShip => SQL behavior and Performance Issue

[Lee Standen]

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/dbix-class@lists.rawmode.org/