Stop relying on your ORM and learn SQL
In modern development, and in particular with web frameworks such as Rails that offer and encourage extensive use of database ORM libraries, some developers skip learning SQL in favour of using ORMs. It is as if developers think they no longer need to know SQL when they’ve got an ORM. The truth is that we are not this fortunate. You should only use an ORM if you know exactly what it is generated by the ORM and you are sure that the generated SQL is as well performing as what you could have written by hand.
Let me go through the most common pitfall I see.
You have a blog listing a bunch of posts: title, content, author, date and the number of associated comments.
Typically one would do it like this in Rails:
<% for post in @posts %>
<h1><%= post.title %></h1>
<p><%= post.content %></p>
<p>
<%= post.author %> posted on <%= post.created_at %>
<%= post.comments.count %> comments
</p>
<% end %>
This looks simple enough, and it is — the issue here is the query for retrieving the number of comments associated (post.comments.count) is run for each blog post, although it could easily be included in the main SQL query fetching the posts with a join:
SELECT posts.*, count(comments.id) as comments_count
FROM "users"
INNER JOIN "comments" ON comments.post_id = posts.id
GROUP BY posts.id
Or in Rails’ ORM, ActiveRecord:
Post.all(
joins: :comments,
select: 'posts.*, count(comments.id) as comments_count',
group: 'posts.id',
)
For a typical blog an extra 20 count queries are not critical, but once your database reaches a certain size a noticeable, avoidable, delay will occur on that page. Something that could have been avoided with a basic understanding of SQL.
ORMs are indeed very useful to developers, however you should not neglect learning SQL because you have it.
Every time you use your ORM you should stop for a moment and think to yourself: “Can I be sure the ORM is generating the optimum query possible here?”
You might also like...