Is Your ORM Making Your Site Slow?


ORM Are No Excuse For Not Knowing What SQL Is Hitting The DB:

Some developers love ORMs and never touch SQL. Others love SQL and would hate using ORMs because it abstracts them a bit. And then there are those who fall somewhere in the middle. Personally for me it depends on the requirements. I lean on ORMs a lot but bypass them when it makes sense for efficiency sake.

But I am not here to argue the use of ORMs or not. What I am saying is if you do rely on an ORM you still need to know what SQL that ORM is generating and how it is affecting the health and latency of your Database.

Warning: Some ORM frameworks documentation does NOT match the documentation. For example Django has some... quirks. One specific one I found is iexact. This command should do a “case insensitive” look up. The documentation says it will translate to the following:

WHERE firstname = 'emil';

But notice with that query if the value of firstname was “eMiL” or any variation with uppercase letters in it the query will not find it.

In reality I found the query to actually look alot like the following:

WHERE UPPER(firstname) = UPPER('emil');

That means it has to convert every firstname to uppercase on every record in the table every time you run the query. That is a lot of extra CPU cycles.

Now this is not to throw shade on Django, nor any open source ORM. Maintaining docs is hard and mistakes get made. It is the dev team as a whole’s job to dig in and really understand the SQL generated by the ORM and its effects on the DB and sometimes the docs are just plain wrong so make sure you peek under the hood. Try logging the SQL statements your ORM creates. Turn on slow query logs on the DB and see what pops up. There are plenty of other options as well. Just make sure you and your team are being mindful of the final outcome and how it affects your tech products health and user experience as a whole.

Download my free e-book 20 Things You Can Do Today To Save Money On Your Amazon Web Services Bill