A Simple Optimization for Your Sequelize Query

Feb 18, 2021

The Problem#

There was a critical slowdown on the staging server front-end pages (at my work), for example, sometimes the response has faced timeout. I tried to reduce the size of a GraphQL query and optimized the pages' fetch function to solve the issue, but it didn't work well. So I started to guess that problem is on Sequelize query.

The Conclusion#

In conclusion, I added separate: true to the include option of QueryBuilder. It only works with hasMany entities (many-to-many associations).

js
1const asset = await Asset.findAll({
2  order: [['id', 'ASC']],
3  where: {
4    id: req.params.id,
5  },
6  include: [
7    {
8      model: User,
9      as: 'owner',
10    },
11    {
12      model: AssetProperty,
13      as: 'properties',
14      include: [
15        {
16          model: AssetImages.scope('active'),
17          as: 'images',
18          separate: true, // <-- here
19        },
20      ],
21    },
22    {
23      model: AssetChildren,
24      as: 'children',
25      separate: true, // <-- here
26    },
27    {
28      model: AssetReputation.scope('active'),
29      as: 'reputations',
30      separate: true, // <-- here
31      include: [
32        {
33          model: AssetReputationForm.scope('active'),
34          as: 'forms',
35          separate: true, // <-- here
36          include: [
37            {
38              model: Authority,
39              as: 'writableAuthorities',
40              separate: true, // <-- here
41            },
42          ],
43        },
44      ],
45    },
46  ]
47})

Performance Improvement#

Pass logging as an option for Sequelize to make it easier to find where the performance problem is.

js
1import { Sequelize } from 'sequelize';
2import colors from 'colors/safe';
3

4const createConnection = (database, user, password, options) => {
5  const loggingOptions = {
6    benchmark: true,
7    logging: (logStr, execTime, options) => {
8      if (!options) {
9        options = execTime;
10        execTime = undefined;
11      }
12        
13      let col = null;
14      switch (options.type) {
15        case 'SELECT':
16            col = colors.blue.bold;
17            break;
18        case 'UPDATE':
19            col = colors.yellow.bold;
20            break;
21        case 'INSERT':
22            col = colors.green.bold;
23            break;
24        default:
25            col = colors.white.bold;
26            break;
27      }
28

29      if (execTime) {
30        if (execTime >= 10) {
31            col = colors.red.bold;
32            console.log(colors.magenta.bold(`[${execTime} ms]`), col(logStr));
33        } else {
34            console.log(col(logStr));
35        }
36      }
37    }
38  }
39

40  return new Sequelize(database, user, password, { ...options, ...loggingOptions })
41}

Before#

terminal
1[107 ms] Executed (default): SELECT `Asset`.* ...

The execution time of the query was 107ms. Since we are experimenting with a small number of data in the local environment, it will take longer when dealing with larger amounts of data.

After#

terminal
1[27 ms] Executed (default): SELECT `Asset`.* ...

The execution time has reduced by 80ms for the same query since its nested queries have separated into small pieces. We are not seeing the same problems currently, so I think it was a simple but effective improvement.