Play ActiveRecord model queries are very slow

[albertlyu]

According to New Relic (which monitor's app performance), the queries to grab plays or shots statistics are causing some pages to load quite slowly. 5-6 second page loads are not good at all. We'll need to investigate how to write more optimal queries, particularly the shot statistics in the draft view, as I believe the page load time is due to this.

Additionally, we'll need to figure out how to test this in our dev environment. Most likely it'll be monitoring the rails server logs while navigating the pages locally, but it may be worth our time to investigate continuous integration, testing and performance tools for Rails apps.

[albertlyu]

Okay, after reading through some reference files, it seems like Postgres caching could dramatically improve query performance. It's going to cost some money though. The hobby-basic plan that our app is currently on has no in-memory caching unfortunately. Seems like the standard-yanari plan is a good option, when we're ready to fork out an additional $50/month.

https://devcenter.heroku.com/articles/heroku-postgres-plans https://devcenter.heroku.com/articles/understanding-postgres-data-caching

So I'll keep this issue open for us to optimize mostly the Play queries as well as the PlayerGame queries and to test the app performance after that. But long-run fix will probably be upgrading the plan, should we ever take the app commercial.

[albertlyu]

One way of improving performance is creating views in the Postgres database. In particular, PostgreSQL 9.3 introduced built-in materialized views. See the following for reference: http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html http://wiki.postgresql.org/wiki/Materialized_Views

How this interacts with Active Record and integrates into Rails, I'm not sure yet. In general though, we really should be storing these custom metrics in views, maybe views named players_stats for all the basic per-game, per 40-min, percentage stats and the shot location stats. This probably is a more straightforward way to go. If this is the direction we go, this issue's title can be updated.

[albertlyu]

I think implementing Postgrews views in the Rails app using the following link would absolutely improve the performance of our app: http://alexpotrykus.com/blog/2013/04/10/postgres-views-in-rails/

[albertlyu]

More resources for monitoring and improving app performance, particularly with optimizing Heroku's dyno usage: https://devcenter.heroku.com/articles/optimizing-dyno-usage

[albertlyu]

Some of these SQL queries are taking an embarrasing 7 seconds when run in production...

[albertlyu]

Thanks to #48, the pages are loading much faster now. I'll close this issue for now, as major work has been completed to improve the efficiency of the database calls. There's still potential room for improvement in terms of indexing the stats tables and updating the model associations, but this is great for now. An initial browse through the site in production shows <1.25 seconds load time, sometimes taking even faster at 0.5 seconds.

For other contributors to this project, once you fetch changes from upstream, you will need to run rake db:migrate locally in order to update your database with the new tables.