From days to minutes, from minutes to milliseconds with SQLAlchemy

Avoiding performance pitfalls and writing good database access patterns with the SQLAlchemy ORM

Leonardo Rochael Almeida

Debugging Performance SQL Alchemy python

See in schedule Download Slides

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.

Type: Talk (45 mins); Python level: Beginner; Domain level: Beginner