Rails ActiveRecord Order by a Function

Of course you can pass functions in the order clause through ActiveRecord finders:



In the above case a Foo has fiscal_year and fiscal_month columns unfortunately stored as VARCHAR(255) containing, for example "2008" and "7". While the sorting will work that query will be able to leverage an index on these columns only if the database used can create indices that include functions. I'm not sure which databases can do this. From a quick look at the SQL Server and MySQL documentation it doesn't appear that either of these databases can do this.

ActiveRecord Change SQL Server Query Timeout

Recently I had a rake task that was timing out while moving some data from a fact table into another data table. This task has to be run only when we get a new batch of data, do some ETL work, and then dump the data into the production database tables. I found out how to change the SQL Server query timeout on the Backyard Bamboo blog. Since I just wanted to turn off the timeout for the current session I ran some code like this:



The 0 turns the timeout off, otherwise the value is the number of seconds to set the timeout to. The default appears to be 30 seconds.

The timeout probably wouldn't occur if we add an index to a certain field on our fact table but this will get our data into production until we can make that change in the ETL process.

Rails Reporting, Raw SQL, and ActiveRecord

Most of the time when you dump some data out using Rails you are only dumping out a page of data at a time into an XHTML representaton. This is often done with some kind of pagination and it all works pretty well and speed doesn't become an issue. When doing reporting, however, you may be providing a much larger number of records to the user in the form of an Excel document or CSV file that the user will use third party software to do number crunching on. In this case the process of Rails reconstituting all of those model objects can really slow things down and you will likely need to resort to writing raw SQL and avoiding ActiveRecord. You don't, however, have to abandon ActiveRecord completely as it has some very helpful methods for constructing the SQL queries you will issue. Some of these methods are private but you can invoke them using the send method. Here is an example that uses ActiveRecord to append where and order clauses to the manually constructed sql statement:



A few notes...When you call select_all you are returned an array of hashes with the column names as keys. If you call execute you will get an array of arrays and you will have to use column positions to index into the array (probably a bit faster than select_all on producing that record set). As of Rails 2.0 the record set that select_all returns can be used as the :collection parameter in a partial. Lastly, in addition to the :add_conditions! and :add_order! methods there is a very handy :construct_finder_sql as well which accepts the same options your would pass to a regular finder.