4 ms·

I sort of said this elsewhere. Others are saying "that's just what happens" and "well, Python?" but it doesn't just happen, and slow database queries are nothin

I sort of said this elsewhere. Others are saying "that's just what happens" and "well, Python?" but it doesn't just happen, and slow database queries are nothing to do with the application language. The problem is Django ORM, like Ruby on Rails, uses the Active Record pattern. Alternate ORMs, such as SQLAlchemy or Hibernate, uses Data Mapper pattern, which avoids the pitfalls of Active Record.

pmontra 3y ago
We can have 1+N queries in any language even without an ORM. Maybe an ORM facilitates mistakes because it hides joins but lack of experience is lack of experience. Some naive bad pseudocode: for book in select * from books: author = select * from authors where id == book.id print book.title author.name In the real world that nested select could be hidden many levels down in method or function calls or even in code run from the templating language. Example: Django templatetags can query the database from the HTML template and any framework or non framework I worked with can do that too.
- robertlagrant 3y ago
  I'm not criticising ORMs, just how active record works.
  - chris12321 3y ago
    Well it's trivial in ActiveRecord to avoid n+1s by preloading associations. As with any technology, if you use it wrong and inefficently then things will be incorrect and slow.
    - robertlagrant 3y ago
      Here's an example I remember: if I want to update a field in a million records in my database, I can't just send the update command to the database to run. Instead, an ActiveRecord ORM will try and load all the records into the application, and for each record object make the change in memory, and then persist the record. If I'm remembering correctly, that is a fundamentally poor approach. Instead of telling the database to do some work, the database is doing more work, and the application is doing work. One mitigation is to batch the work[0]. Another is to special-case updates[1], which bypasses all the ActiveRecord pre/post-save logic. In either case you aren't holding the tool wrong. The tool is wrong. [0] https://apidock.com/rails/ActiveRecord/Batches/find_each https://apidock.com/rails/ActiveRecord/Batches/find_each - you'll note that the batches are "subject to race conditions" - i.e. each batch is its own transaction! And you're still loading the records into your application pointlessly. You're just limiting how many do it at once. [1] https://docs.djangoproject.com/en/dev/topics/db/queries/#updating-multiple-objects-at-once https://docs.djangoproject.com/en/dev/topics/db/queries/#upd... - note: > Be aware that the update() method is converted directly to an SQL statement. It is a bulk operation for direct updates. It doesn’t run any save() methods on your models, or emit the pre_save or post_save signals (which are a consequence of calling save()), or honor the auto_now field option. If you want to save every item in a QuerySet and make sure that the save() method is called on each instance, you don’t need any special function to handle that. Loop over them and call save()

pmontra 3y ago

We can have 1+N queries in any language even without an ORM. Maybe an ORM facilitates mistakes because it hides joins but lack of experience is lack of experience. Some naive bad pseudocode: for book in select * from books: author = select * from authors where id == book.id print book.title author.name In the real world that nested select could be hidden many levels down in method or function calls or even in code run from the templating language. Example: Django templatetags can query the database from the HTML template and any framework or non framework I worked with can do that too.

robertlagrant 3y ago
I'm not criticising ORMs, just how active record works.
- chris12321 3y ago
  Well it's trivial in ActiveRecord to avoid n+1s by preloading associations. As with any technology, if you use it wrong and inefficently then things will be incorrect and slow.
  - robertlagrant 3y ago
    Here's an example I remember: if I want to update a field in a million records in my database, I can't just send the update command to the database to run. Instead, an ActiveRecord ORM will try and load all the records into the application, and for each record object make the change in memory, and then persist the record. If I'm remembering correctly, that is a fundamentally poor approach. Instead of telling the database to do some work, the database is doing more work, and the application is doing work. One mitigation is to batch the work[0]. Another is to special-case updates[1], which bypasses all the ActiveRecord pre/post-save logic. In either case you aren't holding the tool wrong. The tool is wrong. [0] https://apidock.com/rails/ActiveRecord/Batches/find_each https://apidock.com/rails/ActiveRecord/Batches/find_each - you'll note that the batches are "subject to race conditions" - i.e. each batch is its own transaction! And you're still loading the records into your application pointlessly. You're just limiting how many do it at once. [1] https://docs.djangoproject.com/en/dev/topics/db/queries/#updating-multiple-objects-at-once https://docs.djangoproject.com/en/dev/topics/db/queries/#upd... - note: > Be aware that the update() method is converted directly to an SQL statement. It is a bulk operation for direct updates. It doesn’t run any save() methods on your models, or emit the pre_save or post_save signals (which are a consequence of calling save()), or honor the auto_now field option. If you want to save every item in a QuerySet and make sure that the save() method is called on each instance, you don’t need any special function to handle that. Loop over them and call save()