Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ban 1+N in Django

suor.github.io

Post reply on HN
Print view View original

131–140 of 153 posts

Re: Ban 1+N in Django

#131

Post by Suor »

JeremyNT wrote: post #128
nickjj wrote: post #7

There is a case where having N+1 queries are beneficial. In Rails terms, it's when you perform Russian doll caching, but you can do this in any framework. The idea is you can cache a specific X thing which might make a query to an associated Y thing. A textbook N+1 query case (ie. a list of posts (X) that get the author's name (Y)). If you render the view without any cache with 10 things then you'd perform 20 queries…

> If you render the view without any cache with 10 things then you'd perform 20 queries but after the cache is warm you'd perform 0 queries. If item 5's Y gets updated then you only need to bust the cache for item 5 and query only item 5's Y association. Performing a preloaded query to get all X 10 things with their Y associated things could be an expensive query. The technique you describe makes sense, but how often…

Writing things directly is tightly coupling code. Sometimes quite distant portions of it, i.e. database access and presentation logic. With ORM and some smarter lazy technics or introspection at least you can untie it, with hand written SQL there is no way. And the reason is strings are poorly composable. Unless you use some query generator, but then we are back to ORM-like something.

Re: Ban 1+N in Django

#132

Post by jonatron »

stavros wrote: post #122
jonatron wrote: post #8

See also django-zen-queries https://github.com/dabapps/django-zen-queries , which can make it impossible for changes to a template to trigger queries.

I like Zen queries, but sometimes you just have to make queries in templates (e.g. when you want to check the user on the request object), or sometimes it's just convenient and there's nothing wrong with it (e.g. when you want to check the user on the request object). Zen queries makes that use case impossible, sadly.

I don't actually use Zen queries myself, but it does have a queries_disabled template tag, which would allow for more specific control.

Re: Ban 1+N in Django

#133

Post by zzzeek »

aforwardslash wrote: post #130
zzzeek wrote: post #116

Earlier quoted context omitted.

I don't know what serialization/deserialization has do with this. Does the object map to database rows and there's code that moves the data back and forth? That's an object relational mapper.

mapping database rows to object structures is an object mapper. An object relational mapper also keeps track of table dependencies (such as related fields). If you read a row from a database and generate an object whose attributes map the fields in the database and are used to retrieve the values (a data object), that is an object mapper. This means when fetching eg. user.type it will return 1 instead of the data obj…

> An object relational mapper also keeps track of table dependencies (such as related fields).

that's not what "relational" means. "relational" means, "a relational database". where we are using SQL statements to deliver data to and from such a database. an ORM that does not directly interpret objects along one-to-many collections /many-to-one attributes is still an object relational mapper.

> Regarding serialization, why does it matter? Because you need to serialize and de-serialize data objects or models if you're adding cache to eg. a service layer.

that's a separate concern from object relational mapping. Do you have the notion that ORMs produce objects that aren't compatible with serialization?

Re: Ban 1+N in Django

#134

Post by JeremyNT »

Suor wrote: post #131
JeremyNT wrote: post #128

Earlier quoted context omitted.

> If you render the view without any cache with 10 things then you'd perform 20 queries but after the cache is warm you'd perform 0 queries. If item 5's Y gets updated then you only need to bust the cache for item 5 and query only item 5's Y association. Performing a preloaded query to get all X 10 things with their Y associated things could be an expensive query. The technique you describe makes sense, but how often…

Writing things directly is tightly coupling code. Sometimes quite distant portions of it, i.e. database access and presentation logic. With ORM and some smarter lazy technics or introspection at least you can untie it, with hand written SQL there is no way. And the reason is strings are poorly composable. Unless you use some query generator, but then we are back to ORM-like something.

Of course, it's not a one-size fits all solution. I agree that ORMs are incredibly helpful and using them is almost always the right place to start for this sort of thing because they are very flexible and take away the cognitive load.

But we're talking about performance here... if/when you start to have issues, it is time to take a look and see where you can do better.

Rails' ActiveRecord gives you tools to help build queries whether or not you materialize them as full blown objects. If you need to do some specific data extraction but want to avoid a bunch of needless instantiation, you can still leverage the ORM to help build the queries (and avoid writing raw SQL as strings in your codebase) which is a very helpful halfway point.

Re: Ban 1+N in Django

#135

Post by Arch-TK »

zzzeek wrote: post #133
aforwardslash wrote: post #130

Earlier quoted context omitted.

mapping database rows to object structures is an object mapper. An object relational mapper also keeps track of table dependencies (such as related fields). If you read a row from a database and generate an object whose attributes map the fields in the database and are used to retrieve the values (a data object), that is an object mapper. This means when fetching eg. user.type it will return 1 instead of the data obj…

