Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rails 3 adding database name to table makes includes default to eager load

Sources:

  • http://emphaticsolutions.com/2009/11/23/has_many_through_across_databases.html
  • http://blog.arkency.com/2013/12/rails4-preloading/

My project is moving towards having multiple databases (currently on the same server), and I would like to be able to join between these databases. In order to do this, I need to add the database name to the table prefix like so:

class FirstBase < ActiveRecord::Base
    def self.table_name_prefix
        "DBNAME.t_"
    end

    establish_connection :firstdb
end

class User < FirstBase
    has_many :user_roles
end

class UserRole < FirstBase
    belongs_to :user
end

Adding the table name prefix seems to impact the default behavior of includes on the same query, even within the same database. Consider User.includes(:user_roles).first

Without table name prefix:

User Load (67.1ms) SELECT t_users.* FROM t_users LIMIT 1 UserRole Load (84.5ms) SELECT t_user_roles.* FROM t_user_roles WHERE t_user_roles.user_id IN (1)

With table name prefix:

SQL (76.8ms) SELECT DISTINCT DBNAME.t_users.id FROM DBNAME.t_users LEFT OUTER JOIN DBNAME.t_user_roles ON DBNAME.t_user_roles.user_id = DBNAME.t_users.id LIMIT 1

SQL (66.4ms) SELECT DBNAME.t_users.id AS t0_r0,DBNAME.t_users.email AS t0_r1, DBNAME.t_user_roles.id AS t1_r0, DBNAME.t_user_roles.user_id AS t1_r1 FROM DBNAME.t_users LEFT OUTER JOIN DBNAME.t_user_roles ON DBNAME.t_user_roles.user_id = DBNAME.t_users.id WHERE DBNAME.t_users.id IN (1)

In other words, the default behavior of calling includes has changed from preload to eager load.

Does anyone know why the default behavior is changing? There must be something about adding the database name that makes Rails think we must eager load, but I don't see why. I'm also surprised to see this as I imagine it's not unusual to add the database name in. I can brute-force fix this in our codebase by changing all includes to preload, but I would like to understand what is happening here. Is there a way to change the default behavior?

like image 656
raja Avatar asked May 05 '15 20:05

raja


People also ask

How eager loading works in Rails?

Eager loading lets you preload the associated data (authors) for all the posts from the database, improves the overall performance by reducing the number of queries, and provides you with the data that you want to display in your views, but the only catch here is which one to use. Gotcha!

What is eager_ load in Rails?

Eager_load loads all columns in single query But Includes loads the data as per needed.. It works as preload in default case and works as eager_load in case of additional query in associated table.

What are active records in Rails?

Rails Active Records provide an interface and binding between the tables in a relational database and the Ruby program code that manipulates database records. Ruby method names are automatically generated from the field names of database tables.


1 Answers

The problem is that the table_name_prefix introduces a period. This confuses the logic that tries to determine if it should preload or eager load. This is a Rails 3 bug and has been resolved in Rails 4. If you need a specific behavior in Rails 3, you'll need to explicitly specify preload or eager_load as you have indicated in your question.

In ActiveRecord::Relation, exec_queries calls eager_loading? to decide if it should eager load. This calls references_eager_loaded_tables? which uses tables_in_string to try to find the table names in the SQL query that aren't a part of the joined tables:

# ActiveRecord::Relation#references_eager_loaded_tables?
(tables_in_string(to_sql) - joined_tables).any?

The tables_in_string method was flawed since it didn't always parse the SQL correctly. This code can be used to review what it thinks are the table names in the SQL query:

relation = User.includes(:user_roles)
relation.send(:tables_in_string, relation.to_sql)

With the DBNAME.t_ table name prefix, this will give ["DBNAME", "t_users"] as the table names, which is wrong. It should give ["DBNAME.t_users"].

A similar problem was documented in ActiveRecord query changing when a dot/period is in condition value. This led to changes in ActiveRecord::Relation that moved away from the use of tables_in_string when deciding whether to preload or eager load.

like image 166
cschroed Avatar answered Oct 12 '22 22:10

cschroed