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

[benoit plessis]

I did build two sql results files, one using JOIN and one without and runned
directly through mysql (cat sql | mysql -B DATABASE > /dev/null) and it
turns out that simple request are quite faster:
simple: 9s (took 1min40 through DBIC) (2s without the main query)
join : 30s  (took 2min50 through DBIC) (23s without the main query)

So mysql IS slower when using joined query (but he is not responsible for
everything, since there is still 40s that are unaccounted for).

PS: As an exercice i timed a simple perl/DBI script, which prepared the =

second
query which is runned 27k times using only execute =3D> it took 37s =

(compared to 23s).
    Preparing each query and running with argument took 40s
    Preparing each query with argument 'integrated' =3D> 27s ....
Kinda funny

Matt S Trout a =E9crit :
> On Thu, May 31, 2007 at 03:37:47PM +0200, Benoit Plessis wrote:
>   =

>> In fact i think it's the generation of the SQL query that take all the
>> time or it could be the prefetching.
>>     =

>
> It's unlikely to be on the DBIC side.
>
> Try setting DBIC_TRACE=3D1 and running the query against your DB yourself.
>
> MySQL is notoriously crap at handling prefetches across many rels.
>
>   =

>> Is it a way to prepare the resultset with all arguments except the
>> search condition value, and just run the query in each loop ?
>> Like the famous prepare / execute combo in DBI ?
>>     =

>
> DBIC effectively does that - for a given query it'll re-use the same
> already-prepared $sth.
>
> Don't try and micro-optimise your usage - somebody once built almost
> exactly what you describe shortly before we implemented the current appro=
ach
> and found it provided about a 2% speed improvement.
>
>   =



-- =

Benoit Plessis                    +33 4 67 36 42 59
<benoit at plessis.info>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: benoit.vcf
Type: text/x-vcard
Size: 180 bytes
Desc: not available
Url : http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070602/9d=
bb2e46/benoit.vcf