2

I'm only just starting to learn about views in ActiveRecord from reading a few blog posts and some tutorials on how to set them up in Rails.

What I would like to know is what are some of the pros and cons of using a View instead of a query on existing ActiveRecord tables? Are there real, measurable performance benefits of using a view?

For example, I have a standard merchant application with orders, line items, and products. For an admin dashboard, I have various queries, many of which ping a query that I reuse a lot in the code - namely one that returns user_id, order_id and total_revenue from that order. From a business perspective, many good stats are based off that core query. At what point does it make sense to switch to to a view instead?

The ActiveRecord docs on Views are also a bit sparse so any references to some good resources on both the why and how would be greatly appreciated.

Clarification: I am not talking about HTML views but SQL database views. Essentially, are the performance wins maintained in an ActiveRecord implementation? Since the docs are sparse, are there any potential obstacles to those performance wins that could be lost if you implement them incorrectly (i.e., any non-obvious gotchas)?

Sia
  • 8,894
  • 5
  • 31
  • 50

2 Answers2

1

I got this information from another developer off-line, so I am answering my own question here in case other people stumble upon it.

Basically, ActiveRecord starting in Rails 3.1 began implementing prepared statements which preprocess and cache SQL statement patterns ahead of time which later allow faster query responses. You can read more about it in this blog post.

This actually might result in not much benefit in switching to views in PostgreSQL since views may not perform much better than prepared statements in PG.

The PostgreSQL documentation on prepared statements seems clear and well-written. More thoughts on PostgreSQL views performance can be found in this stackoverflow post.

Additionally, it's probably much more likely that your Rails app has performance issues due to N+1 queries - this is a great post that explains the problem and one of the easiest ways to prevent it with eager loading.

Community
  • 1
  • 1
Sia
  • 8,894
  • 5
  • 31
  • 50
0

This question is not directly related to ActiveRecord and it seems more a database related question to me. The answer is "it depends". Many times people use view because they want to:

  • represent a subset of the data contained in a table
  • simplify your query by join multiple tables into a virtual table represented by the view
  • do aggregation in the view
  • hide complexity of your data
  • for some security reasons

etc.

But most of the aforementioned features can be implemented by using raw tables. It's just a little complicated than using views. Another place you may considering using a view is for performance reason. That's materialized view or indexed view in SQL Server. Basically it saved a copy of your data in the view in the form you want and it can greatly boost performance.

Just a learner
  • 26,690
  • 50
  • 155
  • 234
  • Thanks for that insight! Maybe what I am not fully grasping is the marriage of how it is implemented in ActiveRecord and whether that implementation still constitutes a performance win. If you have a database where source tables are being refreshed often, does the way that ActiveRecord treats a view (and refreshing the data with it) still end up improving performance? – Sia Dec 28 '15 at 23:45