Robot Has No Heart

Xavier Shay blogs here

A robot that does not have a heart

Counting ActiveRecord associations: count, size or length?

Short answer: size. Here’s why.

length will fall through to the underlying array, which will force a load of the association

1
2
3
>> user.posts.length
  Post Load (0.620579)   SELECT * FROM posts WHERE (posts.user_id = 1321) 
=> 162

This is bad. You loaded 162 objects into memory, just to count them. The DB can do this for us! That’s what count does.

1
2
3
>> user.posts.count
  SQL (0.060506)   SELECT count(*) AS count_all FROM posts WHERE (posts.user_id = 1321) 
=> 162

Now we’re on to something. The problem is, count will always issue a count to the DB, which is kind of redundant if you’ve already loaded the association. That’s were size comes in. It’s got smarts. Observe!

1
2
3
4
5
6
7
>> User.find(1321).posts.size
  User Load (0.003610)   SELECT * FROM users WHERE (users.id = 1321) 
  SQL (0.000544)   SELECT count(*) AS count_all FROM posts WHERE (posts.user_id = 1321) 
=> 162
>> User.find(1321, :include => :posts).posts.size 
  User Load Including Associations (0.124950)   SELECT ...
=> 162

Notice it uses count, but if the association is already loaded (i.e. we already know how many objects there are), it uses length, for optimum DB usage.

But know that’s not all. There’s always more. If you also store the number of posts on the user object, as is common for performance reasons, size will use that also. Just make sure the column is named _association__count (i.e. posts_count).

1
2
3
4
5
>> User.columns.collect(&:name).include?("posts_count")
=> true
>> User.find(1321).posts.size
  User Load (0.003869)   SELECT * FROM users WHERE (users.id = 1321) 
=> 162

The bad news

So now you’re all excited, I better tell you why this is only fantastic until you start using has_many :through.

Now, the situation is slightly different between 1.2.x (r4605) and edge (r7639), so I’ll start with stable. Now, they may look the same but a normal has_many association and one with the :through option are actually implememted by two entirely separate classes under the hood. And it so happens that the has_many :through version kind of, well, doesn’t have quite the same smarts. It loads up the association just as length does (then falls through to Array#size). Edge is sharp enough to use a count, but still doesn’t know about any caches you may be using. This was commited in r7237, so it’s pretty easy to patch in to stable. Or you can use this extension (on either branch – here is the trac ticket): This patch was added to edge in 7692

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
module CoreExtensions::HasManyThroughAssociation
  def size
    return @owner.send(:read_attribute, cached_counter_attribute_name) if has_cached_counter?
    return @target.size if loaded?
    return count
  end

  def has_cached_counter?
    @owner.attribute_present?(cached_counter_attribute_name)
  end

  def cached_counter_attribute_name
    "#{@reflection.name}_count"
  end
end

ActiveRecord::Associations::HasManyThroughAssociation.send(:include, CoreExtensions::HasManyThroughAssociation)

How it doesn’t work

1
user.posts.find(:all, :conditions => ["reply_count > ?", 50]).size

size normally works because assocations use a proxy – when I call user.posts it won’t actually load any posts until I call a method that requires them. So user.posts.size can work without ever loading the posts because they aren’t required for the operation. The above code won’t work well because find does not use a proxy – it will straight away load the requested posts from the DB, without size getting a chance to send a COUNT instead. You may be better off moving this finder logic into an association so that size will work as expected. This also has the benefit that if you decide to add a counter cache later on you won’t have to change any code to use it.

1
has_many :popular_posts, :class_name => "Post", :foreign_key => "post_id", :conditions => ["reply_count > ?", 50]

So use size when counting associations unless you have a good reason not to. Most importantly thought, ensure you’re watching your development log so to be aware what SQL your app is generating.

UPDATE: Added link to my patch on trac

UPDATE 2: ... which is now closed, see r7692

  1. Caleb says:

    Very informative post.

  2. Vic says:

    thx man! ^)

  3. Bilal Ahmed says:

    Good post. thanks

  4. ktkaushik says:

    Thanks a lot, that was very informative.

  5. Wojciech Kruszewski says:

    This post dates back to 2007. It's late 2012 and I still refer to it every once in a while to make sure I don't confuse length and size (-:

  6. polo says:

    http://www.burberry-factory.net/
    http://www.shophandbagsonline.com/
    http://www.official-coachoutlet.com/
    http://www.barbour-factory.com/
    http://www.burberry-outlet2014.com/
    http://www.guccibags.us.com/
    http://www.marcjacobsonsale.com/
    http://www.mcmworldwide.ca/
    http://www.guccishoes-uk.com/
    http://www.kate-spades.com/
    http://www.louisvuittonas.com/
    http://www.lv-guccishoesfactory.com/
    http://www.official-mkoutlets.com/
    http://www.official-pradaoutlet.com/
    http://www.michael-korsusa.net/
    http://www.north-facesoutlet.com/
    http://www.moncler-clearance.com/
    http://www.north-faceclearance.com/
    http://www.clothes-mall.com/
    http://www.polo-outlets.com/
    http://www.ralphlauren.so/
    http://www.ralphlaurentshirts.com/
    http://www.ferragamos.in.net/
    http://www.longchampsoutlet.com/
    http://www.abercrombiee.com/
    http://www.barbour-jacketsoutlet.com/
    http://www.michael--korsonline.com/
    http://www.thenorthface.so/
    http://www.beatsbydreoutlet.net/
    http://www.tommyhilfiger.in.net/
    http://www.ralphslauren.co.uk/
    http://www.michaelkors.so/
    http://www.oakleyssunglassoutlet.com/
    http://www.warm-boots.com/
    http://www.woolrich-clearance.com/
    http://www.nike-jordanshoes.com/
    http://www.monsterbeatsbydres.net/
    http://www.canada-gooser.com/
    http://www.bestcustomsonline.com/
    http://coach.mischristmas.com/
    http://www.coach-blackfriday2014.com/
    http://www.coachccoachoutlet.com/
    http://www.coach-clearance.com/
    http://www.coach-factories.net/
    http://www.coach-factorysoutlet.com/
    http://www.coachlosangeles.com/
    http://www.coachoutletstates.com/
    http://www.coach-pursesoutlets.com/
    http://www.hermes-outletonline.com/
    http://www.misblackfriday.com/
    http://www.mischristmas.com/
    http://www.mmoncler-outlet.com/
    http://www.newoutletonlinemall.com/
    http://www.ralphlaurenepolo.com/
    http://www.zxcoachoutlet.com/
    http://michaelkorsoutlet.mischristmas.com/
    http://mcmbackpack.mischristmas.com/
    http://monsterbeats.mischristmas.com/
    http://northfaceoutlet.mischristmas.com/
    http://mk.misblackfriday.com/
    http://coachoutlet.misblackfriday.com/
    http://coachfactory.misblackfriday.com/
    http://uggaustralia.misblackfriday.com/
    http://coachpurses.misblackfriday.com/
    http://coachusa.misblackfriday.com/
    http://coach.misblackfriday.com/
    http://michaelkorss.misblackfriday.com/
    http://michaelkors.misblackfriday.com/
    http://airmax.misblackfriday.com/
    http://michael-kors.misblackfriday.com/
    https://twitter.com/CoachOutlet2014
    https://www.facebook.com/coachoutletstoreonline
    https://www.facebook.com/ralphlaurenoutletonline

Post a comment


(lesstile enabled - surround code blocks with ---)

A pretty flower Another pretty flower