Arbitrary SQL ordering

I ran across an issue the other day where I had 4 records from the database that were being displayed in order of creation date, but I wanted them displayed in an arbitrary order - not alphabetical by name or title, or chronological, or sorted by id.

Before, I probably would have used Ruby to loop through the records and build an array by comparing ids. Now, though, I know better than to use Ruby for this type of thing, because it’s faster and better for memory to do it in SQL if possible (which I learned the hard way).

Turns out, querying using SQL looks a lot like what I would have written in Ruby anyhow:

SELECT * FROM foo ORDER BY
CASE
  WHEN id=67 THEN 1
  WHEN id=23 THEN 2
  WHEN id=1362 THEN 3
  WHEN id=24 THEN 4
END

Pretty cool! Thanks to cpjolicoeur on Github for this one.

Object overload! Careful of instantiating objects in Ruby

Wowzers, I just tackled a major memory issue in an app I’ve been developing, and it was nearly all due to rampant over-instantiation of ActiveRecord objects.

Here’s the TL;DR: Use SQL queries whenever possible to grab info from the database, rather than using Ruby code.

Now for the longer version. First, I knew I had a memory issue because my server was crashing and it was showing up the logs saying that memory could not be allocated. But I had to find out what the issue was first.

Oink to the rescue. It’s a handy little Ruby gem that will log how much memory and how many ActiveRecord objects are instantiated for each request.

I installed Oink, ran through a few requests that seemed kinda slow, and lo and behold, I found the problem: One of my requests was generating 1500 objects!

Here’s what was happening. I was looping through an array of IDs, and for each one I was calling

account.users.order(:id).select{ |user| user.id == id }.first

Don’t laugh. I have a baby at home, I was sleep deprived, I don’t know why I did it this way. But yes, this is instantiating all User objects in an account, and it’s doing it every single time it iterates through the loop. Hence, 1500 objects instantiated per request.

This was easily solved by a simple find_by_id with the id from the array.

That took me down to only one instance of each User object, which is still not fantastic. So, I ended up using one of my favorite ARel methods, pluck, to get just the attributes of the users that I need without actually creating the User objects.

That’s the ticket! From 1500 User objects to 0, this should significantly help with speed and memory usage. I ended up combing through the rest of the app and finding a few other spots where object instantiation was a bit out of control, though not nearly as bad as that one.

It became a kind of game - and in the end, I’d say I won.

Thanks to Engine Yard for the great tips. Lots more in their linked post.