I'm Jake Cahill. Lifetime Pythonista, web scraping and automation expert. Enjoy books. Love my wife, dog, and cat, and think AI and Julia are pretty nifty
Location
Maine, USA
Education
A Master's patient mentorship and insatiable curiosity
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 convenience?
That seems like a common trade off. The more abstracted we get from what we are doing, the more complex the plumbing becomes. I suppose that should be expected (although admittedly I didn't even think about that). Your doing everything twice. Invoking a class, which mirrors closely, but not exactly, a database table that handles data in a simpler, more straightforward way than the application layer code above it. 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. 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. Main issue being that in order to account for the large set of potential use cases, error handling, etc that all "convenience based" software code is designed to account for, a fairly straightforward SQL request becomes bloated, slow, and overly complex simply because it is, for lack of a better metaphor, a "Python-SQL Compiler" that takes Python code, translates it into the SQL that SQLite, MySQL, Postges, etc can understand, speaking "broken SQL" at best, then takes the response, translates it back to the code that the Python interpreter is expecting so that the application can use it. All when this could have been done with a standard SQL query that we don't know because ORMs are easier.
Is that about what this is saying? I'm not trying to sound sarcastic, 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.
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
:-)
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
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 convenience?
That seems like a common trade off. The more abstracted we get from what we are doing, the more complex the plumbing becomes. I suppose that should be expected (although admittedly I didn't even think about that). Your doing everything twice. Invoking a class, which mirrors closely, but not exactly, a database table that handles data in a simpler, more straightforward way than the application layer code above it. 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. 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. Main issue being that in order to account for the large set of potential use cases, error handling, etc that all "convenience based" software code is designed to account for, a fairly straightforward SQL request becomes bloated, slow, and overly complex simply because it is, for lack of a better metaphor, a "Python-SQL Compiler" that takes Python code, translates it into the SQL that SQLite, MySQL, Postges, etc can understand, speaking "broken SQL" at best, then takes the response, translates it back to the code that the Python interpreter is expecting so that the application can use it. All when this could have been done with a standard SQL query that we don't know because ORMs are easier.
Is that about what this is saying? I'm not trying to sound sarcastic, 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.
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)
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 loadingwhich translates to something like select all users, join with the comments table and load the comments at the same timeAnother 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)
:-)