John Kopanas
2006-Nov-24 20:45 UTC
Company.count taking over 3 seconds for DB of over 500K
Why would: Company.count Take 3.5s consistently to process... if I pass conditions that limit the size to about 5K the time it takes to count is milliseconds. What is up with that jerry? -- John Kopanas john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org http://www.kopanas.com http://www.cusec.net http://www.soen.info --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Jodi Showers
2006-Nov-24 21:00 UTC
Re: Company.count taking over 3 seconds for DB of over 500K
Hey John, Company.count runs a "select count(*) from company"(or some db specific variation) - which by nature must scan every row of your database. This is something you may be able to tune from your databases perspective - try a google search on "select count" and your database name. Cheers, Jodi On 24-Nov-06, at 3:45 PM, John Kopanas wrote:> > Why would: > Company.count > > Take 3.5s consistently to process... if I pass conditions that limit > the size to about 5K the time it takes to count is milliseconds. What > is up with that jerry? > > > > > -- > John Kopanas > john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org > > http://www.kopanas.com > http://www.cusec.net > http://www.soen.info > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Keynan Pratt
2006-Nov-24 21:05 UTC
Re: Company.count taking over 3 seconds for DB of over 500K
count a result set instead of the model. comps = Company.find(:all) comps.count #best guess your executing multiple queries as part of count (2n+1) -- 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 -~----------~----~----~----~------~----~------~--~---
Jodi Showers
2006-Nov-24 21:15 UTC
Re: Company.count taking over 3 seconds for DB of over 500K
On 24-Nov-06, at 4:05 PM, Keynan Pratt wrote:> > count a result set instead of the model. > > comps = Company.find(:all) > comps.count > > #best guess your executing multiple queries as part of count (2n+1)Keyan, using that method, I think he''d be instantiating 1/2 million objects. You''d be better off finding a better way to have the db count for you. J --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Trevor Squires
2006-Nov-24 22:01 UTC
Re: Company.count taking over 3 seconds for DB of over 500K
Hey, how long does it take to run this query directly against the DB? select count(*) as count_all from companies I don''t have a table with 500K records handy but I do have one with 50K and Model.count is instantaneous for me. Here''s a guess: is your Company model using STI? If it is, and you haven''t indexed your inheritance column then things might get slow because the query is actually something like: select count(*) as count_all from your_sti_table where type = ''Company'' HTH, Trevor On 24-Nov-06, at 12:45 PM, John Kopanas wrote:> > Why would: > Company.count > > Take 3.5s consistently to process... if I pass conditions that limit > the size to about 5K the time it takes to count is milliseconds. What > is up with that jerry? > > > > > -- > John Kopanas > john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org > > http://www.kopanas.com > http://www.cusec.net > http://www.soen.info > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Danny Burkes
2006-Nov-25 03:04 UTC
Re: Company.count taking over 3 seconds for DB of over 500K
Just to chime in- I have a table with 412K entries, and the simple "select count(*)..." takes 0.01 seconds. So it seems clear that something else is going on here. -- 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 -~----------~----~----~----~------~----~------~--~---
Keynan Pratt wrote:> count a result set instead of the model. > > comps = Company.find(:all) > comps.count > > #best guess your executing multiple queries as part of count (2n+1)Responding to whoever said that would create an object for every record in the table: ActiveRecord does everything else automagically, so can''t it find() a cursor and then decline to populate this until we start calling .each? I know that''s probably a little bit too much magic. Nothing else around here seems to value streaming over buffering. But cursors might find their record counts almost as cheaply as a database COUNT(*) would too, right? -- Phlip http://www.greencheese.us/ZeekLand <-- NOT a blog!!! --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
The speed at which SELECT COUNT(*) depends somewhat on your database for example if you are running MySQL and your table is a MyISAM one then the row count is part of the table meta data, and so count(*) is pretty much instant. Other databases may also have that property. On an innoDB table Mysql has to run through an index (or failing that, the whole table) to find the row count, the point at which things become slow probably corresponds to when mysql doesn''t have enough memory to read the whole thing in one go or something like that. Fred -- 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 -~----------~----~----~----~------~----~------~--~---
John Kopanas
2006-Nov-25 15:07 UTC
Re: Company.count taking over 3 seconds for DB of over 500K
Very interesting... I am using innoDB. Maybe that is why I am having problems. Plus I probably don''t have enough RAM on my dev box. hmmm... thanks :-) On 11/25/06, Fred <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > The speed at which SELECT COUNT(*) depends somewhat on your database for > example if you are running MySQL and your table is a MyISAM one then the > row count is part of the table meta data, and so count(*) is pretty much > instant. Other databases may also have that property. > On an innoDB table Mysql has to run through an index (or failing that, > the whole table) to find the row count, the point at which things become > slow probably corresponds to when mysql doesn''t have enough memory to > read the whole thing in one go or something like that. > > Fred > > -- > Posted via http://www.ruby-forum.com/. > > > >-- John Kopanas john-Iau1QiYlxLpBDgjK7y7TUQ@public.gmane.org http://www.kopanas.com http://www.cusec.net http://www.soen.info --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Adam Greene
2006-Nov-27 05:03 UTC
Re: Company.count taking over 3 seconds for DB of over 500K
Hi John, try ''select count(id) from....'' instead of ''select count(*)...''. I''m not sure about MySQL, but other databases (sybase and db2) will select all columns from the row to do the count, which means a full table scan. If you only scan the id column, it will be faster. If it is indexed, it will be a lot faster. good luck! Adam --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Adam Greene wrote:> try ''select count(id) from....'' instead of ''select count(*)...''. I''m > not sure about MySQL, but other databases (sybase and db2) will select > all columns from the row to do the count, which means a full table > scan. If you only scan the id column, it will be faster. If it is > indexed, it will be a lot faster.(I don''t know if someone asked this, but) if you have a billionty-one extra tables all joined in with rampant has_many directives, would a lowly .count() accidentally traverse them all? -- Phlip http://www.greencheese.us/ZeekLand <-- NOT a blog!!! --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---