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

[rahed]

Benoit Plessis <benoit at plessis.info> writes:

> 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.
>

Just a few days ago I also came across the similar behaviour. I've got
four tables a,b,c,d. a-b and b-c has a belongs_to relationship and c-d
has a might_have relationship. db is sqlite.

I search with prefetch. WHERE clause has a condition in the c
table. Data is returned in more than 10 sec. But if I apply the same select
from the sqlite promt the response is immediate (less than a second).

I couldn't find why it takes so much time. The cause is the WHERE clause
refering to a table (c or d) with left join. I fixed it with moving the
WHERE condition to the a table and response is adequate, say one second.

Don't know what should I focus on.

-- 
Radek