SQLAlchemy Performance on Query Builder, Selects and Inserts

After using SQLAlchemy for over 2 years in various projects, I can say it’s been an amazing tool despite its occasional quirks. Like any ORM, it comes with trade-offs, but what sets SQLAlchemy apart is its flexibility in handling these limitations. SQLAlchemy gives developer from higher level access to lower level access to handling these queries and let the dev choose the approach to solve their problem. Let me share my benchmarks and insights from real-world usage.

Performance Benchmarks

Query Building Performance

Note that these times represent only the SQL string building process. The query builder adds some overhead when constructing the actual SQL query that will be executed.

Converting results back to ORM objects also creates overhead. For maximum raw performance, it’s best to use lower-level approaches and write efficient queries.

Large Result Sets

Number of Iterations: 1,000,000

From the table above, we can see that the ORM struggles with large result sets. The raw DBAPI calls are the fastest. This is because the ORM has to create a lot of objects and this adds overhead to the performance.

Code Snippets for Loading Large Result Sets

1. Load rows using DBAPI fetchall(), don’t make any objects

   cursor.execute(sql)
   [row[0:3] for row in cursor.fetchall()]

2. Load rows using DBAPI fetchall(), generate an object for each row

   cursor.execute(sql)
   [
      SimpleCustomer(id_=row[0], name=row[1], description=row[2])
      for row in cursor.fetchall()
   ]

3. Load individual columns into named tuples using the ORM

   (
      session.query(Customer.id, Customer.name, Customer.description)
         .yield_per(10000)
         .limit(n)
   )

4. Load Core result rows using fetchall

   (
      conn.execute(Customer.__table__.select().limit(n))
         .mappings()
         .fetchall()
   )

5. Load Core result rows using fetchmany/streaming

   (
      conn.execution_options(stream_results=True)
         .execute(Customer.__table__.select().limit(n))
   )

6. Load lightweight “bundle” objects using the ORM

   (
      session.query(
         Bundle("customer", Customer.id, Customer.name, Customer.description)
      ).yield_per(10000)
   )

7. Load Core result rows using Core / fetchmany

   result = conn.execute(Customer.__table__.select().limit(n))
   result.fetchmany(10000)

8. Load Core result rows using fetchall

   conn.execute(Customer.__table__.select().limit(n)).fetchall()

9. Load fully tracked ORM objects a chunk at a time using yield_per()

   session.query(Customer).yield_per(1000).limit(n)

10. Load fully tracked ORM objects into one big list

    list(session.query(Customer).limit(n))

Bulk Inserts

Inserting a row is a relatively expensive operation. And with the increasing number of rows, the performance degrades significantly. I usually prefer batched inserts, as this saves memory and faster.

Code Snippets for Bulk Inserts

1. Batched INSERT statements via the ORM in “bulk”, returning new Customer objects

   session.scalars(
      insert(Customer).returning(Customer),
      [
         {"name": "customer %d" % i, "description": "customer %d" % i}
         for i in range(n)
      ],
   )

2. A single Core INSERT construct inserting mappings in bulk

   conn.execute(
    Customer.__table__.insert(),
      [
         {"name": "customer %d" % i, "description": "customer %d" % i}
         for i in range(n)
      ],
   )

3. The DBAPI’s API inserting rows in bulk

   cursor.executemany(
      str(compiled),
      [("customer %d" % i, "customer %d" % i) for i in range(n)],
   )

4. Batched INSERT statements via the ORM in “bulk”, not returning rows

   session.execute(
      insert(Customer),
      [
         {"name": "customer %d" % i, "description": "customer %d" % i}
         for i in range(n)
      ],
   )

5. INSERT statements via the ORM (batched with RETURNING if available), fetching generated row id

   session.add_all(
      [
         Customer(name="customer %d" % i, description="customer %d" % i)
         for i in range(chunk, chunk + 1000)
      ]
   )
   session.flush()

6. Batched INSERT statements via the ORM, PKs already defined

   session.add_all(
      [
         Customer(
            id=i + 1,
            name="customer %d" % i,
            description="customer %d" % i,
         )
         for i in range(chunk, chunk + 1000)
      ]
   )
   session.flush()

Choosing the Appropriate Abstraction Level

Based on comprehensive benchmarks and understanding the trade-offs, here are the recommended approaches for different scenarios:

• Raw SQL/DBAPI: Optimal for maximum performance in bulk operations
• SQLAlchemy Core: Recommended for bulk operations requiring SQL generation benefits
• ORM Bulk Operations: Suitable for batch operations requiring ORM features
• Full ORM: Best for complex operations requiring complete object functionality

Data Loading Performance Analysis

PostgreSQL benchmark results demonstrate the following performance patterns:

• Raw DBAPI fetch: 1.31 seconds
• ORM Column Loading: 2.48 seconds
• Core Operations: ~3.5 seconds
• Full ORM Objects: 8.53-15.05 seconds

All the analysis and benchmark was done using SQLAlchemy’s example scripts