ActiveRecord Custom Query vs find_by_sql loading

Question

I have a Custom Query that look like this

self.account.websites.find(:all,:joins => [:group_websites => {:group => :users}],:conditions=>["users.id =?",self])

where self is a User Object

I manage to generate the equivalent SQL for same

Here how it look

sql = "select * from websites INNER JOIN group_websites on group_websites.website_id = websites.id INNER JOIN groups on groups.id = group_websites.group_id INNER JOIN group_users ON (groups.id = group_users.group_id) INNER JOIN users on (users.id = group_users.user_id) where (websites.account_id = #{account_id} AND (users.id = #{user_id}))"

With the decent understanding of SQL and ActiveRecord I assumed that(which most would agree on) the result obtained from above query might take a longer time as compare to result obtained from find_by_sql(sql) one.

But Surprisingly

When I ran the above two I found the ActiveRecord custom Query leading the way from ActiveRecord "find_by_sql" in term of load time here are the test result

ActiveRecord Custom Query load time

Website Load (0.9ms)

Website Columns(1.0ms)

find_by_sql load time

Website Load (1.3ms)

Website Columns(1.0ms)

I repeated the test again an again and the result still the came out the same(with Custom Query winning the battle)

I know the difference aren't that big but still I just cant figure out why a normal find_by_sql query is slower than Custom Query

Can Anyone Share a light on this.

Thanks Anyway

Regards Viren Negi

Answer 1

Well, the reason is probably quite simple - with custom SQL, the SQL query is sent immediately to db server for execution. Remember that Ruby is an interpreted language, therefore Rails generates a new SQL query based on the ORM meta language you have used before it can be sent to the actual db server for execution. I would say additional 0.1 ms is the time taken by framework to generate the query.

Answer 2

With the find case, the query is parameterized; this means the database can cache the query plan and will not need to parse and compile the query again.

With the find_by_sql case the entire query is passed to the database as a string. This means there is no caching that the database can do on the structure of the query, and it needs to be parsed and compiled on each occasion.

I think you can test this: try find_by_sql in this way (parameterized):

User.find_by_sql(["select * from websites INNER JOIN group_websites on group_websites.website_id = websites.id INNER JOIN groups on groups.id = group_websites.group_id INNER JOIN group_users ON (groups.id = group_users.group_id) INNER JOIN users on (users.id = group_users.user_id) where (websites.account_id = ? AND (users.id = ?))", account_id, users.id])