A simple example is, say, foreign keys. Trying to access the foreign key of an object by doing `book.user.id` does an additional query for the user table to get the ID. It's less known that the id is immediately available by just doing `book.user_id` instead.
I've spent time optimising things like text searches down from 2000+ queries to about ~4, and one of the more noticeable things to me isn't actually the number of joins, rather the SELECT's that take place. Many of these ORMs do a SELECT * unless you explicitly tell them to otherwise, and when dealing with large-ish datasets or on models that have large text fields this translates into significant time taken to serialise these attributes. So you can optimise the query and still have it take a long time until you realise that limiting the initial `SELECT` parameter is probably more efficient than limiting the number of joins.
I personally love tools like Miniprofiler [1] for this (though maybe there's something better today, it's been a while since I've worked on that type of thing). It's a constant and accessible way to keep an eye on what goes into each request, and I've caught many of those bad queries before they were problems by using it (eg: "WTF, why did it take 9 queries and 250ms to grab what looks to be a single row from a single table?!").
So without a large number of rows it can be hard to know what it will actually do.
https://docs.djangoproject.com/en/dev/topics/testing/tools/#...
Hibernate (on Java) at least optimizes this specific use-case. At first, accessing a lazy-loaded property-object will give you a "proxy" and you can access the ID without incurring a database load (since it knows that anyway). And when doing a query, the object won't be joined when requesting book.user.id unless it needs to be (like you have some other WHERE clause that requires an actual join on that row).
> Trying to access the foreign key of an object by doing `book.user.id` does an additional query for the user table to get the ID. It's less known that the id is immediately available by just doing `book.user_id` instead.
But that's not really unsuspecting. `book.user` is asking for the user table, `book.user_id` is not. Those two things are not identical even though they return the same value.
I don't think that's right. There is a user_id column in the book table, so why shouldn't there be book.user_id?
hibernate is then "the magic "environment where it just works"
I realize OO is out of fashion now but it's still true and it still works and I've been in a lots of projects where ORM was useful
Hmm.. Sounds like a bug. Why is this not the same value for a foreign key?
Now perhaps some ORM's prefer to be thinner, to provide more footguns via a leaky abstraction that mixes implementation details with the object mapping. I don't think those are good implementaations.
To me, it seems like the (hypothetical?) implementation you're talking about is much more leaky and footgun-y than the more straightforward ("thinner", in your words) version. In order for foo.user.id to not execute a new query, foo.user would have to return some sort of proxy object that only fetched the user row when you tried to access a field that hasn't been loaded. That's way more magic than the more obvious solution—which is to load the row when you access the related object—and could easily cause more problems than it solves in the long run when you need to debug very specific queries.
Furthermore, how is going out of your way to hide a field that exists in the database (user_id) not the definition of a leaky abstraction? What purpose does it serve to direct you through an unnecessary layer if all you need is the ID?
Or you can just consider user_id to be a reference pointer that is part of foo, while user.id is an attribute of user. Totally different things and I am glad that the distinction is there.
If it's a bug, sounds like a "wontfix" to me.
A similar concept called covered queries exists whereby you index a table by foreign key, and a few additional columns that you do not expect to use in join conditions, but you do expect to frequently retrieve. Depending on your database, requests for only columns in the index (some being the join condition, some subsequent columns being in the set of popular additional columns) means faster access to those popular columns only. In the context of ORMs, you would need to do something to avoid a default behavior of "select *" in order to exploit this index.
Not necessarily, it can be overridden: "id" is only the default for models that haven't explicitly been given a field with the "primary_key" kwarg (common on legacy tables where the primary key column might for example be "user_id").
The alias guaranteed to be the same value is ".pk", and I'm not sure what django does if you try to create a column named "pk" that isn't the primary key.
Django does provide relatively easy ways to get over the N+1 issue, though. If you do Book.objects.select_related('user'), only one query is made.
You still need to understand a minimum of SQL and databases, and usually those that complain about the ORM are the ones that expect it to be a "sufficiently advanced compiler", but it has matured so much that nowadays the developers consider a *bug* every time the answer to How do I do this query X? involves something along the lines of use .extra or raw sql.
Even though Django has come a long way, I greatly prefer ORMs like SQLAlchemy and Ecto that map more closely to the SQL query I'm trying to write.
`user` would be a property defined as a User object on the Book model, so accessing `book.user` will cause the framework to fetch the entire user model (even if we then only fetch the id).
On the other hand, `book.user_id` is the auto-generated database column, generated to make the above property definition possible. But since this `user_id` is directly defined on the book object, there is no need to query the user table.
SQL is readable (at least far more than 20m lasigna of boilerplate objects decorated with tons of annotations googled from internet where no one really know what they do) and you KNOW that if you have optimized the database structure (and filesystem, and network,... :D) and SQL statement you will get peak performance while with ORMs you are on constant hunt what else can you turn on while they are far too huge to read their code.
And they are becoming quite absurd after they "mature" and begin adding corner cases that no one has thought about when they were a starting project.
public interface UserDao {
@SqlUpdate("CREATE TABLE user (id INTEGER PRIMARY KEY, name VARCHAR)")
void createTable();
@SqlUpdate("INSERT INTO user(id, name) VALUES (?, ?)")
void insertPositional(int id, String name);
@SqlQuery("SELECT * FROM user ORDER BY name")
@RegisterBeanMapper(User.class)
List<User> listUsers();
}
This is great because it's explicit. No hidden queries. context
.update(User.USER)
.set(User.USER.NAME, userName)
.where(User.USER.ID.eq(userId))
.execute()For small queries with straight forward joins, a query builder is nice and readable.
But for larger, more complex queries, I found putting the query into its own file was best for readability.
And to me, SQL is the easiest language to learn and read of all, and while I understand doing basic marshalling of SQL records into objects is tedious, its really not hard at all, and it just saves so so much heartache down the road.
The exception might be very basic CRUD apps that are meant to be used by third parties and want to support multiple backends like mysql/postgres/whatever. There might be other exceptions as well where you are just trying to prototype/find market fit, but for a typical project that you know is going to be used in a real way, the risks just don't outweigh the benefits IMHO.
Some of the team griped a bit, but I absolutely think it was the right move in hindsight. At least once a year I would hear about an outage due to an ORM gone wild, and these outages were usually prolonged by the fact that things function, and then there is finger pointing between the DB/DBAs and the app developers, etc.
I'd love to have a tool that just generates an object type for a given SQL query's result rows, and a function signature for its query parameters.
For a similar purpose in Python I generally use psycopg with namedtuple resultsets; the namedtuple 'records' do what I need for the returned data and are reasonably efficient.
If you want to keep going forward with it, I'd recommend working on making it clearer what the examples do, and looking at sqlc for Go for inspiration. Of the existing versions of this people replied with, that one had the clearest API, or at least clearest explanation on its landing page.
We don't need ORM, we need OQM (object-query mapping)!
So now my pattern is to use for example ActiveModel in Ruby for the models, but not ActiveRecord for the persistence part.
Also, the big thing is you won't know how to translate to other ORMs if you don't know SQL. Eg once you know that you want an index, it's a matter of a web search to find out what the syntax is in your ORM. But if you just started with ORM, you might not realize that kind of thing is part of how it works.
plus it means you don't have to write all the dumb "select * from books where bookName = :bookName" code that obviously can be handled trivially by the ORM. You just use SQL the places where it makes sense.
the ORM hate always strikes me as a little misplaced because of this - you can always write SQL where it's appropriate, in any decent ORM. And you can write bad queries in SQL too. Obviously very complex queries are maybe better reserved for raw SQL but it seems like a lot of the hate comes from maybe less experienced engineers getting in over their head with complex work items because ORMs "make it easy", and that is going to happen with raw SQL too if you throw those same engineers at those work items.
ORMs aren't inherently that heavyweight, I see people complaining here about SQLAlchemy and as a Java developer I don't have performance concerns about hibernate. That sounds to me like a Python problem and a "this specific ORM isn't performant" problem, not ORMs being bad as a whole. And if you really want a "just load the data for me and do nothing else that incurs a performance hit" approach then you can use stateless objects and it's just a wrapper around the DB to load and transform the data for you and/or do a raw, whole-object update back to the DB.
The reason is that sure, for your first 10 basic select queries, the ORM saved you half an hour. Then you got to that complicated join and had to resort to looking up archane syntaxes and prototyping attribute quirks for an hour, when the junior guy got the whole thing written in 15 minutes of trial/stackoverflow/error in a SQL prompt.
Then, even when the "expert" did get it working, guess who is going to be the one looking at it again when trying to figure out production support issues? The junior guy, who is now clueless and has to spend two hours to figure out what this crazy ORM mess does here. The better alternative was just to have the SQL there ready to go so it is well understood and can be ran against the production database or a test database to reproduce the issue. No questions whatsoever.
I have seen this over and over again, more than a statistically relevant number of times.
Maybe we work in very different fields, but "basic select queries" makes up 90% of what I need to fetch from the database.
If I'm working on a forum and I want to load user 123, with all their posts, all the awards each post has, and the count of friends the user has, with Eloquent (Laravel's ORM), I could do:
`User::with('posts.awards')->withCount('friends')->findOrFail(123);`
That would return me a User model, with a collection "posts" containing a list of Post models, each with a collection "awards" of Award models, and a field `friends_count` with the number of friends. It would run three queries: one to fetch the user, one to fetch the posts, and one to fetch the awards. Depending on how I have configured my models, I can have things like dates automatically hydrated to DateTime objects.
Compare that to plain SQL queries; I would have to fetch the users, including manually writing the subquery for the friend count. Once I had those users I would then have to fetch the posts, and then again for the awards. If I want them in a hierarchy like the ORM example gives me, I then need to loop through each set of records and manually stitch them together. Not difficult, but super tedious.
Sure a complicated join is better done with as little magic as possible, but Eloquent exposes functions for adding subselects, joins, etc. in a way that just reads like SQL (and maps 1:1 underneath).
But the thing is, a lot of things aren't really on the hot path and optimizing them isn't worth a ton of time. Like yeah so what if this web request that only gets used 2% of the time makes 5 extra database requests that it shouldn't, on a single data item. Not gonna tank the overall program.
I guess I'd accept that it's important to be aware of what you're pulling, regardless of whether that's automagically when a proxy object sees it needs to be lazy loaded, or explicitly in a query. Throwing junior developers on performance-critical paths is going to be a problem anywhere and on any DB access layer.
Some years ago, one morning I gave a co-worker a recommendation on how to improve a loop that was unnecessarily hitting database through the Django ORM. He committed the fix that afternoon. Barely an hour later I accidentally reintroduced the exact same slowdown in the exact same loop when adding a different piece of data to it.
Soooo yeah, ORMs can be so simplistic it's too easy to do by accident even if you know exactly what's going on under the hood.
And like I said above, usually you don't just select out the entire contents of a table and handle it in the application, so I reflexively did the wrong thing as well, because of how easy it was to do with Django's ORM.
``` [book.author.name for book in Book.objects.all()] ```
The way I sped it up was to call `.values()` on the query, which serializes the data into a dict and prevented me from accidentally making subsueqent calls.
PS: Indent by 4 spaces for code formatting.
For instance, Django has prefetch_related and select_related. At almost every Django conference, there's a talk on this topic because it's so important and very underused/overlooked. But these are provided methods of the ORM.
Aside from that, there are wonderful introspection tools such as django-debug-toolbar to view the raw SQL and its performance.
It can be argued that if a solution written in Django hasn't had its database performance introspected with for instance django-debug-toolbar, then the solution isn't done. This is a small step with big rewards.
This introspection can easily identify where raw SQL is useful. But apply it late in process: As a project matures, the costs of converting some queries into raw/hybrid SQL are lower, as the statement is less likely to change. But keep these SQL statements in the models and managers, don't let them spill into views, template tags etc.
I've got a couple of web handlers that after quite a bit of work I can legitimately claim will run on the microsecond timeframe... but they're the exception. Generally even a single DB hit across the network, even on the same system, is going to blow right past the web framework's time you're using.
On that note, using this sort of metric, japronto's claimed results smell funny. Even with a 4GHz processor, getting 1,214,440 requests per second on a single core is ~3300 cycles per request. That's less than one cycle per byte in the HTTP request for a reasonable request (with no blocking on any sort of memory request), and that's not counting the TCP itself, any response, or the overhead of switching back and forth between C and Python. I can't see how this is possible without a huge degree of corner cutting; just validating that what you've received is a legal HTTP request, correctly encoded, decoding the fields, etc. is going to eat into that pretty fast, even with all the SSE instructions you may be able to throw at it. (And to emphasize, I'm not saying this is "impossible", just that it requires a lot of corner cutting. I've also got a "web server" out in the wild that handles "web requests" blazingly fast... because it basically ignores the entire web request and shovels out a hard-coded response. Very fast. Not a very good web server.)
This seems like a false dichotomy. Avoiding obvious performance mistakes such as the ones you mentioned does not require additional focus that would detract from general building. It just requires that you know what you are doing.
If you are the type of person who makes said mistakes, its unlikely you would ever go back and fix them by "focusing" on performance because the issue is simply that you don't know what you don't know. Likely someone else will come along in future and point out your mistakes to you.
Optimization that actually hinders you from building and requires focus is at the very margins and almost no one is going to those levels in typical "application" code.
It feels like a sensible advice but "optimization", if ever possible, can only get you so far until you need a costly refactoring or rewrite in my experience.
As projects can be very different in context, it is all about what makes a minimal implementation "viable".
Completely agree and this has been my experience ae well. To this I'll also add, inadequate thought put into data modelling. One would have to think lesser about query performance or cost of overfetching if data is modelled around the needs of the system it would serve instead of just modelling real life entries and their relationships as is, straight onto the database.
Sure, the framework matters at very large scale and the benefits from optimizing the framework become large when you're doing millions of requests a second over many thousands of servers because it can help reduce baseline cost of running the service.
But I agree with the author's main point which seems to be that framework performance is pretty meaningless when comparing frameworks if you're just starting on a new project. Focus on making a product people wanna actually use first. If you're lucky enough to get to scale you can work about optimizing it then.