#626 ✓resolved

Using :fields causes DataMapper to generate TONS of queries in mysql.

Reported by coryodaniel | October 30th, 2008 @ 10:19 PM

Ive gotten this bug to occur in multiple queries.


A table with three users

User.all(:fields=>[:id]) [/code]

That query generated some 90 or queries to mysql.

Here is my mysqld.log for that one query, it was truncated right before I ran it (and I ran it in IRB, its not in a loop or anything) http://pastie.org/304634

I also tried these two queries, the first one sends one query to SQL, the second sent about 30 or so (for the same 3 user table). http://pastie.org/304613

Comments and changes to this ticket

  • coryodaniel

    coryodaniel October 30th, 2008 @ 10:30 PM

    Also, I created a new merb app, solely required dm-core and added a User the did... This is my input/output from the command line.

    User.all(:fields=>[:id]) ~ SELECT id FROM users ORDER BY id ~ SELECT id FROM users WHERE (id IN (1)) ORDER BY id ~ SELECT id FROM users WHERE (id IN (1)) ORDER BY id ~ SELECT id FROM users WHERE (id IN (1)) ORDER BY id ~ SELECT id FROM users WHERE (id IN (1)) ORDER BY id

  • coryodaniel

    coryodaniel October 30th, 2008 @ 10:31 PM

    I hate the formatting on this site. The input/output: http://pastie.org/304644

  • coryodaniel

    coryodaniel October 30th, 2008 @ 11:01 PM

    Heres some additional info => http://blog.vokle.com/index.php/...

    I plan to look into this on Saturday when I have some free time. (if its not fixed by then :))

  • Bernerd Schaefer

    Bernerd Schaefer October 31st, 2008 @ 06:12 PM

    • State changed from “new” to “open”
    • Tag changed from bug, dm-core, fields, rediculousamountofqueries to bug, dm-core, fields

    This is, generally speaking, an IRB problem, though it's a bit more complicated than that.

    What's happening, is that after you say User.all, IRB will call inspect on the returned collection. Inspect then goes through each of your properties asking for the value.

    Since you didn't load the value for anything other than :id, it's going to try and run a DB query to load them... except those attributes aren't defined as :lazy => true, so when it calls "reload_attributes" it doesn't actually have any attributes it knows about to reload.

    This is obviously unexpected behavior, and a flaw in the "if not loaded, lazy_load" code.

    However, it's not actually a problem -- as long as you only access the fields you actually requested from the database:

    User.all(:fields => [:id]).collect { |user| user.id }
    # Fri, 31 Oct 2008 23:08:11 GMT ~ debug ~ SELECT `id` FROM `users` ORDER BY `id`
    # => [1, 2, 3]

    In conclusion, then, the bug is actually quite localized: when a value is not loaded, DM should check for lazy properties, but also properties it simply didn't load with.

  • coryodaniel

    coryodaniel October 31st, 2008 @ 08:36 PM

    Cool, its nice to see some light shed on this from somebody that knows the internals of DM

  • Bernerd Schaefer

    Bernerd Schaefer October 31st, 2008 @ 10:34 PM

    • State changed from “open” to “resolved”

    Okay, Cory. The fix has been pushed to dm-core's git repo (e5df3f6).

    That was one of the hairiest one-line fixes I've done in a good long while!

    The problem was, as I suspected, the assumption that all non-lazy loaded fields were already loaded. It turned out to be inside of Query.update().

    In your example, the original query was for { :fields => [:id] }. When inspect was called, it called query.update with { :fields => [:id, :name] }, but (and here's where the problem was) [:id, :name] was the same as that model's default non-lazy attributes. It therefore didn't update the query under the (false) assumption that those fields were already loaded.

    The problem should now be fixed. Thanks for digging this one up.

  • coryodaniel

    coryodaniel November 1st, 2008 @ 11:45 AM

    sweet meat, you are the cats pajamas, sir. Thanks!

  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) November 29th, 2008 @ 07:42 PM

    • Assigned user cleared.


    I had to revert the commit that Bernerd pushed because it broke too many other things within dm-core.

    However, I did find a work-around that I think is even better. In Resource#inspect we were lazy loading unloaded non-lazy attributes (wow what a mouthful). The effect was that it would generate a ton of queries. I changed the logic to not lazy load at all within Resource#inspect, and that resolved this issue.

    As Bernerd mentioned, this issue was not affecting application code and would only cause issues within IRB (merb -i).

  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) September 1st, 2009 @ 02:14 AM

    (from [e5df3f6a58ae7e591f188359e33984fbccea9cfd]) Updated Query#update's field mergining logic.

    Now Query#update only replaces the existing @fields if they are
    the defaults for the model's property set. This lets DM load
    missing non-lazy attributes excluded from the original :fields

    This resolves #626.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

People watching this ticket

Referenced by