Hi... I''ve got a little bit of an SQL (how does this work in rails) type question. If I have a schema with: Customer < ActiveRecord::Base has_many :contact_detail_items and then ContactDetailItem < ActiveRecord::Base belongs_to :customer and on my list form for customers, I''d like to list the contact detail items... Does ActiveRecord auto-load the contactdetailsitem objects as I''m loading my list in the controller - with one SQL leftjoin type statement? I mean, that way instead of N+1 database accesses, we just have 1. From doing a few simple tests, I think I''ve realised that it DOESN''T do it the efficient way. My next question is... WHY ON EARTH NOT? It''s SO MUCH SLOWER the non-efficient way for accessing data from "child-objects" (ie having the contactdetailitems in each customer row in a list... and the potential downside of being slower when accessing simple lists without the "child-objects" is almost not even noticable in speed difference. Julian.
Hi Julian,
AR does do this if you want it to - as an example:
Customer.find(:all, :include => :contact_detail_items)
or, for many tables:
Customer.find_by_surname(''Jones'', :include =>
[:contact_detail_items,
:invoices])
Cheers!
-David Felstead
On 9/10/05, Julian Leviston
<julian-AfxEtdRqmE/tt0EhB6fy4g@public.gmane.org>
wrote:> Hi...
>
> I''ve got a little bit of an SQL (how does this work in rails) type
> question.
>
> If I have a schema with:
>
> Customer < ActiveRecord::Base
> has_many :contact_detail_items
>
> and then
>
> ContactDetailItem < ActiveRecord::Base
> belongs_to :customer
>
> and on my list form for customers, I''d like to list the contact
> detail items...
>
> Does ActiveRecord auto-load the contactdetailsitem objects as I''m
> loading my list in the controller - with one SQL leftjoin type
> statement?
>
> I mean, that way instead of N+1 database accesses, we just have 1.
>
> From doing a few simple tests, I think I''ve realised that it
DOESN''T
> do it the efficient way.
>
> My next question is... WHY ON EARTH NOT?
>
> It''s SO MUCH SLOWER the non-efficient way for accessing data from
> "child-objects" (ie having the contactdetailitems in each
customer
> row in a list... and the potential downside of being slower when
> accessing simple lists without the "child-objects" is almost not
even
> noticable in speed difference.
>
> Julian.
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
Cool. Thanks for that man... I didn''t know that until you wrote that. Trouble is, my code runs slower when I turn it on <grins> But it''s okay, worked out it''s only a problem when more than 50 records are returned, and they rarely are with this code. Julian. On 10/09/2005, at 11:43 PM, David Felstead wrote:> Hi Julian, > > AR does do this if you want it to - as an example: > > Customer.find(:all, :include => :contact_detail_items) > > or, for many tables: > > Customer.find_by_surname(''Jones'', :include => [:contact_detail_items, > :invoices]) > > Cheers! > > -David Felstead > > On 9/10/05, Julian Leviston <julian-AfxEtdRqmE/tt0EhB6fy4g@public.gmane.org> wrote: > >> Hi... >> >> I''ve got a little bit of an SQL (how does this work in rails) type >> question. >> >> If I have a schema with: >> >> Customer < ActiveRecord::Base >> has_many :contact_detail_items >> >> and then >> >> ContactDetailItem < ActiveRecord::Base >> belongs_to :customer >> >> and on my list form for customers, I''d like to list the contact >> detail items... >> >> Does ActiveRecord auto-load the contactdetailsitem objects as I''m >> loading my list in the controller - with one SQL leftjoin type >> statement? >> >> I mean, that way instead of N+1 database accesses, we just have 1. >> >> From doing a few simple tests, I think I''ve realised that it DOESN''T >> do it the efficient way. >> >> My next question is... WHY ON EARTH NOT? >> >> It''s SO MUCH SLOWER the non-efficient way for accessing data from >> "child-objects" (ie having the contactdetailitems in each customer >> row in a list... and the potential downside of being slower when >> accessing simple lists without the "child-objects" is almost not even >> noticable in speed difference. >> >> Julian. >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On 11.9.2005, at 17.31, Julian Leviston wrote:> Cool. Thanks for that man... I didn''t know that until you wrote that. > Trouble is, my code runs slower when I turn it on <grins> But it''s > okay, worked out it''s only a problem when more than 50 records are > returned, and they rarely are with this code.That sounds like a database optimization problem. Try taking the produced SQL from the log files, and using some kind of query plan analyzer ("explain plan" in PostgreSQL, for example) to see if there''s missing indeces or something else that''s slowing down your query. //jarkko> > Julian. > > On 10/09/2005, at 11:43 PM, David Felstead wrote: > > >> Hi Julian, >> >> AR does do this if you want it to - as an example: >> >> Customer.find(:all, :include => :contact_detail_items) >> >> or, for many tables: >> >> Customer.find_by_surname(''Jones'', :include => [:contact_detail_items, >> :invoices]) >> >> Cheers! >> >> -David Felstead >> >> On 9/10/05, Julian Leviston <julian-AfxEtdRqmE/tt0EhB6fy4g@public.gmane.org> wrote: >> >> >>> Hi... >>> >>> I''ve got a little bit of an SQL (how does this work in rails) type >>> question. >>> >>> If I have a schema with: >>> >>> Customer < ActiveRecord::Base >>> has_many :contact_detail_items >>> >>> and then >>> >>> ContactDetailItem < ActiveRecord::Base >>> belongs_to :customer >>> >>> and on my list form for customers, I''d like to list the contact >>> detail items... >>> >>> Does ActiveRecord auto-load the contactdetailsitem objects as I''m >>> loading my list in the controller - with one SQL leftjoin type >>> statement? >>> >>> I mean, that way instead of N+1 database accesses, we just have 1. >>> >>> From doing a few simple tests, I think I''ve realised that it >>> DOESN''T >>> do it the efficient way. >>> >>> My next question is... WHY ON EARTH NOT? >>> >>> It''s SO MUCH SLOWER the non-efficient way for accessing data from >>> "child-objects" (ie having the contactdetailitems in each customer >>> row in a list... and the potential downside of being slower when >>> accessing simple lists without the "child-objects" is almost not >>> even >>> noticable in speed difference. >>> >>> Julian. >>> _______________________________________________ >>> Rails mailing list >>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >>> http://lists.rubyonrails.org/mailman/listinfo/rails >>> >>> >>> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Thank you so much! I can''t believe how stupid I am. <shaking head> I put the indexes I needed on, and of course now it flies. <LOL> God I suck. I think from now on I have to take a more humble approach (ie assume the problem is with me first... I mean, it''s incredibly likely to be). Julian On 12/09/2005, at 3:15 AM, Jarkko Laine wrote:> > On 11.9.2005, at 17.31, Julian Leviston wrote: > > >> Cool. Thanks for that man... I didn''t know that until you wrote that. >> Trouble is, my code runs slower when I turn it on <grins> But it''s >> okay, worked out it''s only a problem when more than 50 records are >> returned, and they rarely are with this code. >> > > That sounds like a database optimization problem. Try taking the > produced SQL from the log files, and using some kind of query plan > analyzer ("explain plan" in PostgreSQL, for example) to see if > there''s missing indeces or something else that''s slowing down your > query. > > //jarkko_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails