Rails的活动记录：如何减少前往数据库的数量？ [英] Rails Active Record: How to reduce number of trips to database?

问题描述

在我的Rails项目，我有一个功能，显示用户是否有自己的Facebook上的朋友都已经在网站上。验证后，我打开他们的Facebook朋友到@fb_friends然后做到这一点：

  @ fb_friends.each办|朋友|
    friend_find = Authorization.find_by_uid_and_provider（好友[：符]，Facebook的）
    如果friend_find
      @fb_friends_on_cody＆LT;＆LT; friend_find.user_id
      @ fb_friends.delete（朋友）
    结束
  结束
 

基本上，如果发现匹配（通过授权表），我把该记录@fb_friends_on_cody。这被证明是一个非常昂贵的操作：

1. 这每个朋友访问数据库一次。所以，在我的情况下，共执行582查询也有。

2. 每个Authorization.find_by_uid_and_provider有表现不佳。我有添加索引像这样：

     add_index授权，[UID，提供者]：名称=＆GT; index_authorizations_on_uid_and_provider：独特=＆GT;真正
    

我想象很多性能增益可以通过寻址所述第一点可以看出。也许有一些办法让查询本身更有效。鸭preciate在这些方面的任何提示。

解决方案

使用Unixmonkey的指导下，我能够查询的次数减少，从582到1！这里是最后的code：

  friend_ids = @ fb_friends.map {| F | F [：符]}
  授权= Authorization.where（'提供商=？和UID IN（？）'，'Facebook的，friend_ids）
  @fb_friends_on_cody = authorizations.map {| A | {USER_ID：a.user_id，UID：a.uid}}
  @ fb_friends.reject {|！F | @ fb_friends_on_cody.map {| F | F [：UID]}包括（F [：标识符]）。？}
 

解决方案

我觉得这应该工作

  friend_ids = @ fb_friends.map（安培;：ID）
授权= Authorization.where（'提供商=？和UID IN（？）'，'Facebook的，friend_ids）
@fb_friends_on_cody = authorizations.map（安培;：USER_ID）
@ fb_friends.delete_all（'身份证在（？）'，@fb_friends_on_cody）
 

In my rails project, I have a feature that shows users whether any of their facebook friends are already on the site. After authenticating, I load their facebook friends into @fb_friends and then do this:

  @fb_friends.each do |friend|
    friend_find = Authorization.find_by_uid_and_provider(friend[:identifier], 'facebook')
    if friend_find
      @fb_friends_on_cody << friend_find.user_id
      @fb_friends.delete(friend)
    end 
  end

Basically, if a match is found (via the Authorization table), I push that record to @fb_friends_on_cody. This proves to be a very expensive operation:

1. This hits the database once per friend. So in my case there are a total of 582 queries executed.

2. Each Authorization.find_by_uid_and_provider has poor performance. I have add an index like so:

   add_index "authorizations", ["uid", "provider"], :name => "index_authorizations_on_uid_and_provider", :unique => true
   

I imagine a lot of performance gains can be seen by addressing the first point. And perhaps there are some ways to make the query itself more efficient. Appreciate any tips on these fronts.

Solution

Using Unixmonkey's guidance, I was able to cut down the number of queries from 582 to 1! Here's the final code:

  friend_ids = @fb_friends.map{|f| f[:identifier] }
  authorizations = Authorization.where('provider = ? AND uid IN (?)','facebook',friend_ids)
  @fb_friends_on_cody = authorizations.map{ |a| { user_id: a.user_id, uid: a.uid }}
  @fb_friends.reject!{|f| @fb_friends_on_cody.map{|f| f[:uid]}.include?(f[:identifier]) } 

解决方案

I think this should work

friend_ids = @fb_friends.map(&:id)
authorizations = Authorization.where('provider = ? AND uid IN (?)','facebook',friend_ids)
@fb_friends_on_cody = authorizations.map(&:user_id)
@fb_friends.delete_all('id in (?)', @fb_friends_on_cody)

这篇关于Rails的活动记录：如何减少前往数据库的数量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！