Why ORMs are slow (and getting slower)

Query splitting 🪓​

It’s common for ORMs to perform non-trivial operations (deep fetching, nested mutation, inline aggregation, etc.) by opaquely executing several queries under the hood. This may not be obvious to the end user. Even “next-generation” ORMs do this.

For instance, the TypeScript ORM Prisma executes a separate query for each relation you fetch. The following operation executes 3 separate SQL queries, executed in sequence.

const result = await prisma.movies.findMany({
  select: {
    id: true,
    title: true,
    reviews: {
      select: {
        rating: true,
        author: {
          select: {id: true, name: true},
        },
      },
    },
  },
  where: {title: {startsWith: 'Iron Man', mode: 'insensitive'}},
});

Transactional mutations 🧬​

Nested mutations (inserts, updates, etc.) are even worse. In addition to requiring an roundtrip query for each level of nesting, the ORM must also begin and commit a transaction, as well as a final query to fetch the data that was just inserted. The following operation executes six SQL queries and five round-trips.

const results = await prisma.movies.create({
  data: {
    title: 'The Batman',
    year: 2022,
    cast: {
      create: {
        person: {
          create: {
            name: 'Robert Pattinson'
          },
        },
      },
    },
  },
});

By contrast, EdgeDB and SQL can represent this operation as a single query requiring a single round trip.

insert Movie {
  title := "The Batman",
  year := 2022,
  cast := (insert Person { name := "Robert Pattinson"})
}

Aggregation (or lack thereof) 🪣​

Less mature ORMs often don’t support functionality like aggregations (counts, statistics, averages, etc.), forcing users to overfetch and perform these calculations server-side.

While some modern ORMs provide “top-level” aggregation ("Count the number of movies in the database"), even the most advanced ORMs don’t support simple relational (i.e. nested) aggregations such as "Select some movies, returning the title and average rating of all its reviews" This (quite simple) query requires either:

1. Overfetching all associated Reviews for each movie, or

2. Executing a separate aggregation query

3. Falling back to SQL ☹️

The first two options require two round trips. Here’s how #2 might look in Prisma:

const results = await prisma.movies.findMany({
  select: {
    id: true,
    title: true
  },
  where: { release_year: { gt: 1999 }}
});

const avgRatings = await prisma.reviews.groupBy({
  by: ['movie_id'],
  where: {
    movie_id: {
      in: results.map((r) => r.id),
    },
  },
  _avg: { rating: true }
});

// then post-process the data

Simulated server-database latency 🐇​

The execution environment simulates a 1 millisecond latency between the server and database. The typical latency between availability zones in a given region is 1-2ms. The vast majority of applications do not have the resources to support per-availability-zone replication, so this assumption is reasonable.

On Linux, this latency can be simulated with tc like so:

sudo tc qdisc add dev br-webapp-bench root netem delay 1ms

Dataset 🍿​

We are simulating an IMDB-style movie database website. The sample dataset consists of 25k movies, 100k people, 100k users, and 500k reviews.

The Movie type has two links (relations): cast -> Person[] and directors -> Person[] (both of which are orderable by a list_order association property). The Review type has two links: movie -> Movie and author -> User.

Queries 🐣​

The following queries have been implemented for each target.

InsertMovie: Evaluates nested mutations and the ability to insert and select in a single step.

Insert a Movie, setting its cast and directors with pre-existing Person objects. Return the new Movie, including all its properties, its cast, and its directors.

with
  new_movie := (
    insert Movie {
      title := <str>$title,
      image := <str>$image,
      description := <str>$description,
      year := <int64>$year,
      directors := (
        select Person
        filter .id = <uuid>$d_id
      ),
      cast := (
        select Person
        filter .id in array_unpack(<array<uuid>>$cast)
      )
    }
  )
select new_movie {
  id,
  title,
  image,
  description,
  year,
  directors: { id, full_name, image } order by .last_name,
  cast: { id, full_name, image } order by .last_name
};

GetMovie: Evaluates deep (3-level) fetching.

Fetch a Movie by ID, including all its properties, its cast (in list_order), its directors (in list_order), and its associated Reviews (including basic information about the review author).

select Movie {
  id,
  image,
  title,
  year,
  description,
  avg_rating,
  directors: {
    id,
    full_name,
    image
  } order by @list_order empty last
    then .last_name,
  cast: {
    id,
    full_name,
    image,
  } order by @list_order empty last
    then .last_name,
  reviews := (
    select .<movie[is Review] {
      id,
      body,
      rating,
      author: {
        id,
        name,
        image
      }
    } order by .creation_time desc
  )
}
filter .id = <uuid>$id;

GetUser: Evaluates reverse relation fetching and relation aggregation.

Fetch a User by ID, including all its properties and 10 most recently written Reviews. For each review, fetch all its properties, the properties of the Movie it is about, and the average rating of that movie (averaged across all reviews in the database).

select User {
  id,
  name,
  image,
  latest_reviews := (
    select .<author[is Review] {
      id,
      body,
      rating,
      movie: {
        id,
        image,
        title,
        avg_rating := math::mean(.<movie[is Review].rating)
      }
    }
    order by .creation_time desc
    limit 10
  )
}
filter .id = <uuid>$id;

JavaScript ORMs [Full Report]​

The libraries tested are:

• Prisma

• TypeORM

• Sequelize

• EdgeDB (TypeScript query builder)

Python ORMs [Full Report]​

The libraries tested are:

• Django

• SQLAlchemy

• EdgeDB (Python client)

Raw SQL [Full Report]​

For comparison, below are the benchmark results for a tuned PostgreSQL implementation of the benchmark queries, executed using popular Postgres drivers:

• asyncpg

• psycopg2

• go-pgx

• node-postgres

For reference the EdgeDB results (using the Python client) are also included.