re: 📊 Languages' Popularity on DEV VIEW POST

re: So what I'm seeing is that there is a deep trade off with an ORM? Sacrificing scalability, performance, and efficiency for ease of use and convenie...

The class then translates, to the best of its ability, SQL queries that relate to that table. But first, must connect to them, which could have been done much more quickly by straight-up SQL and much earlier in the process.

I feel this is not part of the impedence mismatch. ORMs are usually backed by connection pools and lazy connections are definitely a pro (most of the times you don't want your code to connect to the DB until the data is asked for)

Then, as with all processes that are meant to be easy-to-use, the SQLAlchemy class uses an over-generalized method of connecting, aggregating, and updating data from the db, before then reorganizing it into the form we expect when the round trip completes

Sure, there's some overhead, because data has to be translated and converted and objects have to be created and so on

A common (and easily solvable) issue with ORMs is the N+1 which roughly translates to something like select all users, and for each user select their comments, this can be solved by what ORMs call eager loading which translates to something like select all users, join with the comments table and load the comments at the same time

Another common issue with ORMs is the update example Dian gives. If you have to update a single column in bulk the naive and very slow way to do it is to load the objects in memory, iterate through them, change the column and save the object. A better way to do it, in SQLAlchemy, is through bulk methods which are much faster (but not as fast as using the core which basically generates a single SQL UPDATE). In Rails you probably have to use something like activerecord-import

Same goes for the combination of the two issues above (N+1 update? don't know the name :D)

I've just never thought about what was going on under the hood with an ORM and want to make sure I understand what is being explained to me here :D


code of conduct - report abuse