Why is my Sequelize query so slow? (nested-joins using findAll)

Question

I'm seeing an issue where my query is exponentially slower as I include more of the DayAvailability nested associations on the Schedule model. The goal is to serialize the User instance to include all associated instances.

Here is the table structure:

// User model

const User = sequelize.define('user', {
    id: {
      type: DataTypes.UUID,
      primaryKey: true
    }
}, {});
});

User.associate = function (models) {
  User.hasOne(models.Schedule);
};

// Schedule model

const Schedule = sequelize.define('schedule', {
  id: {
    type: DataTypes.UUID,
    primaryKey: true
  },
  userId: DataTypes.UUID
}, {
  hooks: {
    async afterCreate(instance, options) {
      await instance.createMonday();
      await instance.createTuesday();
      await instance.createWednesday();
      await instance.createThursday();
      await instance.createFriday();
      await instance.createSaturday();
      await instance.createSunday();
    }
  }
});

Schedule.associate = function(models) {
  Schedule.belongsTo(models.User);
  Schedule.hasOne(models.DayAvailability, { as: 'Monday', foreignKey: 'scheduleId' });
  Schedule.hasOne(models.DayAvailability, { as: 'Tuesday', foreignKey: 'scheduleId' });
  Schedule.hasOne(models.DayAvailability, { as: 'Wednesday', foreignKey: 'scheduleId' });
  Schedule.hasOne(models.DayAvailability, { as: 'Thursday', foreignKey: 'scheduleId' });
  Schedule.hasOne(models.DayAvailability, { as: 'Friday', foreignKey: 'scheduleId' });
  Schedule.hasOne(models.DayAvailability, { as: 'Saturday', foreignKey: 'scheduleId' });
  Schedule.hasOne(models.DayAvailability, { as: 'Sunday', foreignKey: 'scheduleId' });
};

// DayAvailability model

const DayAvailability = sequelize.define('day_availability', {
  id: {
    type: DataTypes.UUID,
    primaryKey: true
  },
  scheduleId: DataTypes.UUID
}, {});

DayAvailability.associate = function(models) {
  DayAvailability.belongsTo(models.Schedule);
};

(omitting other fields for brevity)

This setup does indeed work as intended. I can get a User instance with the nested Schedule instance included, however including the DayAvailability instances nested inside the Schedule instance causes a ridiculously slow query. If I omit some of the DayAvailability instance associations from the query, it's not as slow.

// User controller

const include = { model: Schedule, include: [
    { association: 'Monday' },  // only Monday - ~0.1 sec
    { association: 'Tuesday' },  // include Tues - ~0.5 sec
    { association: 'Wednesday' },  // include Wed - ~1.5 sec
    { association: 'Thursday' },   // include Thurs - 2+ sec
    { association: 'Friday' },
    { association: 'Saturday' },
    { association: 'Sunday' }   // include all - times out 
]};

await User.findOne({
  where: { id: '123' },
  include
});

Any ideas as to why this query is so incredibly slow? Is it no good to include nested associations like this?

Thanks for your help.

Edit: here is the SQL query that gets generated from the above Sequelize function. Other fields from the models are included in the SQL query as a heads up.

https://gist.github.com/FilmCoder/0afba42ffefb8f1e483699eb7ca2ac3b

Schedule.hasOne(models.DayAvailability, { as: 'Tuesday', foreignKey: 'scheduleId' }); Schedule.hasOne(models.DayAvailability, { as: 'Wednesday', foreignKey: 'scheduleId' }); ... looks like duplicated unneccessary associations. They will map exactly the same record — Anatoly, Apr 16 '20 at 16:59
It seems you multiply 7 table records so overall amount of records is enormous! — Anatoly, Apr 16 '20 at 17:04
@Anatoly agreed but how could one create the seven different fields on the Schedule model that each map to a DayAvailability model instance? BTW I ended up setting each of these fields as a JSON instead of a separate model. It's working well, but I left the question open because it still confuses me. — user3386826, Apr 18 '20 at 03:16
How many records in DayAvailability for one schedule id? From 0 to 7 according to days of week? — Anatoly, Apr 18 '20 at 09:46

score 0 · Answer 1 · answered Apr 18 '20 at 15:57

I can give a little more information. The DayAvailability record may expand in the future with more columns to include more information, such as a time range for when a person is available, which time zone they are in, their track record for how open they are on that day, etc...

And there will be 7 DayAvailability records per schedule, as the "Schedule" represents one week, and DayAvailability a single day of that week.

We're wondering if we're doing something fundamentally wrong with foreign keys and there's a way to make this work efficiently within a relational database.

I would have made this a comment, but for some reason stack overflow won't let me.

Why is my Sequelize query so slow? (nested-joins using findAll)

1 Answers1