Slow associations in SequelizeJS

Question

I am trying to diagnose the cause of some slow downs in my Express app which is using SequlizeJS as the ORM. I have a model that has a 2x hasMany & a hasOne relation with 2 other models:

Update: I've made the associations within the define call using the classMethods#associate function.

// Model1
classMethods: {
    associate: function(models) {
        Model1.hasMany(models.Model2);
        Model1.hasMany(models.Model3);
        Model1.hasOne(models.Model2, {as: 'next', foreignKey: 'model2_next'});
    }
}

// Model2
classMethods: {
    associate: function(models) {
        Model2.belongsTo(models.Model1, {foreignKey: 'model2_next'});
    }
}

if I query them in the following manner:

db.Model1.find({
    where: { /* Simple where statement */ },
    include: [
        db.Model2,
        db.Model3,
        { model: db.Model2, as: 'next' },
    ]
}).complete(function(err, data) {
    res.json(data);
});

It can take between 8-12seconds to respond. However, if I query Model2 separately and merge them manually using the async & lodash libraries:

async.parallel({
    model2: function(callback) {
        db.Model2.findAll({
            where: { /* Simple where statement */ }
        }).complete(callback)
    },
    model1: function(callback) {
        db.Model1.find({
            where: { /* Simple where statement */ },
            include: [
                db.Model3,
                { model: db.Model2, as: 'next' },
            ]
        }).complete(callback);
    }
}, function(err, data) {
    var response = data.model1.values;
    response.Model2 = data.model2.map(function(Model2){ return Model2.values });

    res.json(response);
})

it takes between 60-100ms.

I've tried switching from MySQL to PostgreSQL and whilst PostgreSQL was fractionally faster, it was a matter of 2-3%!

What is causing Sequelize to take so much longer than the split query and is there a way I can speed this up?

Answer 1

Sequelize slows down when you add :M relations to your include. :M relations result in duplicate rows in your sql result, so we have to spend time deduplicating that and parsing it into models.

For optimal performance you could leave your :1 relations in your include but do the :M in seperate queries.

Of course the query itself could also be slow, but most likely it's the result of Sequelize overhead - Try running the query directly on the database.

(Disclaimer: Sequelize core developer)

What version are you running on? The initial number you reported sounds high, but we have heard of those numbers before we did some optimizations, try testing against the latest git master.

We're always working on optimizing the code for these scenarios, but deduplicating 20.000 rows to 5.000 rows will always require some cpu cycles.

Answer 2

For your hasMany includes (and after proper indexing), consider adding separate: true,.

It basically executes include blocks in parallel, instead of sequentially.

I had a query that went from 10 seconds to less than 200 miliseconds after that:

include: [
    {
        model: MyModel,
        as: 'myModels',
        separate: true, // does magic; only with .hasMany associations
    },
]

Documentation: https://sequelize.org/api/v7/interfaces/includeoptions#separate

Answer 3

in your model association, sequelizejs don't create index

add {foreignKeyConstraint: true} to all association.

Model1.hasMany(models.Model2, {foreignKeyConstraint: true});
Model1.hasMany(models.Model3, {foreignKeyConstraint: true});
Model1.hasOne(models.Model2, {as: 'next', foreignKey: 'model2_next', foreignKeyConstraint: true});
Model2.belongsTo(models.Model1, {foreignKey: 'model2_next', foreignKeyConstraint: true});