SQL Performance – Options When ORM-Generated SQL Needs Tuning

djangoormperformancesql

I am starting working on a web project using django. While researching whether to use Sqlalchemy or raw sql when django orm is not sufficient which is also a question I asked here
Raw Sql vs SqlAlchemy when Django ORM is not enough

One question bugged me.

As the load on the website increases and we find out that sql queries ran by ORM needs tuning. But we have no control on how does an ORM executes queries. In that case we have to use raw sql queries because they give us the best control.

But if we start using raw sql queries, all the benefits of using an ORM in first place are gone. We are stuck with both sql and orm code which will surely mess up the reading and maintainability of the code.

It feels to me that we are opting for easy code in lieu of many problems later. No doubt our initial code will be fast and easy maintainable but this can cause later problems.

I would like to know thoughts of others on it. I am not starting a question comparing orms and sql but I would like to know what are the options when we have created web app using ORM and comes the situation when its clear that ORM is not supporting our cause?

Best Answer

This is pretty straightforward. Two solutions :

  1. Tune the ORM. This is the application of separation of concerns and the cleaner solution in the long run. Anyway this has drawbacks.
    • The ORM codebase could be hard to tweak.
    • This could be hard to achieve in a given deadline.
    • The tweak has to be ported to other versions of the ORM, or continuous obolescance will arise pretty quickly. Working with people behind the ORM is the way to go, but some company policies can prevent it.
    • The ORM can be impossible to modify that way. It can be a proprietary software or the company organisation could prevent you from doing this.
  2. Use manually done queries mapped by the ORM.
    • This is easy and fast to do. get the job done.
    • Has to be unique, or you codebase will be quickly doomed with such hacks everywhere.
    • Has to be packed in an abstraction. Make it look like standard use of the ORM. This limits the dirtyness to a small portion of code.
    • The ORM may not provide this function (and if it the case, you probably choosed a crappy one).

Depending on the conext and deadlines, solution 2 is a real world solution, even if it not clean. Technical debt can be made in a project with precaution to limit it to a small portion of code and justified by some extra technical constraints.

Related Topic