Finders are great but be careful

Finders are very pleasant to use, enable you to write readable code and they don’t require in-depth SQL knowledge. But the nice high level abstraction come with a computational cost.

Follow these rules of thumb:

1. Retrieve only the information that you need. A lot of execution time can be wasted by running selects for data that is not really needed. When using the various finders make sure to provide the right options to select only the fields required (:select), and if you only need a numbered subset of records from the resultset, opportunely specify a limit (with the :limit and :offset options).


2. Don't kill your database with too many queries, use eager loading of associations through the include option:
   Avoid dynamic finders like MyModel.find_by_*. While using something like User.find_by_username is very readable and easy, it also can cost you a lot. In fact, ActiveRecord dynamically generates these methods within method_missing and this can be quite slow. In fact, once the method is defined and invoked, the mapping with the model attribute (username in our example) is ultimately achieved through a select query which is built before being sent to the database. Using MyModel.find_by_sql directly, or even MyModel.find, is much more efficient.


3. Be sure to use MyModel.find_by_sql whenever you need to run an optimized SQL query. Needless to say, even if the final SQL statement ends up being the same, find_by_sql is more efficient than the equivalent find (no need to build the actual SQL string from the various option passed to the method). If you are building a plugin that needs to be cross-platform though, verify that the SQL queries will run on all Rails supported databases, or just use find instead. In general, using find is more readable and leads to better maintainable code, so before starting to fill your application with find_by_sql, do some profiling and individuate slow queries which may need to be customized and optimized manually.