On Aug 6, 2009, at 1:36 PM, Alpha Blue wrote:
> > When I first started my project, I thought long and hard about the > exact > information I would need. Because my site works largely on > statistical > analysis and virtual matchups can be done by any number of > scenarios, it > makes it very difficult to come up with a common solution. > > The simple facts are that at any type 'any two' teams can be compared > against one another. The data that is used for comparison is very > extensive. When I started with a large join, the return time was so > large that at times it would freeze up my development machine. So, I > carefully and systematically broke it down into several smaller > joins to > ease the load. > > This worked fairly well, considering the type of data I'm pulling. > With > that said, the best time I can find when running this particular query > is 540 ms. As more and more work load hits the test server, hours > later > this lone query can reach up to 14,000 ms (14 seconds is just too > long). > > So, I'm trying to find a way to speed up my query, optimize it, or > figure out what I can do to improve this result. Now the model method > I'm about to show you is in fact enormous, but if you take it a part > piece by piece you'll see that I'm using selects for specific columns, > joins for specific tables and trying to do several smaller queries to > speed things up. > > My question is what can I do to improve this? As someone else mentioned if you can cache it, cache it. That said... am I reading that right that in one of your queries you are joining on 13 different tables? - Find the SQL produced for that query and run it through your database using whatever necessary to get the query plann (ie. EXPLAIN SELECT.....). Make sure you've got indexes on the columns that need them. - Play around with breaking that query up into smaller pieces. It may be that splitting them into individual queries and "building the join" yourself turns out to be faster. This is particularly true if your DB isn't picking up the indexes and if you have massive tables, but are only returning a very small set of data (and again your DB isn't picking up the indexes and lopping things off from the get go). If you're using MySQL it can be very frustrating when you have the right indexes, but it chooses not to use them. Look to see if you can force it to or give it hints. --~--~---------~--~----~------------~-------~--~----~ 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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
