#730 ✓resolved
zeijeigo

multiple sql queries when strategic loading empty associations [PATCH]

Reported by zeijeigo | December 29th, 2008 @ 08:16 PM

When referencing an association of an object from a collection, sibling objects that don't have anything associated with them are not marked in any way. This results in a significant performance issue. In the worst possible case, for a collection of size N and each element having 0 associated objects, a total of 2N sql queries will be made (N asking for N objects, and N asking for a single object).

For an example look at the following log:

http://ruby.pastebin.com/f1021fb1c

You can see 2 extra sql queries for each Item such that item.reviews.size == 0

Comments and changes to this ticket

  • zeijeigo

    zeijeigo December 29th, 2008 @ 09:36 PM

    I've only started with Datamapper today, but I think this simple patch to dm-core/associations/relationship.rb (around line 95) should fix the problem.

      grouped_collection = {}
    + query_values.each do |key|
    +   parent_obj = parent_identity_map[key]
    +   grouped_collection[parent_obj] = []
    + end
      collection.each do |resource|
      child_value = child_key.get(resource)
        parent_obj = parent_identity_map[child_value]
    -   grouped_collection[parent_obj] ||= []
        grouped_collection[parent_obj] << resource
      end
    
    
  • zeijeigo

    zeijeigo December 29th, 2008 @ 10:33 PM

    Or perhaps query_values.each should be bind_values.each, since this is the one used in actualy query. I'm not able to make out the difference between the two.

  • zeijeigo

    zeijeigo December 29th, 2008 @ 10:57 PM

    grouped_collection = {}

    + bind_values.each do |key|
    +   parent_obj = parent_identity_map[key]
    +   grouped_collection[parent_obj] = []
    + end
      collection.each do |resource|
        child_value = child_key.get(resource)
        parent_obj = parent_identity_map[child_value]
    -   grouped_collection[parent_obj] ||= []
        grouped_collection[parent_obj] << resource
      end
    
    
  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) January 8th, 2009 @ 05:18 AM

    • State changed from “unconfirmed” to “accepted”
    • Assigned user set to “Dan Kubb (dkubb)”
  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) May 28th, 2009 @ 01:53 AM

    • State changed from “accepted” to “resolved”

    This issue should be resolved in the dm-core/next branch.

    I explicitly initialize associations in all siblings, even when there the association is empty. This prevents extra queries from being executed to lazy-load the non-existent records.

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

Attachments

Pages