Gernot,
The only real hints I can give relate to understanding the various
syntactical invocations of DataMapper queries and how they generate SQL
differently between them.
For complex joins I stay away from the Set-like syntax, favor "string paths"
like User.all('address.state' => "CA", 'acount.payments.amount.gt' => 2.0), and
avoid using query chaining like Model.first.relationship1('property1' =>
'foo').relationship2.property2 that cross large (intermediate) data sets
because (usually) each "hop" materializes the collection individually (instead
of using joins), and as you've seen those GROUP BYs will literally murder your
performance.
For anything that needs tuned performance on large datasets, you're going to
have to accept that you'll always know more/better about your DB and how to
navigate it than any ORM. That's just the reality.
Ted Han (knowtheory) and I are collaborating on fixing joins at
http://github.com/jpr5/dm-do-adapter/tree/fix_joins.
Context: This was brought on by a recent experience trying to upgrade a large
project from DM 0.10x to 1.0. We use the "string path" invocations a lot, but
got hung up (again) on a long-standing bug in the join generation logic related
to bad table aliasing (aliasing: http://gist.github.com/276940, broken joins
dating back to 0.9.x: http://gist.github.com/277406#file_3_fail_cases.txt).
For string-path queries, my code fixes the join aliasing/scoping by attaching
each "link chain" to its associated query comparison (for the SQL generation
phase to connect a given WHERE property condition to its correctly scoped table
alias), but because DM has such a diverse range of invocations, I quickly
discovered there were other paths that bypassed the standard query condition
evaluation logic that I had hooked (one relates to Relationship#query_for and
"chained relationship" syntax). So it's a partial fix at the moment.
I talked with Ted last night about maybe just living with the partial win for
now, doing what was necessary to get it into production shape and merged into
mainline, while we continue further down this path. We're going to work on it
this weekend.
cheers,
--jordan
On Sep 2, 2010, at 10:03 PM, Gernot wrote:
> Hi Jordan
>
> On 2 Sep., 04:05, Jordan Ritter <[email protected]> wrote:
>> Gernot,
>>
>> While there may be syntactically better ways to construct your
>> particular DM queries, I don't necessarily think you're going about it all
>> wrong. The truth is that the current logic behind generating JOINs for many
>> sophisticated (multi-table) join scenarios is plain broken.
>>
>
> Any hints how I could use better syntax?
>
>> The good news is that it just so happens there's a recent/current discussion
>> + forked branch or two focused on refactoring the Query/JOIN construction
>> logic. The bad news is it probably won't be production ready any time soon.
>> YMMV, but I've personally resigned myself to using raw SQL in these sorts
>> of performance-is-important scenarios.
>>
>
> Could you give me a hint where I can find those forks?
>
> Thank you ver much for your opinions and suggestions.
> Gernot
>
> --
> You received this message because you are subscribed to the Google Groups
> "DataMapper" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/datamapper?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"DataMapper" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/datamapper?hl=en.
