In a site I''m working on, I have to generate statistics on demand. This requires going through substantially all three tables of the database, without about 1500 records in table A owning about 12,000 records in tables B and C. This is after the site''s been up about three weeks, so I know it will get a lot worse. There''s no issue with memory (dedicated server), and the database operation is fast enough (0.2 second or so), but the number crunching is killing me, presently taking about 16 seconds to iterate through all the data and produce the hash that eventually gets displayed. I''ve gotten to the point where I''m thinking about rewriting the routine in Perl or even C. I''d much prefer to avoid that. I would greatly appreciate suggestions from anybody who''s confronted a similar situation. Thanks, --Al Evans -- 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 Nov 14, 2006, at 7:41 PM, Al Evans wrote:> In a site I''m working on, I have to generate statistics on demand. > > This requires going through substantially all three tables of the > database, without about 1500 records in table A owning about 12,000 > records in tables B and C. This is after the site''s been up about > three > weeks, so I know it will get a lot worse. > > There''s no issue with memory (dedicated server), and the database > operation is fast enough (0.2 second or so), but the number > crunching is > killing me, presently taking about 16 seconds to iterate through > all the > data and produce the hash that eventually gets displayed. > > I''ve gotten to the point where I''m thinking about rewriting the > routine > in Perl or even C. I''d much prefer to avoid that. > > I would greatly appreciate suggestions from anybody who''s confronted a > similar situation. > > Thanks, > > --Al EvansI''d suspect that the "data" you''re iterating through is in the form of ActiveRecord models, yes? If so, you might get a big win if you can avoid that overhead completely by dropping into SQL and using something like select_all [1] (to get hashes back rather than model objects). Perhaps a bit of code would let us help you better. -Rob [1] http://rdoc.caboo.se/doc/classes/ActiveRecord/ConnectionAdapters/ DatabaseStatements.html#M004819 Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Al Evans wrote:> In a site I''m working on, I have to generate statistics on demand. > > This requires going through substantially all three tables of the > database, without about 1500 records in table A owning about 12,000 > records in tables B and C. This is after the site''s been up about three > weeks, so I know it will get a lot worse. > > There''s no issue with memory (dedicated server), and the database > operation is fast enough (0.2 second or so), but the number crunching is > killing me, presently taking about 16 seconds to iterate through all the > data and produce the hash that eventually gets displayed. > > I''ve gotten to the point where I''m thinking about rewriting the routine > in Perl or even C. I''d much prefer to avoid that. > > I would greatly appreciate suggestions from anybody who''s confronted a > similar situation.Can you store precomputed stats in the database, updating them as each piece of data is added? Or can you automatically calculate and save the stats once a day, and update these on demand using only the data added since? -- We develop, watch us RoR, in numbers too big to ignore. --~--~---------~--~----~------------~-------~--~----~ 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 11/15/06, Al Evans <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> I''ve gotten to the point where I''m thinking about rewriting the routine > in Perl or even C. I''d much prefer to avoid that.Before you do that, try doing as much of the calculating as possible in SQL. I had a similar problem to yours recently, and brought the statistics generation time down from 45 seconds to 0.6 by letting the database do the computation. -- Calle Dybedahl -*- cdybedahl-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Calle Dybedahl wrote:> On 11/15/06, Al Evans <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote: > >> I''ve gotten to the point where I''m thinking about rewriting the routine >> in Perl or even C. I''d much prefer to avoid that. > > Before you do that, try doing as much of the calculating as possible > in SQL. I had a similar problem to yours recently, and brought the > statistics generation time down from 45 seconds to 0.6 by letting the > database do the computation.Thanks! This is what I did. The code is uglier now, but it works almost 10 times as fast. --Al Evans -- 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 -~----------~----~----~----~------~----~------~--~---