How to make ActiveRecord query faster on production environment running multiple servers?

Question

How could I rewrite the ActiveRecord query to solve this issue? Im a python programmer and use sqlalchemy, so it would be great if this could be explained from a python perspective. I really just need an idea of how to go about this problem.

I know the issue probably has to do with the connection being opened on every server, hence the slowness when run in production environment compared to single server staging environment. But I dont know how to go about this problem. I'm used to programming in python, where I use sqlalchemy, which is already optimized for this sort of distributed system.

class Board < ActiveRecord::Base
    def messages
        message_ids = Message.where("board_id = ?", self.id).map { |c| c.id }
        message_ids.map { |message_id| Message.find_by_id(message_id) }
    end
end

The ActiveRecord query is optimized to run on both multi-server production and single-server staging environments.

score 0 · Answer 1 · answered Aug 15 '19 at 08:04

If I am not mistaken, what you're trying to do is select all messages for a particular board.

If that's correct, you can do something like this:

def messages
  Message.where(board_id: self.id)
end

This will retrieve all messages for a particular board.

Now, you can call this method for a board, for example like this:

board = Board.first
board.messages

For more informations, check this reference: https://guides.rubyonrails.org/active_record_querying.html

Hope this helps.

Jay-Ar Polidario · Accepted Answer · 2019-08-15T19:04:43.177

0

app/models/board.rb

class Board < ActiveRecord::Base
  has_many :messages
end

Above will allow you to do something like:

board = Board.first
board.messages
# => returns a `ActiveRecord::Associations::CollectionProxy` object
#    for beginners, simply treat `ActiveRecord::Associations::CollectionProxy` as
#    if that's an Array object. So you can still do something like

board.messages.first
# => #<Message id: 123, ...>

Now you'll also need to add below (but is not required to answer your question)

app/models/message.rb

class Message < ActiveRecord::Base
  belongs_to :board
end

...which will allow you to do:

message = Message.first
message.board
# => #<Board id: 456, ...>

See Rails association docs here

In particular, take note of "implied conventions", like implied foreign_key and class_name values

edited Aug 15 '19 at 19:04

answered Aug 15 '19 at 09:30

Jay-Ar Polidario

6,463
14
28

Just a quick comment to your "I know the issue probably has to do with the connection being opened on every server". No it does not open [and closes] a connection every SQL statement. Rails by default uses a `ConnectionPool` (which you would not be really concerned about unless you'd want customized behaviour or just simply curious). See [this SO](https://stackoverflow.com/questions/34350406/how-rails-database-connection-pool-works) for more info. – Jay-Ar Polidario Aug 15 '19 at 09:36
If you intentionally do not want to define associations, but just intentionally only wanted to define `messages` method, let me know and I'll update my answer. Though I suggest using associations for this, as Rails would automatically cache objects, and that you can perform more powerful and complex association queries, likes JOINS without the complexity of writing lots of code. – Jay-Ar Polidario Aug 15 '19 at 09:41
Then why would the original code run slower on a distributed system vs a single server with higher CPU/RAM, if its not opening connections on multiple servers? I guess what I want to know is how the optimized code runs differently than the original code, and how its better in multi-server environments. – user5736915 Aug 15 '19 at 17:10
I don't know the variables at play between your personal experience of it running between distributed systems vs single-server, and so unfortunately, I cannot answer that. I just wanted to directly answer your question though that Rails has a Connection pool and by default, it does not close the connection; Rails just merely put them back into the pool when not yet in use. However, if I have to guess, yes a distributed system can potentially be also slower than a production one if only one request at a time, though a distributed system would be potentially scalable than a single-server one – Jay-Ar Polidario Aug 15 '19 at 18:26
To answer your question "I guess what I want to know is how the optimized code runs differently than the original code", your original code is performing N+1 more SQL queries, because you are doing first a `"SELECT * FROM messages where board_id = ?"`, and then after that you have n-times doing a `SELECT * FROM messages WHERE id = ? LIMIT 1`. My code would just instead execute one JOIN SQL statement: `SELECT * FROM messages INNER JOIN boards ON messages.board_id = boards.id` – Jay-Ar Polidario Aug 15 '19 at 18:33
^ above is the direct answer why it's faster, however in addition to that, Rails associations would also be smart enough to perform caching, lazy loading, and allows you to perform more complex queries, and not be concerned about the SQL generated. "Usually", Rails knows how to optimize your queries, provided you adhere to the Rails conventions (like Rails association, for example) – Jay-Ar Polidario Aug 15 '19 at 18:35

How to make ActiveRecord query faster on production environment running multiple servers?

2 Answers2