> An object relational mapper also keeps track of table dependencies (such as related fields). that's not what "relational" means. "relational" means, "a relational database". where we are using SQL statements to deliver data to and from such a database. an ORM that does not directly interpret objects along one-to-many collections /many-to-one attributes is still an object relational mapper. > Regarding serialization…

> an ORM that does not directly interpret objects along one-to-many collections /many-to-one attributes is still an object relational mapper.

This is diluting the term ORM beyond any usable definition.

A bit like when people claim that any programming language where you can associate a function with data and have the function implicitly get passed a reference to the data through data.function() call syntax is an OO language.

First off, I question your definition of "object". Is a C struct an "object"? Is a python tuple an "object"? I know that in python it literally is an object, but so is a function. But obviously your definition of an object must be separate from any given language.

To that end, does python's sqlite3 interface constitute an ORM? It converts the results of SQL queries to tuples. What about if I change the row_factory to sqlite3.Row? Is it now an ORM?

Where do you draw the line between something which is and isn't an ORM?

What is the point of such a vague definition?

What isn't an ORM in this case?

Re: Ban 1+N in Django

#136

Post by aforwardslash »

zzzeek wrote: post #133
aforwardslash wrote: post #130

Earlier quoted context omitted.

mapping database rows to object structures is an object mapper. An object relational mapper also keeps track of table dependencies (such as related fields). If you read a row from a database and generate an object whose attributes map the fields in the database and are used to retrieve the values (a data object), that is an object mapper. This means when fetching eg. user.type it will return 1 instead of the data obj…

> An object relational mapper also keeps track of table dependencies (such as related fields). that's not what "relational" means. "relational" means, "a relational database". where we are using SQL statements to deliver data to and from such a database. an ORM that does not directly interpret objects along one-to-many collections /many-to-one attributes is still an object relational mapper. > Regarding serialization…

> that's not what "relational" means. "relational" means, "a relational database". where we are using SQL statements to deliver data to and from such a database. an ORM that does not directly interpret objects along one-to-many collections /many-to-one attributes is still an object relational mapper.

It doesn't. It means it works as a virtual object database that honors relationships on the underlying schema. The fact that the schema is relational is an implementation detail. Many ORMs will work with non-relational databases, but will honor underlying relations transparently if they exist, such as eg. SqlAlchemy on MongoDB.

An object mapper isn't even a "database-related" technique. Its just a mechanism to map data into objects - ex. JSON deserialization into object attributes is object mapping. When used in conjunction with traditional database mechanisms (such as eg. repository pattern and query builders), can be used to minimize data conversions between systems.

> that's a separate concern from object relational mapping.

Well, it is because in Django ORM or SqlAlchemy you use differerent declarations of data structures for data received from requests, data passed around and data read from and written to the database. It is actually a very leaky implementation where eg. when designing models you feel like you're working on a business object representation, but in fact you're still catering to implementation details of the relational model, such as primary keys, nullable fields, indexes and so on and so on.

> Do you have the notion that ORMs produce objects that aren't compatible with serialization?

That is precisely the point. This is not a feature, it is a quite important limitation. If you use data objects, this issue (as well as complex mapping routines) goes away, because then these objects only hold data and not business logic. These concepts aren't really mainstream in python, but are bread and butter in many other languages.

Re: Ban 1+N in Django

#137

Post by Daishiman »

aforwardslash wrote: post #76
Daishiman wrote: post #37

Earlier quoted context omitted.

The ORM provides a myriad other features, like adapters for every production database under the sun, query composition that is literally impossible in plain SQL, a reasonable interface to the admin and the ecosystem of Django apps, and above all: a logical interface that maps _business objects_ to their SQL tables. The ORM hate seems to come from people whose day to day interaction with data tables isn't mediated by…

... Or by people that actually understand the impedance mismatch between objects and data (quick django example - request data and models are different and not easily interchangeable). Or people that require good caching implementations. Or people that actually design database systems schema-first. Or peoplw that rely on advanced usage that isnt always easy to perform in orm's. The list goes on.

The list goes on an on and yet in practice these problems are solvable and the impedance is just really not a big deal.

There is only one feature missing in the ORM which is composite primary key. For everything else all those things have clear and simple solutions.

"Impedance mismatch" is just a thought-terminating cliché. High-level languages have impedance mismatch with binary code; reactive components have impedance mismatch with state, relational tables have impedance mismatch with hierarchical data. Yet we find solutions and workarounds and the severity of these problems is generally overrated outside of purely theoretical contexts.

Re: Ban 1+N in Django

#138

Post by s17n »

Daishiman wrote: post #137
aforwardslash wrote: post #76

Earlier quoted context omitted.

... Or by people that actually understand the impedance mismatch between objects and data (quick django example - request data and models are different and not easily interchangeable). Or people that require good caching implementations. Or people that actually design database systems schema-first. Or peoplw that rely on advanced usage that isnt always easy to perform in orm's. The list goes on.

