PyCon 2019 | From days to minutes, from minutes to milliseconds with SQLAlchemy
Speaker:Leonardo Rochael Almeida
Object Relational Mappers (ORMs) are awesome enhancers of developer productivity. The freedom of having the library write that SQL and give you back a useful, rich model instance (or a bunch of them) instead of just a tuple or a list of records is simply amazing.
But if you forget you have an actual database behind all that convenience, then it’ll bite you back, usually when you’ve been in production for a while, after you’ve accumulated enough data that your once speedy application starts slowing down do a crawl.
Databases work best when you ask them once for (or to do) a bunch of stuff, instead of asking them lots of times for small stuff. We’ll discuss how innocent looking attribute accesses on your model instances translate to sequential queries (the infamous [N+1 problem][1]).
Then we’ll go through some practical solutions, taken from real cases that resulted in massive speed ups. We’ll cover how changes in Python code resulted in changes to the resulting SQL Queries
Solutions not only for queries, but also inserts and updates, which tend to be less well documented.
Though this talk focuses on SQLAlchemy, the lessons should be applicable to most ORMs in most programing languages. The ideas discussed, and solutions proposed are also valid for any storage backend, not only SQL databases.
[1]: https://docs.sqlalchemy.org/en/13/glossary.html#term-n-plus-one-problemSlides can be found at: https://speakerdeck.com/pycon2019 and https://github.com/PyCon/2019-slides
