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

[Benoit Plessis]

I've double checked the generated query using explain and all my DB 
knowledge and that really can't be a problem (when removing the where 
filter the full resultset came in less than one tenth of seconds and
the where is on an indexed field).


Lee Standen a écrit :
> 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/