CODEWITHSUNDEEP
sqlnode.jspostgresqlpaginationsequelize.js
spalm
spalm

Reputation: 128

Pagination with Sequelize using limit takes 4 times longer to load. How can I optimize?

I am currently using Sequelize with Node.js and I wanted to incorporate pagination. The code works, however, Sequelize takes 4 times more time to load than what it used to. 13 seconds is not acceptable as a loading time for each page. I tried both findAll and findAndCountAll, but as soon as I add the limit to the options, it becomes really slow. Here is the query used in node.js:

return req.models.BookingGroup.findAndCountAll({
    attributes: group_attrs,
    where: group_where,
    include: [
    {
        model: req.models.Booking,
        attributes: book_attrs,
        where: booking_where, 
        include: [
          {
            model: req.models.Guest,
            attributes: [
             'id',
             'firstname',
             'lastname',
             'email', 
            ],
           },
           ],
         }
        ],
       offset,
       limit
   })
   .then(({count, rows}) => {
      res.send(200, {count, groups: rows})
      return {count, groups: rows}
   })
   .catch(err => console.log("##error ", err))

Am I doing something wrong? It only returns 70 entries, I don't think it should take this long. I haven't found anything online, and I don't know if it is a problem with Sequelize but any help is appreciated !

Upvotes: 3

Views: 4054

Answers (1)

Vishanth
Vishanth

Reputation: 1370

I came across a performance issue when using findandcountall. In my case, Sequelize formed a lengthy JOIN statement in findandcountall (You can check this with passing the option logging: console.log).

However, instead of using findAndCountAll, I used .count() to get the number and .findAll() to get the results. This actually turned out to be much faster than using the findAndCountAll().

const count = await req.models.BookingGroup.count({
  where, include, distinct: true, ...
});

const bookingGroup = await req.models.BookingGroup.findAll({
  where, include, attributes, limit, offset, ...
});

res.send({ bookingGroup: [], count });

Upvotes: 4

Related Questions