Dataloader knex benchmark

_output

mapTo: 428.186ms
mapTo2: 319.058ms
ord: 348.390ms
mapToMany: 786.141ms
mapToMany2: 780.661ms
ord agg: 31856.442ms

dataloader-knex-bench.js

const DataLoader = require('./dist');
const knex = require('knex')('pg://postgres:pg@localhost:5432/postgres');

const rand = n => Math.floor(n * Math.random());

// random user ids used in benchmark
const userIds = Array.from({ length: 250 }, () =>
  Array.from({ length: rand(100) }, () => rand(5e4) + 1),
);
const storyIds = Array.from({ length: 20 }, () =>
  Array.from({ length: rand(100) }, () => rand(15e4) + 1),
);

/*
create table users (
  id bigserial primary key,
  name text,
  email text,
  profile jsonb
);

create table stories (
  id bigserial primary key,
  author_id bigint references users(id) on delete cascade,
  title text,
  content text,
  tags text[],
  link text
);
*/

const insertUsers = async () => {
  for (let i = 0; i < 500; i++) {
    await knex('users').insert(
      Array.from({ length: 100 }, (_, j) => ({
        name: `User ${i * 100 + j}`,
        email: `john.doe+${i * 100 + j}@example.org`,
        profile: JSON.stringify({
          address: '12, Green Street',
          country: 'There',
        }),
      })),
    );
  }
};
const insertStories = async () => {
  for (let i = 0; i < 500; i++) {
    await knex('stories').insert(
      Array.from({ length: 300 }, (_, j) => ({
        author_id: (i * 300 + j) % 5e4 + 1,
        title: `Story ${i * 300 + j}`,
        content: 'Very long cpntent... '.repeat(50),
        tags: ['foo', 'bar'],
        link: `https://foo.blog/my-story-${i * 300 + j}/aef215`,
      })),
    );
  }
};

const dl1 = new DataLoader(ids =>
  knex
    .table('users')
    .whereIn('id', ids)
    .select()
    .then(mapTo(ids, row => row.id)),
);

const dl2 = new DataLoader(ids =>
  knex
    .table('users')
    .whereIn('id', ids)
    .select()
    .then(mapTo2(ids, row => row.id)),
);

const dl3 = new DataLoader(ids =>
  knex
    .table('users')
    .joinRaw('join unnest(:ids::int[]) with ordinality t(id, ord) using (id)', {
      ids,
    })
    .orderBy('t.ord'),
);

const dl4 = new DataLoader(ids =>
  knex
    .table('stories')
    .whereIn('author_id', ids)
    .select()
    .then(mapToMany(ids, row => row.author_id)),
);

const dl5 = new DataLoader(ids =>
  knex
    .table('stories')
    .whereIn('author_id', ids)
    .select()
    .then(mapToMany2(ids, row => row.author_id)),
);

const dl6 = new DataLoader(ids =>
  knex
    .raw(
      `select * from unnest(:ids::int[]) with ordinality t(u, ord)
join lateral (select array_agg(row_to_json(stories)) as stories from stories where author_id=u) s on true
order by t.ord`,
      { ids },
    )
    .then(({ rows }) => rows.map(r => r.stories)),
);

(async () => {
  // await insertUsers();
  // await insertStories();

  console.time('mapTo');
  for (let i = 0; i < userIds.length; i++) {
    await dl1.loadMany(userIds[i]);
  }
  console.timeEnd('mapTo');

  console.time('mapTo2');
  for (let i = 0; i < userIds.length; i++) {
    await dl2.loadMany(userIds[i]);
  }
  console.timeEnd('mapTo2');

  console.time('ord');
  for (let i = 0; i < userIds.length; i++) {
    await dl3.loadMany(userIds[i]);
  }
  console.timeEnd('ord');

  console.time('mapToMany');
  for (let i = 0; i < storyIds.length; i++) {
    await dl4.loadMany(storyIds[i]);
  }
  console.timeEnd('mapToMany');

  console.time('mapToMany2');
  for (let i = 0; i < storyIds.length; i++) {
    await dl5.loadMany(storyIds[i]);
  }
  console.timeEnd('mapToMany2');

  console.time('ord agg');
  for (let i = 0; i < storyIds.length; i++) {
    await dl6.loadMany(storyIds[i]);
  }
  console.timeEnd('ord agg');
})().finally(() => knex.destroy());

const mapTo = (ids, selector) => rows =>
  ids.map(id => rows.find(x => selector(x) === id));
// or
const mapTo2 = (ids, selector) => rows => {
  const m = new Map(rows.map(row => [selector(row), row]));
  return ids.map(id => m.get(id));
};

const mapToMany = (ids, selector) => rows =>
  ids.map(id => rows.filter(x => selector(x) === id));
// or
const mapToMany2 = (ids, selector) => rows => {
  const m = rows.reduce(
    (m, row) => m.set(selector(row), (m.get(selector(row)) || []).concat(row)),
    new Map(),
  );
  return ids.map(id => m.get(id));
};