News for hackers. Stuff that matters.
Re:12 requests per second: A realistic look at Python web fr... by imperio59 on Thursday February 18, 2021 @10:24PM (#26189246)
My experience doing perf optimizations in real world systems with many many people writing code to the same app is a lot of inefficiencies happen due to over fetching data, inefficiencies caused by naively using the ORM without understanding the underlying cost of the query, and lack of actual profiling to find where the actual bottlenecks are (usually people writing dumb code without realizing it's expensive).

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.

Re:12 requests per second: A realistic look at Python web fr... by stevenjohns on Friday February 19, 2021 @12:58AM (#26190216)
There is lots of truth to this. Some ORMs like Django perform joins in very unsuspecting ways.

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.

Re:12 requests per second: A realistic look at Python web fr... by gregmac on Friday February 19, 2021 @10:12AM (#26193987)
The most insidious part about misusing ORMs is it's often not visible for a while. Modern DBMSs on modern hardware are crazy fast, so when you have only a few tens or hundreds of thousand rows in your table, those inefficient and pointless ORM queries are just not noticeable because you still get sub-second response times. As your database grows, the site begins to gets slower and slower, but it's hard to distinguish between the real problem and "I guess we're just handling more requests per second".

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?!").

[1] https://miniprofiler.com/

Re:12 requests per second: A realistic look at Python web fr... by zzzeek on Friday February 19, 2021 @11:12AM (#26194610)
To be fair this is a problem inherent to databases in general. You can have hand written queries that perform badly due to structure or query frequency as well which are not apparent until the dataset grows. The ORM should make it easier to rectify such situations (eg drop in an eager loading directive) vs having to restructure hand-written routines for similar effects.
Re:12 requests per second: A realistic look at Python web fr... by magicalhippo on Friday February 19, 2021 @12:39PM (#26195610)
Indeed, even with query analyzer you might see say table scans instead of index scan just because the DB realizes just scanning the 100 rows you got is faster than trying to use an index.

So without a large number of rows it can be hard to know what it will actually do.

Re:12 requests per second: A realistic look at Python web fr... by fhood on Friday February 19, 2021 @11:33AM (#26194835)
We have everything hooked into lightstep. Makes it extremely easy to track down problematic operations.
Re:12 requests per second: A realistic look at Python web fr... by grw_ on Friday February 19, 2021 @05:57AM (#26191735)
I encountered this a few times and started adding tests that assert each handler only executes the expected number of queries (and no more). If the application code is modified such that this N+1 query pattern occurs the test will immediately fail and you go optimise the query, problem solved.

https://docs.djangoproject.com/en/dev/topics/testing/tools/#...

Re:12 requests per second: A realistic look at Python web fr... by drunkpotato on Friday February 19, 2021 @04:21PM (#26198191)
Or the person who changed the code then disables the test or sets N to 100,000,000 or something equally pleasurable to debug.
Re:12 requests per second: A realistic look at Python web fr... by paulmd on Friday February 19, 2021 @02:34AM (#26190608)
> 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.

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).

Re:12 requests per second: A realistic look at Python web fr... by airstrike on Friday February 19, 2021 @11:20AM (#26194677)
> ...in very unsuspecting ways

> 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.

Re:12 requests per second: A realistic look at Python web fr... by cztomsik on Friday February 19, 2021 @12:07PM (#26195209)
IIRC spark can optimize it away, and in OO there shouldn't be user_id
Re:12 requests per second: A realistic look at Python web fr... by airstrike on Friday February 19, 2021 @12:20PM (#26195369)
> in OO there shouldn't be user_id

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?

Re:12 requests per second: A realistic look at Python web fr... by cztomsik on Sunday February 21, 2021 @01:50PM (#26216003)
depends if you're doing active record (which I am not considering as typical ORM) or data mapper like hibernate where your entity is POJO with id and annotations but typically no foreign keys (just references to other model classes)

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

Re:12 requests per second: A realistic look at Python web fr... by bottled_poe on Friday February 19, 2021 @02:54AM (#26190682)
> 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.

Hmm.. Sounds like a bug. Why is this not the same value for a foreign key?

Re:12 requests per second: A realistic look at Python web fr... by m12k on Friday February 19, 2021 @03:27AM (#26190853)
Well, it is the same value, but the ORM doesn't handle book.user.id any differently than it does book.user.name where it isn't the same value, and thus the only option is to fetch the second table. So it's not a bug, it's really just the ORM being consistent in how it handles queries, thus missing out on a possible optimization in this special case, where a simpler query could have given the same result.
Re:12 requests per second: A realistic look at Python web fr... by lacksconfidence on Friday February 19, 2021 @06:08AM (#26191807)
No, that is clearly a bug. The ORM already has the value of book.id, that's how it knows how to fetch the right book. Performing extra queries is just poor implementation.
Re:12 requests per second: A realistic look at Python web fr... by m12k on Friday February 19, 2021 @06:29AM (#26191971)
The programmer also already had the value in book.user_id but still chose to ask the ORM to fetch all of .user so they could get .id from there instead. And they might then afterward call .name on it as well, and there would be no further queries, because the ORM has already been asked to fetch all fields of .user - so it might in fact have been sensible to fetch all of .user if so. The query builder cannot know whether all of it will be needed or not, because Python is not a compiled language, so there's no way to tell in advance when executing the book.user.id query that no further fields of .user will be needed, so it shouldn't actually do what it's been asked to do, to fetch the entire object, but rather only fetch .id which is available in a different way, so the whole query can be skipped. So yes, this is suboptimal usage, but only the programmer can know that, so it falls to them to optimize if they want to.
Re:12 requests per second: A realistic look at Python web fr... by lacksconfidence on Friday February 19, 2021 @07:28AM (#26192397)
Perhaps i'm a bit odd, but when I'm going to lean on an ORM to do things I expect it to actually do them. I expect that foo.user_id does not exist, because that representation has been transformed into an object. foo.user.id should be the only viable reference to the id. foo.user.id should return the value it already knows, any other property access i would expect will do the equiv of `select * from ...` if the object has not previously been populated.

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.

Re:12 requests per second: A realistic look at Python web fr... by RussianCow on Friday February 19, 2021 @04:27PM (#26198244)
> 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?

Re:12 requests per second: A realistic look at Python web fr... by rglullis on Friday February 19, 2021 @11:07AM (#26194561)
> I expect that foo.user_id does not exist, because that representation has been transformed into an object.

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.

Re:12 requests per second: A realistic look at Python web fr... by bottled_poe on Friday February 19, 2021 @07:48AM (#26192554)
I’m sure there are valid engineering reasons to do it this way. One that comes to mind is memory footprint in allocating the objects associate with foreign key references.
Re:12 requests per second: A realistic look at Python web fr... by jtdev on Friday February 19, 2021 @08:45AM (#26193041)
Wow! Who would have thought that using an ORM would create so much uncertainty in a codebase.
Re:12 requests per second: A realistic look at Python web fr... by zwily on Friday February 19, 2021 @10:25AM (#26194122)
Fancier ORMs will return a proxy for foo.user that doesn’t issue a query until the user asks for a property on it. And it can return user.id without querying.
Re:12 requests per second: A realistic look at Python web fr... by nerdponx on Friday February 19, 2021 @06:16AM (#26191873)
But it doesn't have book.user itself. Unless you want Django to construct some empty proxy object representing book.user for this one particular optimization.

If it's a bug, sounds like a "wontfix" to me.

Re:12 requests per second: A realistic look at Python web fr... by mrslave on Friday February 19, 2021 @03:18AM (#26190796)
It's not a bug, it is the same value. Only instead of using `first_table.foreign_id` to fetch the entire record from `second_table` only to use `second_table.id`, if you only need the identifier itself you already have it in `first_table`.

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.

Re:12 requests per second: A realistic look at Python web fr... by Izkata on Friday February 19, 2021 @08:57AM (#26193165)
> It's not a bug, it is the same value.

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.

Re:12 requests per second: A realistic look at Python web fr... by ehutch79 on Friday February 19, 2021 @09:17AM (#26193372)
If your tables don't use id, or are doing something strange, YOU wrote that code. You should know it's going to be a thing you need to deal with, because YOU did something unusual.
Re:12 requests per second: A realistic look at Python web fr... by Izkata on Friday February 19, 2021 @08:55PM (#26200702)
Note that I gave the example of "legacy" tables. On long-lasting codebases there's a high likelihood that "you" didn't write that code and aren't aware of everything Django's doing under the hood.
Re:12 requests per second: A realistic look at Python web fr... by rglullis on Friday February 19, 2021 @03:17AM (#26190790)
Because when you are acessing .user you are asking for all its properties, not just the id.

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.

Re:12 requests per second: A realistic look at Python web fr... by Hamuko on Friday February 19, 2021 @06:13AM (#26191846)
`QuerySet.select_related()` and `QuerySet.prefetch_related()` are the bread and butter of Django query optimisation. I think most of the time that I've noticed a performance issue in our code, it's been easily fixed with one of those.
Re:12 requests per second: A realistic look at Python web fr... by rglullis on Friday February 19, 2021 @06:29AM (#26191969)
Django's ORM gets a lot of flak, but I don't remember the last time I had complex queries that I could not do with it.

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.

Re:12 requests per second: A realistic look at Python web fr... by RussianCow on Friday February 19, 2021 @04:36PM (#26198328)
This is true, though to be fair to the critics, the syntax through which you express these complex queries is often clunky and unintuitive. For example, I need to re-read the documentation every time I use the annotation API because it's generally not obvious how to use it, and I've run into a few edge cases where you need extra code/syntax just to deal with its nuances and ambiguities.

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.

Re:12 requests per second: A realistic look at Python web fr... by radicalism on Friday February 19, 2021 @03:16AM (#26190785)
It would return the same value, but the approach to obtain it would be different.

`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.

Re:12 requests per second: A realistic look at Python web fr... by bcrosby95 on Friday February 19, 2021 @11:40AM (#26194900)
If you're selecting tons of data when you SELECT * you might also have a god object. I prefer to have my model be a bit more split up by use rather than being full of random stuff. E.g. a customer_address table rather than stuffing all that data into customer, even if they only have a single address.
Re:12 requests per second: A realistic look at Python web fr... by robertlagrant on Friday February 19, 2021 @02:25PM (#26196936)
I'm pretty sure Django has an active record ORM, which are generally a bit rubbish in terms of performance. A unit of work ORM such as SQLAlchemy seems to generate much better queries.
Re:12 requests per second: A realistic look at Python web fr... by divbzero on Thursday February 18, 2021 @10:56PM (#26189497)
I increasingly lean towards plain SQL over ORMs. It requires greater familiarity with SQL but I prefer that over greater familiarity with ORM-specific syntax that doesn’t translate across frameworks or languages. In addition, you can prototype new queries and profile existing queries in the database and copy-paste directly into your code.
Re:12 requests per second: A realistic look at Python web fr... by acdha on Friday February 19, 2021 @12:17AM (#26190029)
I favor a hybrid approach: use the Django ORM to define models, do migrations, auto generate the admin, etc. but don’t be shy about using the extension points (extra, raw, cursors) to put in an optimized query for a hotspot. You can get pretty far using the ORM but it’s really valuable to be able to be comfortable dropping down for things like reports or bulk processing.
Re:12 requests per second: A realistic look at Python web fr... by Jaxtek on Friday February 19, 2021 @02:03AM (#26190494)
I use ORM for Django's auth system alone.I write all other REST API queries in plain SQL.
Re:12 requests per second: A realistic look at Python web fr... by stiray on Friday February 19, 2021 @04:23AM (#26191159)
I agree with you. ORMs are at the end "converting" complexity of SQL capabilities into objects. It is perfectly normal that in best situation they will get at least as complex as SQLs but with features hidden behind annotations or be less efficient.

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.

Re:12 requests per second: A realistic look at Python web fr... by fn1 on Friday February 19, 2021 @01:31AM (#26190366)
In the java-world there are libraries like JDBI, which makes it possible to write interfaces with SQL-annotations and have serialization, connection setup etc. done for you:

    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.
Re:12 requests per second: A realistic look at Python web fr... by shock-value on Friday February 19, 2021 @01:51AM (#26190442)
Another great option in Java is jOOQ, which lets you write type-safe and potentially composable queries such as:

  context
    .update(User.USER)
    .set(User.USER.NAME, userName)
    .where(User.USER.ID.eq(userId))
    .execute()
Re:12 requests per second: A realistic look at Python web fr... by alex-lx on Friday February 19, 2021 @04:09AM (#26191085)
jOOQ and its DSL is good, however IMO it's more readable using raw SQL (by using `context.fetchInto` and its variants) than to using DSL when deal with complex query.
Re:12 requests per second: A realistic look at Python web fr... by jwdunne on Friday February 19, 2021 @04:41AM (#26191264)
I’ve noticed this in other contexts too.

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.

Re:12 requests per second: A realistic look at Python web fr... by lukaseder on Friday February 19, 2021 @03:26PM (#26197637)
Why not just create views and query those with jOOQ, then?
Re:12 requests per second: A realistic look at Python web fr... by alex-lx on Friday February 19, 2021 @08:33PM (#26200529)
hi, @lukaseder, Thank you for creating jOOQ. I don't have much experience on views, and some called `Best Practice` forbid to use that because it's hard to maintain. What do you think about that.
Re:12 requests per second: A realistic look at Python web fr... by JulianWasTaken on Friday February 19, 2021 @05:30AM (#26191538)
In Python this would look like sqlalchemy's query layer (which is great).
Re:12 requests per second: A realistic look at Python web fr... by kevstev on Friday February 19, 2021 @01:08PM (#26195976)
Yeah in my last job, I used my manager power to overrule on using an ORM. In every company I have ever been in, some major outage has occurred due to a seemingly innocuous change causing a DB operation to go from O(N) to O(N^2)+ and its really hard to track down because things seem to work fine in a dev environment, but then can't scale.

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.

Re:12 requests per second: A realistic look at Python web fr... by zem on Thursday February 18, 2021 @11:39PM (#26189825)
for me the value of an ORM is not so much query synthesis as serialisation of object fields to db columns and vice versa
Re:12 requests per second: A realistic look at Python web fr... by jtolmar on Friday February 19, 2021 @01:07AM (#26190256)
Same here.

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.

Re:12 requests per second: A realistic look at Python web fr... by reader_mode on Friday February 19, 2021 @01:29AM (#26190354)
F# has this + static SQL query string checking at compile time using type providers
Re:12 requests per second: A realistic look at Python web fr... by PeterisP on Friday February 19, 2021 @09:49AM (#26193710)
"just generates an object type for a given SQL query's result rows"

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.

Re:12 requests per second: A realistic look at Python web fr... by aabbcc1241 on Sunday February 21, 2021 @01:18AM (#26211288)
Just hack that for Typescript over this weekend (: https://news.ycombinator.com/item?id=26211286
Re:12 requests per second: A realistic look at Python web fr... by jtolmar on Monday February 22, 2021 @12:36PM (#26227393)
Wow, that was fast! I'm impressed.

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.

Re:12 requests per second: A realistic look at Python web fr... by grosskur on Friday February 19, 2021 @02:17AM (#26190553)
sqlc can do this: https://sqlc.dev/
Re:12 requests per second: A realistic look at Python web fr... by jtolmar on Monday February 22, 2021 @12:32PM (#26227321)
This is the nicest one of these from the ones posted, I think it's the model to copy.
Re:12 requests per second: A realistic look at Python web fr... by branko_d on Friday February 19, 2021 @01:14AM (#26190289)
This!

We don't need ORM, we need OQM (object-query mapping)!

Re:12 requests per second: A realistic look at Python web fr... by jtdev on Friday February 19, 2021 @08:49AM (#26193088)
That’s a huge dependency to take on just for mapping and serializing, something that most languages provide in a standard library or base functionality of the language itself.
Re:12 requests per second: A realistic look at Python web fr... by specialp on Friday February 19, 2021 @11:22AM (#26194704)
I like ORMs for doing the things for me like making objects and converting dates and other data to DB ready form and vice versa. I do not like them querying. The premise with ORMs is that you should be able to query pretty much anything easily. The reality is that almost all of the time you are doing something you don't have many distinct queries. You have a few reads and writes that you have to do. Things that are easily done in SQL with full expressiveness and efficiency.

So now my pattern is to use for example ActiveModel in Ruby for the models, but not ActiveRecord for the persistence part.

Re:12 requests per second: A realistic look at Python web fr... by lordnacho on Friday February 19, 2021 @04:03AM (#26191042)
IMO both are required. I'm lucky in that I did a lot of plain SQL early on, and found ORMs later, but I think ORMs do cut out a lot of time for quick-and-dirty queries that end up not being the bottleneck. The problem arises once you find a bottleneck, you won't know how to optimize it if you haven't done a bit of SQL mucking about earlier.

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.

Re:12 requests per second: A realistic look at Python web fr... by kaba0 on Friday February 19, 2021 @06:17AM (#26191876)
Yeah, ORMs are specifically made for insert-heavy operations or very basic mapping of rows to objects. Analytical queries and the rest should be done with SQL.
Re:12 requests per second: A realistic look at Python web fr... by jtdev on Friday February 19, 2021 @08:53AM (#26193120)
I keep seeing some variation of: “ORMs are really intended for x” or “ORMs work best when y is true”. At some point we should take an honest look to determine if ORMs provide enough benefit to justify their existence. “I can talk to the database without knowing SQL” doesn’t cut it.
Re:12 requests per second: A realistic look at Python web fr... by kaba0 on Friday February 19, 2021 @12:33PM (#26195530)
Yes they do? They are basically available in every language, in some ecosystems there are multiple alternatives as well and there would not be that big of a market unless it is useful.
Re:12 requests per second: A realistic look at Python web fr... by paulmd on Friday February 19, 2021 @02:46AM (#26190651)
if that's your bag then you can still totally do that with an ORM, hibernate for example lets you just write a whole query in raw SQL while still getting all the benefits of eliding a bunch of boilerplate field copying, having "active" objects with ORM-level update/transaction management, etc.

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.

Re:12 requests per second: A realistic look at Python web fr... by arcbyte on Friday February 19, 2021 @09:29AM (#26193491)
I have never seen anyone, even the ORM "experts" in my jobs, save time with an ORM over what a junior engineer couldn't do faster with plain SQL.

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.

Re:12 requests per second: A realistic look at Python web fr... by deergomoo on Friday February 19, 2021 @10:19AM (#26194067)
> The reason is that sure, for your first 10 basic select queries, the ORM saved you half an hour

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).

Re:12 requests per second: A realistic look at Python web fr... by paulmd on Sunday February 21, 2021 @01:07AM (#26211232)
I do see the larger point though that ORM usage could still translate into problems elsewhere when you don't pull data you should, or you pull data you shouldn't, or pull data in a way you shouldn't.

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.

Re:12 requests per second: A realistic look at Python web fr... by alexchamberlain on Friday February 19, 2021 @03:22AM (#26190822)
We're gradually removing our ORM usage to avoid active objects. They look too much like VSTs, but subtly make foreign network calls when you least expect it, which makes it very hard to write effective, isolated tests or replace the database layer by something else, like a service call or a cache lookup.
Re:12 requests per second: A realistic look at Python web fr... by kaba0 on Friday February 19, 2021 @06:20AM (#26191906)
I’m sorry if it sounds harsh, but that is the mistake of the developers than. I think you would not allow anyone to write production code without knowing the language; it should be the same way with most libraries, especially ones having a big reach, like ORMs. Nonetheless, I have seen it countless of times in the team I worked in — so it is unfortunately really frequent.
Re:12 requests per second: A realistic look at Python web fr... by girvo on Friday February 19, 2021 @08:24AM (#26192871)
Plain SQL with a “hydration” system to objects is quite powerful. DataMapper, I believe is the term of art?
Re:12 requests per second: A realistic look at Python web fr... by jsmeaton on Thursday February 18, 2021 @11:08PM (#26189573)
https://github.com/charettes/django-seal Can help with these kinds of issues though I haven’t yet used it myself (I should!)
Re:12 requests per second: A realistic look at Python web fr... by wirrbel on Tuesday February 23, 2021 @04:59PM (#26243456)
SQLAlchemy expression language is cool stuff. not an ORM but also no string-templating ...
Re:12 requests per second: A realistic look at Python web fr... by Izkata on Thursday February 18, 2021 @10:52PM (#26189461)
> (usually people writing dumb code without realizing it's expensive)

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.

Re:12 requests per second: A realistic look at Python web fr... by tasn on Friday February 19, 2021 @12:58AM (#26190219)
That's a nice benefit of using async ORMs (not yet available in django), the db calls are explicit!
Re:12 requests per second: A realistic look at Python web fr... by 411111111111111 on Friday February 19, 2021 @01:55AM (#26190460)
You can also commit to always use .values() in django. That makes it error out when you access non-prefetched entities
Re:12 requests per second: A realistic look at Python web fr... by Demiurge on Friday February 19, 2021 @11:15AM (#26194634)
also, values is awesome for returning your models as dicts when that's all you need most of the time, avoiding all the overhead of objects
Re:12 requests per second: A realistic look at Python web fr... by solox3 on Thursday February 18, 2021 @11:38PM (#26189822)
Something about select_related? Please do share.
Re:12 requests per second: A realistic look at Python web fr... by Izkata on Friday February 19, 2021 @12:10AM (#26190001)
It wasn't anything that fancy, it's just the solution was something you usually try not to do so it just wasn't coming to mind for him. The data being looped over came from solr, and some of the fields were primary keys used in lookup tables in the database, for getting translated text. Instead of doing the lookup inline, load the entire table into a python dict before the loop (<100 rows for each of these tables) and do the lookup from the dict in the loop.

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.

Re:12 requests per second: A realistic look at Python web fr... by DangitBobby on Thursday February 18, 2021 @11:44PM (#26189862)
My guess is accessing a related field within a loop causing a database request per iteration, e.g.

``` [book.author.name for book in Book.objects.all()] ```

Re:12 requests per second: A realistic look at Python web fr... by acdha on Friday February 19, 2021 @12:14AM (#26190016)
That’d be my guess: prefetch_related is great but you need to guard it with something like an assertNumQueries test to avoid accidental regressions.
Re:12 requests per second: A realistic look at Python web fr... by rglullis on Friday February 19, 2021 @06:56AM (#26192155)
Maybe I spent too much time with Django already, but if I see anyone doing anything but Book.objects.values_list('author__name', flat=True) for this type of expression, I would mark it as a 3x WTF? in the code review.
Re:12 requests per second: A realistic look at Python web fr... by DangitBobby on Friday February 19, 2021 @05:55PM (#26199129)
As written, it's obvious you should be doing something else like `values()` or `values_list()`. You're much more likely to fall victim to this anti-pattern if it's done within a standard for-loop that has a bunch of other stuff going on. I just wrote it as a list comprehension to avoid having to muck about with formatting on my phone.
Re:12 requests per second: A realistic look at Python web fr... by acdha on Friday February 19, 2021 @09:24AM (#26193449)
I’d also allow prefetch_related if you’re using more than the most trivial data - no point in duplicating logic you have in your models if you have a method which generates something like a name, URL, etc. based on multiple fields.
Re:12 requests per second: A realistic look at Python web fr... by ZephyrBlu on Friday February 19, 2021 @12:20AM (#26190049)
Funnily enough, I recently optimized some code along these lines.

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.

Re:12 requests per second: A realistic look at Python web fr... by monkpit on Friday February 19, 2021 @12:25AM (#26190073)
s/ident/indent/
Re:12 requests per second: A realistic look at Python web fr... by ZephyrBlu on Friday February 19, 2021 @05:51AM (#26191691)
Thanks :).
Re:12 requests per second: A realistic look at Python web fr... by happyflower on Friday February 19, 2021 @04:41AM (#26191263)
ORMs give an affordability to write code faster. That may save time, but instead of saving time, you can also reinvest in better quality and performance. That's up to you and your team.

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.

Re:12 requests per second: A realistic look at Python web fr... by jerf on Friday February 19, 2021 @12:41PM (#26195638)
I'm increasingly banging on the drum that web frameworks shouldn't be measured in requests per second but seconds per request. It sounds really impressive to go from 100,000 to 500,000 requests per second. It's somewhat less impresssive if you consider that's going from 10 microseconds to 2 microseconds... if you consider that your real, non-benchmark handler is likely in the dozens of milliseconds.

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.)

Re:12 requests per second: A realistic look at Python web fr... by vp8989 on Friday February 19, 2021 @11:47AM (#26194968)
"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."

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.

Re:12 requests per second: A realistic look at Python web fr... by closeparen on Friday February 19, 2021 @12:43PM (#26195668)
With Symfony back in the day, you could turn on a little toolbar that would show in the top of your rendered HTML. You could see how many SQL queries were run, what the statements were, how long they took. I think there was some other tracing information as well. This strikes me as a basic and necessary kind of testing to do when developing with an ORM. Perhaps more systematically you could get ORM business into Zipkin or Jaeger, and then have some kind of staging vs. prod or canary vs. prod statistical comparison to see if you’re about to release something dumb. Or maybe simpler to keep generated SQL in unit test assertions. You wouldn’t have to write it, but you would have to read and update it on changes, so you could notice if you were winding up with N+1 queries or a ridiculous join.
Re:12 requests per second: A realistic look at Python web fr... by jeromenerf on Friday February 19, 2021 @04:03AM (#26191044)
> 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.

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".

Re:12 requests per second: A realistic look at Python web fr... by devnonymous on Thursday February 18, 2021 @11:18PM (#26189659)
> My experience doing perf optimizations... without realizing it's expensive).

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.