Beware: this post gets a bit emotional at times. I wanted to share some thoughts about SQL, or rather my thoughts about thinking in this language. I made an unexpected comeback after a few unsuccessful cases with ActiveRecord.



I realise some of you might just stop reading this right after you see the three dreaded letters, one next to another, but let me explain why I did it - and also take a look at two gems that will help you combine thinking in SQL and writing in Ruby. I’m proud to say I made one of them myself. But first, the background...

Switching from SQL to Ruby on Rails

I’m pissed off!

I was a database developer for several years before I started to write in Ruby on Rails but, other than SQL and PG/plSQL, I didn’t know any other language well. In my job, I spent days searching for slow queries, analysing and finding solutions to make them faster. After that, I had to make a deal with Java developers to change current queries into faster ones. Sometimes I succeeded, sometimes I didn’t.

I decided this had to end. I couldn’t take it anymore. I took to writing web applications myself and using better queries from the beginning. I found Ruby as a scripting language and then Ruby on Rails as a web framework: it was easy to learn, with clean code that enabled me to develop applications fast.

No SQL in RoR? A matter of taste

Then I found out that if I wanted to write clean code, legible for other devs, I couldn’t use my beloved SQL. They said: "SQL looks awful” and “if you write SQL, you can’t change the database…”. I didn’t even try to mention the views or stored procedures to my more experienced colleagues. The topic was forbidden. I had to use ActiveRecord.

So, I started to learn to think in AR. After two years, I said to myself once again: no more! I will write my queries in SQL anew. Not all of them of course, but sometimes there is just no other way to get a fast query with the same result. Do you want to know why?

Speeding up queries with SQL (again)

Take a look at the query (this is simplified but it represents the idea) that I was asked to improve:

What can I say? distinct count(distinct ) doesn’t look good.

Then, I tried:

Caching probably involved nothing better.

That was the moment when I started searching for more information. The first answer to “postgresql count distinct” in Google was found on Stack Overflow.

A simple check:

The result was the same, but it performed 3 times faster. “What is going on here?”, I thought. I can’t write it in AR in a clean way, so someone will say: “This is poor code. SQL? Seriously? Make it better!”

Now however, I will reply: “But look how much faster it is”.

Fast searching about “COUNT DISTINCT” in PostgreSQL gave me:

Use Subqueries to Count Distinct 50x Faster

and related:

Getting Count of Distinct Elements, Per Group, in PostgreSQL

I know the query above is not exactly the same, but which of the best performing solutions can you write in pure AR? Anyone?

I came up with a solution to combine “SQL thinking” with coding in Ruby by creating a gem which will allow me to write SQL code and avoid littering the Ruby code with SQL strings. In short, it will use .sql.erb templates. It makes working with pure SQL easier. There’s also another gem solving this problem, created by Tyler Robers, with a slightly different implementation. Now you can write SQL in the template and use it in your app without bloating the Ruby code. There is a syntax highlighting for SQL as well (at least in my VIM).

Of course you might well tell me to use Arel for that, but the tools are not a case of one-size-fits-all. I’m not into Arel for two reasons: it is longer, and it is illegible to me. Plus, it gives you the trigger to find solutions of your own, such as the gem mentioned above :).

Conclusion

I forgot how to think in SQL, but sometimes you have to fight your amnesia. I’m going back to thinking in SQL. AGAIN!!

What do you say to that, Rails community? Would you go back to thinking SQL? Or are you total haters who want to curse me for life?

Let me know what your experiences with SQL have been like in the comments!