CODEWITHSUNDEEP
mysqlruby-on-railsrubyperformance
user14169
user14169

Reputation: 183

Using MySQL views in a Ruby on Rails app to improve performance

I'm having some performance issues in a rails project (running on rails 2.0.5), for example in my user administration pages.

my user model has many relations (details, addresses, roles...) who get loaded with eager loading. That creates really huge SQL queries, for some cases, it takes almost a minute to load 30 users. On the other hand removing the eager loading generates hundreds of queries, in the end I have the same problem: loading the page is slow.

I used to develop on Java & Oracle, for this kind of big queries I used to create views, those views were then cached for a faster rendering. It was extremely boring to maintain, as I had to update the database fields manually in the views scripts etc...

BUT it really had fantastic performances.... so I was wondering if anyone ever tried to implement something to take benefits of Mysql views in active record ?

I just did some basic tests, here's my view (just a few fields for the example, I have a standard Restful Authentication user table, and a big table "details" for the personal datas ):

CREATE VIEW users_vs AS SELECT
users.id              ,          
users.login           ,          
users.email           ,          
details.last_name           ,
details.first_name          ,
details.phone               ,
details.fax                 ,
FROM `users`   LEFT OUTER JOIN `details` ON details.user_id = users.id ;

Then a model:

class UsersV < ActiveRecord::Base
end

Tried a few things in my console:

u=UsersV.find(:first)  # ok !
u=UsersV.find_by_last_name('smith') #=> ok !
us=UsersV.find_all_by_last_name('smith') #=> ok too !

looking at the log, simple queries are just handled the same way as any table queries

Of course, those fake models would just be used to read datas.

I'm wondering:

Upvotes: 10

Views: 6780

Answers (4)

vladr
vladr

Reputation: 66661

The "problem" with views (and in this case you'd probably want a materialized view, assuming the data on which the complex query is based doesn't change often) is that you violate Rails' DRY'ness, to some extent (the addition of the UserV model for the view, which the purists will argue is a duplication of the User model.)

So the ideal solution would be to get the most out of the RDBMS for your complex query. Memcached will not be able to help you if the uncached queries still take a long time to execute (you still have to run them to populate memcached), or if you can't accommodate a bit of time-fudge (i.e. cached results don't have to be realtime-exact) and/or the tables involved get modified often.

  • In MySQL see if you can optimize the query further so it would take milliseconds instead of seconds (add appropriate indices, run ANALYZE, etc.)
  • If you have the option of using/trying out another RDBMS such as Postgres, by all means give it a try. MySQL is appallingly bad with complex joins (InnoDB) when compared with other cost-based engines like Oracle and Postgres. I switched from MySQL to Postgres and complex joins that would take 30s+ on MySQL (with all indices in place) take milliseconds on Postgres. If playing with Postgres make good use of PGAdminIII's graphical explain plan tool.

Upvotes: 5

Michael Glenn
Michael Glenn

Reputation: 1882

Ensure that the MySQL Query Caching is enabled which caches the result set. I've had several situations where many queries without joins versus few queries with joins is actually faster even though you're making more trips to the server.

Upvotes: 1

Tony Pitale
Tony Pitale

Reputation: 1202

http://github.com/aeden/rails_sql_views/tree/master

↑ To create the view in a migration. I would also only use them in extreme cases as they hide logic away from your application and that is generally not good.

If you have that much associated data you could look at using something like DataMapper instead of ActiveRecord with Rails as it supports lazy-loading data as required.

Upvotes: 1

Milan Novota
Milan Novota

Reputation: 15596

I use table views in Rails on a regular basis and I'm pretty happy about it. Rails treat them as normal tables as far as you only use them to read the records (which is my usual scenario). As long as your view consists only of one table, you can even do all the other CRUD stuff with it, too. So, my answer is: just do it.

Upvotes: 3

Related Questions