The list goes on an on and yet in practice these problems are solvable and the impedance is just really not a big deal. There is only one feature missing in the ORM which is composite primary key. For everything else all those things have clear and simple solutions. "Impedance mismatch" is just a thought-terminating cliché. High-level languages have impedance mismatch with binary code; reactive components have impeda…

ORMs are just not performant unless you reason about all the code at the level of "what queries are going to be generated and when", which makes the ORM an unhelpful layer of obfuscation over the layer of abstraction that you're actually reasoning at.

This is quite different from high level vs assembly where you can easily go your whole life without ever learning assembly language or how a compiler works.

Or to put it another way, the difference between the two situations is that an ORM API is not a higher level language than SQL. Transpiling between two languages of comparable expressiveness (SQL is actually more expressive but no need to go there) adds an extra source of problems without gaining you much.

Re: Ban 1+N in Django

#139

Post by aforwardslash »

Daishiman wrote: post #137
aforwardslash wrote: post #76

Earlier quoted context omitted.

... Or by people that actually understand the impedance mismatch between objects and data (quick django example - request data and models are different and not easily interchangeable). Or people that require good caching implementations. Or people that actually design database systems schema-first. Or peoplw that rely on advanced usage that isnt always easy to perform in orm's. The list goes on.

The list goes on an on and yet in practice these problems are solvable and the impedance is just really not a big deal. There is only one feature missing in the ORM which is composite primary key. For everything else all those things have clear and simple solutions. "Impedance mismatch" is just a thought-terminating cliché. High-level languages have impedance mismatch with binary code; reactive components have impeda…

> and the impedance is just really not a big deal

Im quite happy you haven't come across major issues with this. If you develop clean architecture solutions that are live products across years, this is a major problem (eg. table X is now a separate full blown service; table Y is an external materialized table with no inserts, as inserts now go into an external messaging system such as kafka) etc.

> "Impedance mismatch" is just a thought-terminating cliché.

So are the whole ORM advantages. My personal distaste from ORM doesn't even start in the obvious technical drawbacks, starts with the fact that a developer should have a solid grasp on the domain he is working, which more often than not, ORM advocates lack. If you can't model data from a storage perspective (which, btw, is often the bottleneck of your application), you shure as hell won't do a good job modelling it in a business domain.

> Yet we find solutions and workarounds and the severity of these problems is generally overrated outside of purely theoretical contexts.

Ahh yes, the typical "lets not get theoretical" argument. ORMs are usually crap, and in python they are actual crap. If Django is a good example for you, good for you. If you ever have a look at Entity Framework you'll be amazed. Try to use a schema-first approach with any mainstream ORM and you'll quickly realize all you do is workarounds because of assumptions and limitations. Thing is, for my daily work, these problems are actual problems. So much we don't use Django or ORMs.

Re: Ban 1+N in Django

#140

Post by Daishiman »

s17n wrote: post #138
Daishiman wrote: post #137

Earlier quoted context omitted.

The list goes on an on and yet in practice these problems are solvable and the impedance is just really not a big deal. There is only one feature missing in the ORM which is composite primary key. For everything else all those things have clear and simple solutions. "Impedance mismatch" is just a thought-terminating cliché. High-level languages have impedance mismatch with binary code; reactive components have impeda…

ORMs are just not performant unless you reason about all the code at the level of "what queries are going to be generated and when", which makes the ORM an unhelpful layer of obfuscation over the layer of abstraction that you're actually reasoning at. This is quite different from high level vs assembly where you can easily go your whole life without ever learning assembly language or how a compiler works. Or to put i…

> ORMs are just not performant unless you reason about all the code at the level of "what queries are going to be generated and when", which makes the ORM an unhelpful layer of obfuscation over the layer of abstraction that you're actually reasoning at.

You're assuming I use the ORM to not reason about SQL or not think about performance. This isn't true; first of all because even if you write SQL, SQL performance is not immediately obvious for any but the simplest of indexed queries. In no storage system do you ever get away from reasoning about this.

Second because SQL is actually a mediocre abstraction layer over your data storage. You can't really compose SQL queries; in an ORM taking a base Query object and adding a bunch of various `filter()` statements automatically does the right thing. Basic queries are much shorter visually; ORMs deal with the abstraction of table and column renames that mean rewriting all your SQL in other systems. I feel like you're just trotting out "reasons" out of a blog post from people whose priorities aren't the ones that people like us who write CRUD systems day in and day out do.

Again, you're talking about theoretical disadvantages which I have only really encountered about a half dozen times in over a decade of using Django even in performance-sensitive areas. Rewriting one ORM query out of a hundred is not a problem, especially if I had to rewrite the SQL in the first place.

Post reply on HN