#642 ✓not-applicable
Alexander Staubo

has n loads lots of redundant copies

Reported by Alexander Staubo | November 6th, 2008 @ 11:01 AM

Simple model:


class Component
  include DataMapper::Resource
  has n, :alias
end

Sample Merb interpreter + SQL output:


>> cs = Component.all; nil
=> nil
>> cs.length
=> 11
>> cs.each { |c| puts c.id; x = c.aliases.to_a }; nil
 ~ SELECT "id", "created_at", "updated_at", "title" FROM "components" ORDER BY "id"
1
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (4, 10, 1, 7, 6, 3, 9, 2, 8, 5, 11)) ORDER BY "id"
2
3
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (4, 10, 1, 7, 6, 3, 9, 2, 8, 5, 11)) ORDER BY "id"
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (3)) ORDER BY "id"
4
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (4, 10, 1, 7, 6, 3, 9, 2, 8, 5, 11)) ORDER BY "id"
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (4)) ORDER BY "id"
5
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (4, 10, 1, 7, 6, 3, 9, 2, 8, 5, 11)) ORDER BY "id"
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (5)) ORDER BY "id"
6
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (4, 10, 1, 7, 6, 3, 9, 2, 8, 5, 11)) ORDER BY "id"
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (6)) ORDER BY "id"
7
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (4, 10, 1, 7, 6, 3, 9, 2, 8, 5, 11)) ORDER BY "id"
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (7)) ORDER BY "id"
8
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (4, 10, 1, 7, 6, 3, 9, 2, 8, 5, 11)) ORDER BY "id"
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (8)) ORDER BY "id"
9
10
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (4, 10, 1, 7, 6, 3, 9, 2, 8, 5, 11)) ORDER BY "id"
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (10)) ORDER BY "id"
11
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (4, 10, 1, 7, 6, 3, 9, 2, 8, 5, 11)) ORDER BY "id"
 ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (11)) ORDER BY "id"
=> nil

Wow! Why isn't the identity map or the eager association loading catching this? And why two queries for each access, one of which is covered by the previous one?

Comments and changes to this ticket

  • Alexander Staubo

    Alexander Staubo November 6th, 2008 @ 11:27 AM

    I was not using the identity map. Here's with the identity map:

    
    >> DataMapper.repository { cs = Component.all; cs.each { |c| puts c.id; c.aliases.to_a } }; nil
     ~ SELECT "id", "created_at", "updated_at", "title" FROM "components" ORDER BY "id"
    1
     ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (4, 10, 1, 7, 6, 3, 9, 2, 8, 5, 11)) ORDER BY "id"
    2
    3
     ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (10, 11)) ORDER BY "id"
    4
     ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (10, 11)) ORDER BY "id"
    5
     ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (10, 11)) ORDER BY "id"
    6
     ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (10, 11)) ORDER BY "id"
    7
     ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (10, 11)) ORDER BY "id"
    8
     ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (10, 11)) ORDER BY "id"
    9
    10
     ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (10, 11)) ORDER BY "id"
    11
     ~ SELECT "id", "created_at", "updated_at", "title", "component_id", "producer_id" FROM "component_aliases" WHERE ("component_id" IN (10, 11)) ORDER BY "id"
    => nil
    
  • Alexander Staubo

    Alexander Staubo November 27th, 2008 @ 08:21 AM

    • Tag changed from 0.9.6, associations, dm-core, eager_loading to 0.9.6, associations, critical, dm-core, eager_loading
    • Assigned user cleared.

    Marking as critical as this is a performance show-stopper.

  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) November 28th, 2008 @ 02:09 AM

    • State changed from “new” to “open”
    • Assigned user set to “Dan Kubb (dkubb)”

    Alexander, can you create a stand-alone script that demonstrates this problem? It is a good idea to load it up with some fake test data, as well as turn on logging so that the SQL queries can be seen easily. Here's an example of a stand-alone script I created:

    http://gist.github.com/23701

    Also please note that SEL only works within a repository block, so be sure to wrap the loop within one and test with that as well.

  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) December 4th, 2008 @ 03:39 AM

    • State changed from “open” to “hold”

    Marking this ticket as on-hold since it is still awaiting Alexander's reply.

  • Dan Kubb (dkubb)
  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) January 8th, 2009 @ 04:56 AM

    • State changed from “hold” to “not-applicable”

    The model in this example does not have a PK, which is likely the cause of the issue.

    Since the reporter has not provided any further requested information I am closing this ticket.

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

Pages