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
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.
On Nov 5, 9:40am, Robert W. [email protected] wrote:
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