Lets say I have models
Accounts(:id,:name,:type) has_many :items
Items(:id,:account_id;name) belong_to :account
I want all the items where the account.type = "Cost"
I could do:
income = Item.joins(:account).where(''account.type'' =>
"Income")
Or, I could do:
income = Item.where(:account_id => Account.where(:type =>
"Income").map(&:id))
While there are several others queries where this is used, the joins
approach takes about 20ms in activerecord. The in approach (the inner
query produces an array of ids) takes about 10ms.
The Items table is expected to be large, the Accounts table will be
small.
Numbers tell me that my array approach is better, but then I have not
seen many use that approach.
Any other suggestions? Comments?
Steve
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
AppleII717 wrote in post #959561:> I could do: > income = Item.joins(:account).where(''account.type'' => "Income") > > Or, I could do: > > income = Item.where(:account_id => Account.where(:type => > "Income").map(&:id)) > > While there are several others queries where this is used, the joins > approach takes about 20ms in activerecord. The in approach (the inner > query produces an array of ids) takes about 10ms. > > Numbers tell me that my array approach is better, but then I have not > seen many use that approach.I don''t know how extensive (i.e. how many scenarios) your benchmarking involved, but if it was only one contrived test that may not be enough. For example what effect does an increasing number of values in the in() have on performance? Searching a single table on an indexed simple integer is obviously going to be faster than a join. However, as the number of values in the "in" clause increases you need to know how that affects performance. For example in(1,2) might be significantly different than in(1,3,5,7,20,50,100,200). My point is that performance of an in() likely doesn''t increase linearly with the number of values. -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Nov 5, 9:40 am, Robert Walker <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> I don''t know how extensive (i.e. how many scenarios) your benchmarking > involved, but if it was only one contrived test that may not be enough. > For example what effect does an increasing number of values in the in() > have on performance? > > Searching a single table on an indexed simple integer is obviously going > to be faster than a join. However, as the number of values in the "in" > clause increases you need to know how that affects performance. For > example in(1,2) might be significantly different than > in(1,3,5,7,20,50,100,200). > > My point is that performance of an in() likely doesn''t increase linearly > with the number of values. >Thanks for the reply - I don''t seem to get many. Your point is well taken and I guess it depends the application. I''ve done a few in() joins in another application where the array was over a thousand ids and it blasted through it. I also didn''t have the "type" indexed and that would make some difference. I''m just thankful I figured out how to do the join query:-) - have not did many of those. Steve -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.