Hi Everybody Suppose We have two models named Book and Author. Make a suggestion on how we will improve the database query performance on the following code. books = Book.find(:all) books.each do | book | puts book.author end Thanks in advance --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 22, 5:39 pm, Barun Anand <godbolena...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi Everybody > > Suppose We have two models named Book and Author. Make a suggestion on > how we will improve the database query performance on the following > code. > > books = Book.find(:all) > books.each do | book | > puts book.author > end > > Thanks in advanceUse eager-loading: books = Book.find(:all, :include => :author) --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---
You can use :select and :joins for the best performance, this is what I use currently. :include is much less efficient but it is the Rails way of doing things. It used to create a JOIN but in a very ugly way. Since Rails 2.x the :include code has been changed as it now makes 1 query on each table. It is still less efficient than custom :joins, but it is easier to use its derived objects. -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 22, 6:01 pm, Fernando Perez <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Since Rails 2.x the :include code has been changed as it now makes 1 > query on each table. It is still less efficient than custom :joins, but > it is easier to use its derived objects.Not for every scenario. For has_many associations, the Rails 2.x :include code is possibly better than a custom join when dealing with large datasets, since a custom join would give you a cartesian product while :include will only fetch the necessary records, at the expense of course, of additional SQL statements. This specially holds true if your webhost and db resides on different servers. If the data is large enough, it may merit to add indices to your reference fields (or foreign keys): http://erolfornoles.blogspot.com/2008/08/optimize-eager-loading-in-rails-21.html For 1-to-1 associations like belongs_to, Simpler Piggy-Backing makes for a good alternative to custom joins: http://railsexpress.de/blog/articles/2006/05/29/simpler-piggy-backing --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---
> Not for every scenario. For has_many associations, the Rails > 2.x :include code is possibly better than a custom joinHave you tested? because at the time of Rails 1.2.6 where the :include created a gigantic JOINs, my custom :joins was at least x3 times faster! mostly because I only pick the fields I really need and because AR doesn''t create full objects with their references and all the stuff. I tried finding some info on MySQL forums and websites about 1 query with multiple JOIN versus multiple queries, but it is not clear which is more efficient. Things get really complicated when I read: "How adding another table to JOIN can improve performance ?" http://www.mysqlperformanceblog.com/2008/08/01/how-adding-another-table-to-join-can-improve-performance/ -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 22 Sep 2008, at 14:01, Fernando Perez wrote:> >> Not for every scenario. For has_many associations, the Rails >> 2.x :include code is possibly better than a custom join > > Have you tested? because at the time of Rails 1.2.6 where the :include > created a gigantic JOINs, my custom :joins was at least x3 times > faster! > mostly because I only pick the fields I really need and because AR > doesn''t create full objects with their references and all the stuff. > > I tried finding some info on MySQL forums and websites about 1 query > with multiple JOIN versus multiple queries, but it is not clear > which is > more efficient.I wrote the :include code in 2.1 because of performance problems I had with :include in 1.2.x. :including multiple has_manys from the same object very quickly leads to the database generating a very large number of rows (far greater than the number of actual objects you want) which doesn''t help the database or ActiveRecord (who has to wade through them and discard portions of rows it has already seen. Fred --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 22, 9:01 pm, Fernando Perez <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Have you tested? because at the time of Rails 1.2.6 where the :include > created a gigantic JOINs, my custom :joins was at least x3 times faster! > mostly because I only pick the fields I really need and because AR > doesn''t create full objects with their references and all the stuff.Yeah, in my case the Rails 2.1 :include was significantly faster than a :join for multiple has_many relationships, like: class Depot has_many :trucks has_many :containers has_many :cranes end Of course, we may be talking about apples and bananas here, since my example involves has_many associations while the scenario I could think of given your description is a belongs_to or has_one. I agree that belongs_to or has_one associations are better off piggy- backed via a :join and :select, but using them eventually leads into a lot of repetitions in our code. That''s why I prefer using the Simpler Piggy-Backing plugin: http://railsexpress.de/blog/articles/2006/05/29/simpler-piggy-backing The author even claims: "In fact, using the extension is slightly faster than coding the select and joins manually (1%-3%)."> I tried finding some info on MySQL forums and websites about 1 query > with multiple JOIN versus multiple queries, but it is not clear which is > more efficient.The Depot example I mentioned earlier is better composed using atleast 2 SQL statements, as against a single one. Not only would the single SQL approach (using joins) produce a cartesian product, it is logically incorrect since trucks, cranes and containers are independent of each other. Then again, it all boils down to AR being ridiculously slow in composing AR objects from a resultset.> Things get really complicated when I read: "How adding another table to > JOIN can improve performance ?" > > http://www.mysqlperformanceblog.com/2008/08/01/how-adding-another-tab...There are a lot of quirks to doing optimizations in MySQL. The "one index per one query" is one, which is why I usually shy away from MySQL when doing data warehousing. I tried using it once to do CDR (call detail record) reporting, but the performance was such a nightmare that I had to migrate it to Postgre. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> Yeah, in my case the Rails 2.1 :include was significantly faster than > a :join for multiple has_many relationships, like: >Interesting. What is the size of the DB on which your performed the tests? Mine were done on tables that have 100.000 and 1.000.000.000 rows. The :joins gave nice results that were around 0.1s on my good old PowerPC dev machine. The old :include method (rails 1.2.x) took almost 1s to perform the same query.> I wrote the :include code in 2.1 because of performance problems I had > with :include in 1.2.x.Hi Fred, yeah the :include in 1.2.x was pretty lame. Do you have some test results of the new :include versus custom :joins? -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 22 Sep 2008, at 15:23, Fernando Perez wrote:> >> Yeah, in my case the Rails 2.1 :include was significantly faster than >> a :join for multiple has_many relationships, like: >> > > Interesting. > > What is the size of the DB on which your performed the tests? Mine > were > done on tables that have 100.000 and 1.000.000.000 rows. The :joins > gave > nice results that were around 0.1s on my good old PowerPC dev machine. > The old :include method (rails 1.2.x) took almost 1s to perform the > same > query. > >> I wrote the :include code in 2.1 because of performance problems I >> had >> with :include in 1.2.x. > Hi Fred, yeah the :include in 1.2.x was pretty lame. Do you have some > test results of the new :include versus custom :joins?Those are almost by definition hard to come by: custom stuff is likely to be specific to one app (and just some custom joins does not accomplish the primary goal of :include (ie setup your associations). Fred --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 22, 10:23 pm, Fernando Perez <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> What is the size of the DB on which your performed the tests? Mine were > done on tables that have 100.000 and 1.000.000.000 rows. The :joins gave > nice results that were around 0.1s on my good old PowerPC dev machine. > The old :include method (rails 1.2.x) took almost 1s to perform the same > query.Are the seconds you mentioned DB fetch times? Or do they include AR times? And what kind of associations are involved? The Depot model I mentioned is not that large, yet. A couple of dozen depots, which has a hundred to a few thousand trucks and containers each, and a couple of dozen cranes each. If you were going to use a join on them, the yield will be a cartesian product of: 20 depots x 1000 trucks x 1000 containers x 10 cranes = 200,000,000 rows on the average, which I''m pretty sure is going to take AR longer to compose.> > I wrote the :include code in 2.1 because of performance problems I had > > with :include in 1.2.x. > > Hi Fred, yeah the :include in 1.2.x was pretty lame. Do you have some > test results of the new :include versus custom :joins? > -- > Posted viahttp://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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---
On Sep 22, 10:30 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Those are almost by definition hard to come by: custom stuff is likely > to be specific to one app (and just some custom joins does not > accomplish the primary goal of :include (ie setup your associations).I agree. has_one and belongs_to associations can be piggy-backed via :join and :select, but has_many associations cannot. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---
> Are the seconds you mentioned DB fetch times? Or do they include AR > times? And what kind of associations are involved? >These were "completed" times.> If you were going to use a > join on them, the yield will be a cartesian product of: > > 20 depots x 1000 trucks x 1000 containers x 10 cranes = 200,000,000 > rows on the average, which I''m pretty sure is going to take AR longer > to compose. >Your figures are misleading. You never display all the records at once, you always pass a :conditions option to the query, don''t you? Even your :include does, so what are you talking about? -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 22, 11:57 pm, Fernando Perez <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> These were "completed" times.And in what type of association are they in?> > If you were going to use a > > join on them, the yield will be a cartesian product of: > > > 20 depots x 1000 trucks x 1000 containers x 10 cranes = 200,000,000 > > rows on the average, which I''m pretty sure is going to take AR longer > > to compose. > > Your figures are misleading. You never display all the records at once, > you always pass a :conditions option to the query, don''t you? Even your > :include does, so what are you talking about?Nope, those figures aren''t misleading. Those are actual records fetched for a report, if I use traditional joins. Of course it won''t be displayed on-screen, but AR has to compose the corresponding AR objects given that many records. The conditions aren''t present since I have a rake task scheduled via cron that handles archiving of data, which means trucks, containers, and cranes for the past month aren''t included in the resultset. It''s more like an incoming/outgoing report that is generated weekly and monthly, used for billing and accounting purposes. What the new :include does is to break those into manageable SQL. 20 depots x 1000 trucks = 20,000, assuming again, an average of 1000 trucks per depot 20 depots x 1000 containers = 20,000 20 depots x 10 cranes = 200 All in all, AR will only need to compose from 40,200 results on the average. This is a sample of the report which needs to be generated: - Depot - - List of Trucks - - List of Containers - - List of Cranes - - Depot - - List of Trucks - - List of Containers - - List of Cranes - ... If you can give me a :select :join construct (without resorting to multiple SQL''s) which could efficiently generate that kind of report, I''d be more than happy to use that. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---
Hi Erol,
I now perfectly understand your point. Thank you for clarifying.
I will definitely update my code from using :joins to the new :include.
However I am having some problems. Using the following find:
---
@order = Order.find(:all, :include => [:items, :products],
:conditions => [''orders.id = ?'',
session[:order_id]])
---
I get the following SQL:
---
SELECT * FROM `orders` WHERE (orders.id = 15)
SELECT `items`.* FROM `items` WHERE (`items`.order_id IN (15))
CACHE SELECT `items`.* FROM `items` WHERE (`items`.order_id IN (15))
SHOW FIELDS FROM `products`
SELECT * FROM `products` WHERE (`products`.`id` IN (1,2))
SELECT * FROM `products` WHERE (`products`.`id` = 2)
SELECT * FROM `products` WHERE (`products`.`id` = 1)
---
Why is Rails making unnecessary duplicate queries?
--
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Woops, I made a typo, instead of [,] one should use { => }.
--
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
> I will definitely update my code from using :joins to the new :include.Just like what Fred said, it''s a case-to-case basis. But, uhmmm, it''s your call I guess.> However I am having some problems. Using the following find: > --- > @order = Order.find(:all, :include => [:items, :products], > :conditions => [''orders.id = ?'', session[:order_id]]) > --- > > I get the following SQL: > --- > SELECT * FROM `orders` WHERE (orders.id = 15) > SELECT `items`.* FROM `items` WHERE (`items`.order_id IN (15)) > CACHE SELECT `items`.* FROM `items` WHERE (`items`.order_id IN (15)) > SHOW FIELDS FROM `products` > SELECT * FROM `products` WHERE (`products`.`id` IN (1,2)) > SELECT * FROM `products` WHERE (`products`.`id` = 2) > SELECT * FROM `products` WHERE (`products`.`id` = 1) > --- > > Why is Rails making unnecessary duplicate queries? > --Try Order.find(session[:order_id], :include => [:items, :products]) There''s no way I know of to eliminate SHOW FIELDS FROM ..., unless AR gets updated. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---
On Sep 23, 1:05 am, Fernando Perez <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> @order = Order.find(:all, :include => [:items, :products], > :conditions => [''orders.id = ?'', session[:order_id]])Plus, there''s a caveat here. Issuing: Order.find(:all, include => [:items, :products], :conditions => "items.disabled IS NULL") Or referencing the any of the included tables in :conditions will force AR to revert to the old eager-loading scheme. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---
> Or referencing the any of the included tables in :conditions will > force AR to revert to the old eager-loading scheme. >No, it depends how you build the :include. I have yet to found how it actually works. Sometimes I get the old way, sometimes I get the multi-query, it really depends on how you pass the hash.> There''s no way I know of to eliminate SHOW FIELDS FROM ... >You cannot, and you shouldn''t. This is how AR knows which attributes a model has. I already tried in the past to by-pass AR, and I got more problems than anything else, and the speed improvement was not that great. Anyway these queries are fast and they are cached. -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Using the following query:
---
@order = Order.find(:all, :include => [ :products, :items],
:conditions => [''orders.id = ?'',
session[:order_id]])
---
I get the correct multi SQL queries. But I cannot understand why when I
try to do:
---
<%- for item in @order.items -%>
<%= debug item %>
<%- end -%>
---
I get the following error message:
---
undefined method `items'' for #<Array:0x2e9c224>
---
How do I access the list of items associated to the order? Why is it an
array?
--
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On 22 Sep 2008, at 19:06, Fernando Perez wrote:> >> Or referencing the any of the included tables in :conditions will >> force AR to revert to the old eager-loading scheme. >> > No, it depends how you build the :include. I have yet to found how it > actually works. Sometimes I get the old way, sometimes I get the > multi-query, it really depends on how you pass the hash. >Whenever AR thinks you are referencing a column from a table other that the base table it reverts to the old style :include (that''s not to say it gets it right all the time)>> There''s no way I know of to eliminate SHOW FIELDS FROM ... >> > You cannot, and you shouldn''t. This is how AR knows which attributes a > model has. I already tried in the past to by-pass AR, and I got more > problems than anything else, and the speed improvement was not that > great. Anyway these queries are fast and they are cached.Yup, it;s only in dev mode that they are repeated anyway.> > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 23, 2:06 am, Fernando Perez <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > Or referencing the any of the included tables in :conditions will > > force AR to revert to the old eager-loading scheme. > > No, it depends how you build the :include. I have yet to found how it > actually works. Sometimes I get the old way, sometimes I get the > multi-query, it really depends on how you pass the hash.Actually, yes, it depends on whether you reference non-main - or therefore included - tables in :conditions or :order. Taken directly from http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html: Since only one table is loaded at a time, conditions or orders cannot reference tables other than the main one. If this is the case Active Record falls back to the previously used LEFT OUTER JOIN based strategy. For example Post.find(:all, :include => [ :author, :comments ], :conditions => [''comments.approved = ?'', true]) --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---
On 22 Sep 2008, at 19:15, Fernando Perez wrote:> > Using the following query: > --- > @order = Order.find(:all, :include => [ :products, :items], > :conditions => [''orders.id = ?'', session[:order_id]]) > --- > > I get the correct multi SQL queries. But I cannot understand why > when I > try to do: > --- > <%- for item in @order.items -%> > <%= debug item %> > <%- end -%> > --- > > I get the following error message: > --- > undefined method `items'' for #<Array:0x2e9c224> > ---because @order is an array of orders. The items method exists only on the elements of that array. Fred> > > How do I access the list of items associated to the order? Why is it > an > array? > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 23, 2:15 am, Fernando Perez <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Using the following query: > --- > @order = Order.find(:all, :include => [ :products, :items], > :conditions => [''orders.id = ?'', session[:order_id]]) > --- > > I get the correct multi SQL queries. But I cannot understand why when I > try to do: > --- > <%- for item in @order.items -%> > <%= debug item %> > <%- end -%> > --- > > I get the following error message: > --- > undefined method `items'' for #<Array:0x2e9c224> > --- > > How do I access the list of items associated to the order? Why is it an > array?@order = Order.find(session[:order_id], :include => [:products, :items]) That''ll return a single Order instance instead of an array of Orders. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---
> Whenever AR thinks you are referencing a column from a table other > that the base table it reverts to the old style :include (that''s not > to say it gets it right all the time) >In such case, one must check his generated query, and if such condition happens, then probably one can manually create a "pre-query" to save AR from trying to be be too clever.> because @order is an array of orders.Ok I get it. By the way thank you Fred for having written the new :include code, because god knows how people have been able to live until now. -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Thanks Erol, it is 100% safe, or is it preferable to add the :conditions? I never know when Rails sanitizes the user input or not. Even if the data comes from the session, I do not trust any input at all. -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 23, 2:49 am, Fernando Perez <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Thanks Erol, it is 100% safe, or is it preferable to add the > :conditions? > > I never know when Rails sanitizes the user input or not. Even if the > data comes from the session, I do not trust any input at all.It looks safe; I''ve tried a bunch of SQL-injection-like params on it but it always end up as WHERE (`id` = 0), so it''s safe to assume that it is being sanitized. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---
> 20 depots x 1000 trucks x 1000 containers x 10 cranes = 200,000,000 > rows on the average, which I''m pretty sure is going to take AR longer > to compose.Woops I didn''t spot that in the first place. But you are talking about 200.000.000 rows over all the possibilities, but your figure as I said is misleading, as not all depots hold all the containers at the same time, this is not possible. I was talking about my tables being: products: 100 rows order: 100.000 rows items: 1.000.000 rows using :joins keeps processing time under 1s, whereas the old :include was around 1s. So following your theory I would have: 100x100.000x1.000.000 = 10e13 possibilities which is obviously false. :joins certainly owns :include, but the major drawback is that you cannot have real objects as the result would be a franken crane having a diesel engine and 500m2 of storage space and 5t of bananas. I have talked to MySQL power users, and their advice is that having 1 single query with JOIN is faster than having multiple queries, but the real answer, is "it depends on how much data your fetching each time". -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Here are my test results:
Product: 100 rows
Orders: 100.000 rows
Items: 1.000.000 rows
@orders = Order.find( :all, :include => { :items => :product },
:conditions => [''user_id = ?'',
session[:user_id]])
Takes: 0.04s of DB time
The equivalent with :joins, only takes 0.004s of DB time.
The big advantage of :joins, is that I can use :select to only fetch the
rows I need, whereas with :include I need to retrieve all the fields.
The drawback of :joins, is that I don''t have objects with relationships
as one could expect with :include, but I consider that having objects is
overwhelming. The goal is to present retrieved data from the DB to the
user or to a reporting system, therefore objects are not that important.
Anyway I would like to see Erol Fornoles come with proof that :include
is faster than :joins.
My conclusion is :joins >> :include by a factor 5-10, and I would not
recommend any advice from the railsenvy.com as they claim that you can
abuse of :include. This is certainly not true and can make your app go
really slow.
Use :include cautiously and only when it is absolutely needed. I know
this is going slightly against the Rails Way of doing things, but I
don''t care as long as my webapp is blazing fast, and that''s
possible
with Rails.
Have a nice day.
--
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On 23 Sep 2008, at 14:58, Fernando Perez wrote:> > Here are my test results: > > Product: 100 rows > Orders: 100.000 rows > Items: 1.000.000 rows > > @orders = Order.find( :all, :include => { :items => :product }, > :conditions => [''user_id = ?'', > session[:user_id]]) > > Takes: 0.04s of DB time > > The equivalent with :joins, only takes 0.004s of DB time. >That''s not the pathological case. the bad case is if orders had two has_manys on it (ie you were doing :include => [:items, :something_else]) and with orders having quite a few of both.> The big advantage of :joins, is that I can use :select to only fetch > the > rows I need, whereas with :include I need to retrieve all the fields. > The drawback of :joins, is that I don''t have objects with > relationships > as one could expect with :include, but I consider that having > objects is > overwhelming. The goal is to present retrieved data from the DB to the > user or to a reporting system, therefore objects are not that > important. >It largely depends on what you are doing. If what is important is the business intelligence in your models then it''s really handy to have them. Or for the classic n+1 problem of displaying a list of orders along with the items and the products (eg your customer''s account history): you don''t want to be writing custom selects and what not for something mundane like that. Fred> Anyway I would like to see Erol Fornoles come with proof that :include > is faster than :joins. > > My conclusion is :joins >> :include by a factor 5-10, and I would not > recommend any advice from the railsenvy.com as they claim that you can > abuse of :include. This is certainly not true and can make your app go > really slow. > > Use :include cautiously and only when it is absolutely needed. I know > this is going slightly against the Rails Way of doing things, but I > don''t care as long as my webapp is blazing fast, and that''s possible > with Rails. > > Have a nice day. > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 23, 9:58 pm, Fernando Perez <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Here are my test results: > > Product: 100 rows > Orders: 100.000 rows > Items: 1.000.000 rows > > @orders = Order.find( :all, :include => { :items => :product }, > :conditions => [''user_id = ?'', session[:user_id]]) > > Takes: 0.04s of DB time > > The equivalent with :joins, only takes 0.004s of DB time.Unfortunately, your findings is also misleading in a sense that your associations are simplistic compared to mine. - Fred mentioned that the bad case is orders having two has_manys, which orders doesn''t. My depot model, on the other hand, has 3 has_manys. - My reporting requirements are more intensive than yours. - You didn''t even bother to post your :joins and :select construct.> The big advantage of :joins, is that I can use :select to only fetch the > rows I need, whereas with :include I need to retrieve all the fields. > The drawback of :joins, is that I don''t have objects with relationships > as one could expect with :include, but I consider that having objects is > overwhelming. The goal is to present retrieved data from the DB to the > user or to a reporting system, therefore objects are not that important.Again, your model is too simplistic. Objects are going to be important when you''re generating a report with a higher-degree of complexity to what you''ve mentioned.> Anyway I would like to see Erol Fornoles come with proof that :include > is faster than :joins.Fair enough, here is my benchmark with the following dataset: - 10 Depots - 1000 Trucks per Depot - 1000 Containers per Depot Don''t bother telling me that this is impossible because this is what''s really happening; there are on _average_ a hundred truck and a hundred container movements per _day_ per _depot_. Depot.find(:all, :includes => [:trucks, :containers]) = 0.040000 + 1.482000 + 1.732000 Depot.find(:all, :select => "depots.id, depots.name, trucks.plate_no", :joins => "LEFT JOIN trucks ON trucks.depot_id depots.id") = 1.242000 Looks faster than :include, _BUT_, we haven''t included Containers yet, so we''ll included it now to go with your 1-SQL-using-joins theme: Depot.find(:all, :select => "depots.id, depots.name, trucks.plate_no, containers.serial_no", :joins => "LEFT JOIN trucks ON trucks.depot_id = depots.id LEFT JOIN containers ON containers.depot_id = depots.id") = 20+ minutes and still going strong! Now I would like to see Fernando Perez make an efficient :select :join for _MULTIPLE_ has_many relationships (without relying on multiple SQLs) which can generate the report below, given the dataset conditions that I have given above. Depot - List of Trucks - - List of Containers - Depot - List of Trucks - - List of Containers - ...> My conclusion is :joins >> :include by a factor 5-10, and I would not > recommend any advice from the railsenvy.com as they claim that you can > abuse of :include. This is certainly not true and can make your app go > really slow.Again, it depends on the scenario. You''re basing your conclusion on a simplistic model with a has_many -> belongs_to chain. The circumstances greatly change when you''re dealing with _multiple_ has_many relations. Have a nice day. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---
Your setup is definitely puzzling me now. When I have some time, I will do something similar and make my own tests. One day I might run into it, so I want to be ready with the best solution :include VS :joins. I see one possible area of improvement that you can quickly test though: did you add indexes (I presume on depot_id) for both the trucks and containers models? By default, Rails only creates indexes for the id column, not for the referenced columns. JOINs are very sensitive to indexes. Can you make a quick test and report back here? Even if my models have simple relationships, if I remove the indexes then yes, it is going to become a nightmare, remember I am talking about a 1M rows table here! Apologies to the railsenvy.com guys, what they mean by "using and abusing :include", is that you can as I demonstrated, run into very long queries. So they have good advice... so do I ;-P -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Sep 24, 1:08 am, Fernando Perez <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Your setup is definitely puzzling me now. When I have some time, I will > do something similar and make my own tests. One day I might run into it, > so I want to be ready with the best solution :include VS :joins.The setup is pretty normal. If I was going to do it via Crystal Reports, I would have had to use sub-reports, which is going to hit the db hard.> I see one possible area of improvement that you can quickly test though: > did you add indexes (I presume on depot_id) for both the trucks and > containers models? By default, Rails only creates indexes for the id > column, not for the referenced columns. JOINs are very sensitive to > indexes. > > Can you make a quick test and report back here?The benchmark I ran earlier had indexes on both primary keys and foreign keys. It is actually recommended that you make indices for foreign keys when using :include for large resultsets, otherwise MySQL is going to have to do a table scan on a potentially large table.> Even if my models have simple relationships, if I remove the indexes > then yes, it is going to become a nightmare, remember I am talking about > a 1M rows table here!Uhmmm, 1M rows is not large for some db folks. Look for my post at the comp.database.mysql and see how most of the MySQL folks replied. I was actually hoping to get tips on what to configure and if I should already move it to a cluster but here is what I got instead: "I work with databases that generate 5TB+/mth. To do some quick calculations, 162M*30=4.86B records and depending on the record size - I would say you have a pretty healthy size database. Let''s say your nominal record length is 250 bytes, then you are storing ~1.22Tb /mth." - Michael Austin --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---