techspot.zzzeek.org

A few weeks ago, Flávio Codeço Coelho posted a comparison of Python database interfaces, with a test that focused primarily on mass insert speed. While he had good things to say about our performance, I didn't find the results to be very impressive, and not surprisingly - mass insert speed is something we've never really focused on. The idea being, if you have to insert 100,000 records, you'd probably be using a bulk insert tool or raw SQL scripts anyway.

SQLAlchemy's development has historically been focused on the richness of its SQL expression language and the completeness of its object relational mapper. While it was important that applications using these features don't use up all the system's memory and grind to a halt, at the same time we've never had a "speed-crazy" mindset.

Contrast this to my other project, Mako, where speed was the singlemost important priority; every line of code written was checked against a benchmark suite to measure it's impact. The language itself is pretty barebones, and even a little inconvenient in some ways. That effort provided a pretty fast product which it's rumored will form the basis of the newly designed reddit.com.

But while the "speed at all costs" mindset is very relevant for a template language, it somehow doesn't have the same urgency in the land of SQL, where if people truly want blazing speed, they use raw SQL. The point of a SQL toolkit and/or an ORM is to enhance the developer experience, to bring richness and ease to common (and not so common) use cases. Such as, being able to issue SQL statements with Chinese-language identifiers with all the DBAPI-divergent trickery hidden behind the scenes, or more commonly just to have smooth Unicode support, numeric and date consistency, richly featured default generators, support for database-specific features like Oracle OUT parameters and Psycopg2's server-side cursors, a healthy dose of hooks and options for ORM behavior, and everything else. The sheer volume of behaviors we are shooting to deliver place a burden on the architecture, which constantly has to refactor and adapt in order to accomodate (and SQLAlchemy is nothing if not accommodating). Not to mention it heavily weighs upon the ability for a small group of unpaid volunteers to get around to reviewing, streamlining, and optimizing all of it.

Nevertheless, the 0.4 release is just plain ginormous (and is currently available as a beta), and is changing, streamlining, refactoring just about everything. We have more users, more developers, more trac tickets, so a lot of what was always just "potential" is finally becoming a reality. We even have Robert Brewer of Dejavu (and of course CherryPy) writing benchmark tests for us (although, he might just be trying to get Geniusql to kick our butt...in the most friendly way of course). There's a lot of energy right now so I try to get as much production out of that wave as possible while it lasts.

So the particularly egregious slothiness SQLAlchemy exhibited in Flávio's tests (and really, he picked probably the worst thing he could within SA) has been overhauled. An adaptation of his test, which cranks it up by not only inserting but also fully selecting the results, inserts and selects 50,000 rows from an in-memory SQLite database. When run against 0.3, Hotspot shows this extremely large number of function calls:

2850532 function calls (2850529 primitive calls) in 19.501 CPU seconds

That's really bad. In 0.4 trunk, we have it down to this:

900525 function calls (900513 primitive calls) in 8.128 CPU seconds

That's 68% fewer function calls to insert and iterate 50,000 rows of three columns each. It's not that the new system is all that blazingly fast; a raw insert of 100,000 rows with SQLite takes 4 seconds on my machine, while with SA takes 14 seconds. Still, that's a whole lot better than 0.3 which takes 32 seconds. Speedups like this are not due to any brilliant re-architecting, it's just having enough people helping out such that we have the resources to address these old, crufty functionalities that got written at 2:30 AM eight months ago.

For everything that gets said about SQLAlchemy, we are really just barely out of the "idea" phase, and it was meant to take a long time for this thing to be really great; especially at "homegrown" pace. Our version number being only 40% of 1.0 is quite intentional.