Database, Product Management, Technology, Web Development

SQL vs ORM – argumentum ad populum

Object-relational mapping (ORM) serves a purpose. It can be useful for making simple and standard CRUD (create, read, update, delete) functions easy to manage. But when it comes to high performance when querying relational databases, there’s a language designed for this purpose. It’s called SQL.

SQL is an extremely powerful language, originally based upon relational algebra and tuple relational calculus. I believe that many newer developers and perhaps even some senior developers may go through their whole lives without ever needing to do complex/heavy data queries. Those who do know SQL beyond an intermediate level will know that querying databases with SQL is orders of magnitude faster than using ORM. The inherent problem with ORM’s is that generally they use an RBAR (Row By Agonising Row) or Collection style approach. This is a great example of a mechanism that has gained popularity due to it’s “ease of use” but in practice is a massive hindrance to performance. Just because it’s popular doesn’t mean it’s good. ORM is simpler to work with and maintain, but far less efficient than the batch/functional/mathematical approach SQL uses. Stored procedures, functions and even triggers (I can hear the gasps of shock and horror) are incredibly powerful and highly performant and efficient.

There is a trade-off to SQL of course, and that is maintainability. For large companies their main concern is numbers. They want to use environments that they can easily scale up their developer resources if need be, and to do so they often want to use the most commonly known environments. Most developers are comfortable with the latest FOTM (“flavour of the month”) frameworks which have inbuilt ORM’s of various types, and so encourage their use. The problem is when it comes to queries that are beyond basic or intermediate complexity, ORM’s performance is poor, and many developers aren’t familiar with using SQL for advanced/complex queries, mapping and solutions.

The “middle ground” is to use ORM’s for the basic work, but to use SQL for the heavier lifting to prevent bottlenecks. There’s a trend in development for everyone to jump on board the latest fads, frameworks and trends – a lot of these can be fairly heavy and bloated providing everything including the kitchen sink if needed, and that’s all very well, but ORM’s in particular are one of those “trends” that are overrated and probably have caused millions/billions of dollars of damage to companies products, expectations, deadlines and bottom lines.

One of the other considerations is that advanced/heavy SQL is fairly complex. So there may even be an “IQ gate” of sorts – meaning that not everyone can do it! This is testament to SQL developers or mathematicians that are able to work in these areas – they are extremely valuable and should be paid accordingly.

Leave a Reply

Your email address will not be published. Required fields are marked *