What is the best way to bulk update entries?

Questions and Answers Database-SQL
1

Hello,

I have about 1000 records in my database that need to be updated every minute. Currently I’m using the update() function. But it takes a long time until each entry is updated:

Object.values(data).map(async (itemFromExternalApi) => {
  const existingItem = existingItems.find(
    (existingItem) => existingItem.assignment === prefix + itemFromExternalApi.id
  );

  if (existingItem) {
    try {
      await strapi.query("facilities").update(
        { id: existingItem.id },
        {
          spots: {
            free: itemFromExternalApi.free,
            freeText: Number(itemFromExternalApi.free) > 0 ? "free" : "occupied",
            capacity: itemFromExternalApi.capacity,
          },
          state: itemFromExternalApi.status === "1" ? "open" : "closed",
          updated_at: itemFromExternalApi.timestamp,
        }
      );
    } catch (e) {
      console.log(e);
    }
  }
});

If someone sends a request to strapi while the code ist running, it takes up to one minute until the user receives a response. Now I’m searching for a better solution.

Best Regards,
Marius

1 Like
2

I think the main problem here is with the spots component. Since updating entries with components takes a longer time.

I would recommend to omit the component and create 3 new fields:

  • spots__free
  • spots__freeText
  • spots__capacity

This will cost you less time to update the entry.

Components are good for static data, like blog articles, static websites and etc. But if you want to update them periodically(in your case every minute) then you should avoid using them.

To display them in API properly as an object, you can write a custom controller with a function that modified the results:

Example code:

const _ = require('lodash');
let entry = {
            title: 'my super title',
            spots__free: 'free here',
            spots__freeText: 'freetext here',
            spots__capacity: 'capacity here',
            id: 1
          };

entry.spots = {};
_.map(_.pickBy(entry , (value,key)=>_.startsWith(key,'spots__')), function(value, key) {
    entry.spots[_.replace(key, 'spots__', '')] = value;
    _.unset(entry , key);
});

Result:
image

P.S. I update more than 100k entries every 5-10minutes, without any performance issues.

1 Like
3

WOW. Thank you! I didn’t know that! That’s much faster!
But requests to my Strapi instance are still a few seconds slower while the code to update the entries is executing. Are you facing the same behavior?

And where to put your example Code? Into the Controller?

Best Regards
Marius

4

No. You need to examine all the requests and what exactly increases the time and tweak them more if you want to gain more speed and still update them very often. What DB do you use, mongo, MySQL, postgres? Is it an external db or it is hosted on the same server with strapi?

Yes, inside the find and findone controllers, so the end-users could get a well-formatted response instead of spots__ fields.

5

Thank you for the hint! For development I’m using an external DB. But on my production Server, the DB is on the same server and I’m using localhost to establish a connection. There are no performance problems. Now I know what the problem is. Thanks again!

1 Like
6

This is a great post and replies. But what if I had to use only components? How to handle that case?

Running the Cron job is making the CPU 100% all the time and the admin panel inaccessible