3 ms·

Protip: in CRUD applications, most performance issues from the data layer have nothing to do with the database. Typically, the performance issues are at the Ac

Protip: in CRUD applications, most performance issues from the data layer have nothing to do with the database. Typically, the performance issues are at the ActiveRecord level, when it's initialization 100s of instances of an object to render a table. With Rails, the #1 optimization technique is to use `ActiveRecord::Base.connection.exec_query ` to return a hash instead of initialization objects. Side note: I still love Rails.

sigmonsays 4y ago
from my experience, every performance issue is the database when your programming language makes better choices.
rubyist5eva 4y ago
Another trap people fall into is doing joins in their application instead of in the database. I see this all the time where people will query some table with a condition, map the ids from the result and then query another table (which also joins the first table) and then filters by the id of the first query. Or just doing massive aggregates in ruby instead of having the database do it and just spitting out the result. A little bit of knowledge about SQL and how a relational database works goes a long way.
Rapzid 4y ago
It also wraps every query in an explicit transaction unnecessarily causing locks to be held while waiting for the commit from the server which got held up behind some cpu intensive block in separate thread (prob some other request deserializing and hydrating those 100s of entities you mentioned). Side note: I really dislike Rails.
- jemmyw 4y ago
  > It also wraps every query in an explicit transaction unnecessarily causing locks to be held while waiting for the commit from the server no it doesn't. It only wraps updates in transactions, and a single line update would have an implicit transaction at the database anyway.
  - Rapzid 4y ago
    It would have an implicit transaction, wouldn't it? Think about what we both wrote here (sans the weak man argument, of course I'm talking about updates).
    - jemmyw 4y ago
      I see. Well you could have been more explicit in your original message, I don't think it was obvious at all that you were talking about updates. Rails does the right thing here in my opinion. Anything that happens between calling `.save` and it returning should be in the safe envelop of a transaction. It can't know that you're only going to emit a single UPDATE query in that transaction, plus the framework user expects any exception between calling save and it returning to roll back. There are outs if you need them. I've worked on a few reasonably high traffic and complex rails applications and I don't recall a time when the time between the last query and the commit caused an issue. Yes times when the transaction itself had got too large and that commit was taking awhile. That said, there were more of these types of locking issues in the project I worked on 10 years ago with MySQL and repeatable read isolation level than with PG since then which defaults to read committed.
grey-area 4y ago
I would say lack of appropriate indexing can be more important. Adding an index for a query can take it from seconds to me for the average response.

sigmonsays 4y ago

from my experience, every performance issue is the database when your programming language makes better choices.

rubyist5eva 4y ago

Another trap people fall into is doing joins in their application instead of in the database. I see this all the time where people will query some table with a condition, map the ids from the result and then query another table (which also joins the first table) and then filters by the id of the first query. Or just doing massive aggregates in ruby instead of having the database do it and just spitting out the result. A little bit of knowledge about SQL and how a relational database works goes a long way.

Rapzid 4y ago

It also wraps every query in an explicit transaction unnecessarily causing locks to be held while waiting for the commit from the server which got held up behind some cpu intensive block in separate thread (prob some other request deserializing and hydrating those 100s of entities you mentioned). Side note: I really dislike Rails.

jemmyw 4y ago
> It also wraps every query in an explicit transaction unnecessarily causing locks to be held while waiting for the commit from the server no it doesn't. It only wraps updates in transactions, and a single line update would have an implicit transaction at the database anyway.
- Rapzid 4y ago
  It would have an implicit transaction, wouldn't it? Think about what we both wrote here (sans the weak man argument, of course I'm talking about updates).
  - jemmyw 4y ago
    I see. Well you could have been more explicit in your original message, I don't think it was obvious at all that you were talking about updates. Rails does the right thing here in my opinion. Anything that happens between calling `.save` and it returning should be in the safe envelop of a transaction. It can't know that you're only going to emit a single UPDATE query in that transaction, plus the framework user expects any exception between calling save and it returning to roll back. There are outs if you need them. I've worked on a few reasonably high traffic and complex rails applications and I don't recall a time when the time between the last query and the commit caused an issue. Yes times when the transaction itself had got too large and that commit was taking awhile. That said, there were more of these types of locking issues in the project I worked on 10 years ago with MySQL and repeatable read isolation level than with PG since then which defaults to read committed.

grey-area 4y ago

I would say lack of appropriate indexing can be more important. Adding an index for a query can take it from seconds to me for the average response.