What is difference in using find_by_sql and our normal rails query method?

I have a small doubt regarding the execution time for MySQL query in my rails application.

I have written a query like

  @claims = Claim.joins('left join drugs on claims.ndc = drugs.ndcupchri left join pharmacies on claims.nabp = pharmacies.nabp').select('claims.*, drugs.*, pharmacies.* ').where('claims.member_id = '218').group(:ndc)

and I wrote a the same query using find_by_sql

   @sql = "SELECT claims.*, drugs.开发者_StackOverflow中文版*, pharmacies.* FROM `claims` 
          left join drugs on claims.ndc = drugs.ndcupchri 
          left join pharmacies on claims.nabp = pharmacies.nabp 
          WHERE (claims.member_id = '218') GROUP BY ndc "

  @claims = Claim.find_by_sql(@sql)

Sometimes I see that using find_by_sql the execution time of the query is pretty fast when compared to the other. Is this correct.

Which one of the above pattern is a better accepted method, when it comes to performance oriented. Please share your ideas.

Currently am using Rails 3.0.7

The first query is using Arel. It provides chainability and scope. So you if you want to filter more data you can do something like :

@claims.where(:id => 5)

The above thing is not possible with find_by_sql . Also Arel do optimization of sql queries formed using Arel methods. You can search for other advantages of Arel on google .In rails 3.1 there will be caching of database query results (correct me if I am wrong)

If you are quite good in sql then find_by_sql will always be more optimize as it execute queries directly on database . But then we always want a balance in productivity and performance .

in your case the joins clause is excessive, as you are not imposing any WHERE conditions on join tables, only on claim table and as you are calling Claim.find_by_sql, only Claim objects will be populated. So your query boils down to

Claim.where(:member_id => 218)

It will be faster than both your queries, as no joins are performed.

In general only some specific queries should be done via find_by_sql. Rails Arel interface is flexible enough to implement most of commonly used queries and it's a lot easier to debug that huge SQL statements.