We're sorry but this page doesn't work properly without JavaScript enabled. Please enable it to continue.
Feedback

From days to minutes, from minutes to milliseconds with SQLAlchemy

Formal Metadata

Title
From days to minutes, from minutes to milliseconds with SQLAlchemy
Subtitle
Avoiding performance pitfalls and writing good database access patterns with the SQLAlchemy ORM
Title of Series
Number of Parts
118
Author
License
CC Attribution - NonCommercial - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this
Identifiers
Publisher
Release Date
Language

Content Metadata

Subject Area
Genre
Abstract
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). 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. We'll see solutions not only for queries, but also for 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 back-end, not only SQL databases. This talk is geared towards Python developers with systems that talk to databases. It should be accessible to anyone who already programs in Python (early intermediary level), but will be most useful for developers with projects talking to SQL databases, specially using an ORM like SQLAlchemy. Attendees will learn to detect how N+1 query situations arise and how to work around them effectively. They will also learn how to do mass inserts and mass updates with SQLAlchemy.
Keywords