Slow queries : what can I do?

Questions and Answers
1

Hi there,

I’m currently experiencing an issue making my app unusable: very slow queries.

I have to retrieve about 30 entities, composed of simple components and pictures, but the requests are unusually long, around 30s.

I searched quite a bit for a solution, tried to better target the data to be recovered in the controller, and finally reduced the time by almost 2. But 14s is far too long for a correct user experience, especially considering the amount of data to get back.

I saw that several people encounter the same problem as me, and that it would be related to auto populate.

I have very few backend and databases knowledge and this is the first time I created a backend. (that’s why I use Strapi ^^’)

Do you have any solution for that ?

Thanks,

Valentin

System Information
  • Strapi Version: 3.2.5
  • Operating System: Debian 10.6 - Buster
  • Database: MongoDB
  • Node Version: 15.1
  • NPM Version: 7.0.3
2

Can you share your ./api/*/models/*.settings.js files and the components that are related to this model? Also, the query request you use to fetch the data so we could make some tests locally.

1 Like
3

I’ll 2nd @sunnyson here, if you can share your project code or the parts he mentioned so we can play locally that would help us try to figure out a good solution for you.

Also when it comes to relations, it’s generally my suggestion to use a SQL database as we are aware of some pain points when it comes to relation population performance on MongoDB.

1 Like
4

Thanks to both of you.

Here is a copy of my models files :

events.settings.json 
{
  "kind": "collectionType",
  "collectionName": "events",
  "info": {
    "name": "Events"
  },
  "options": {
    "increments": true,
    "timestamps": true,
    "draftAndPublish": false
  },
  "attributes": {
    "event_name": {
      "type": "string"
    },
    "event_iteration": {
      "type": "component",
      "repeatable": true,
      "component": "entity.event-entity"
    }
  }
}
components/entity/event-entity.json 
 {
  "collectionName": "components_entity_event_entities",
  "info": {
    "name": "Event_entity",
    "icon": "arrow-right"
  },
  "options": {},
  "attributes": {
    "name": {
      "type": "string"
    },
    "illustration": {
      "model": "file",
      "via": "related",
      "allowedTypes": [
        "images",
        "files",
        "videos"
      ],
      "plugin": "upload",
      "required": false
    },
    "main_pictures": {
      "type": "component",
      "repeatable": false,
      "component": "content.content"
    },
    "additional_pictures": {
      "type": "component",
      "repeatable": false,
      "component": "content.content"
    }
  }
}

Also when it comes to relations, it’s generally my suggestion to use a SQL database as we are aware of some pain points when it comes to relation population performance on MongoDB.

Good to know for my next projects. :ok_hand:

5

Can you also share the query / request that you use?

6

Strapi currently does not officially support Node v15.* . The recommended version is v14.*
Can you install nvm and switch to Node’s LTS version and repeat the tests? Also don’t forget to delete node_modules folder and reinstall all the packages before repeating the tests.

7

Ok, I switch my node version from v15.1 to v14.15.1. (nvm :star_struck:). Unfortunately, it doesn’t solve the actual issue but it can surely improve stability.

For my query, I did not change it. It’s the default find query.

I don’t know if it can help, but here is my controller :

/api/controllers/events.js 
"use strict";

const { sanitizeEntity } = require("strapi-utils");

module.exports = {
  async find(ctx) {
let entities;
if (ctx.query._q) {
  entities = await strapi.services.events.search(ctx.query);
} else {
  entities = await strapi.services.events.find(ctx.query);
}

return entities
  .map(entity => {
    const event = sanitizeEntity(entity, { model: strapi.models.events });
    const formattedEvent = [];

    event.event_iteration.forEach(iteration => {
      formattedEvent.push({
        event_name: event.event_name,
        event_id: event.id,
        created_at: event.created_at,
        iteration_name: iteration.name,
        illustration: iteration.illustration
      });
    });

    return formattedEvent;
  })
  .flat();
  }
};
8

Interesting behavior here. We need to make more tests. In my case for 10 events with 10pictures each is working fine, 500-600ms for the first request(after restarting strapi), and 50-100ms for others.

So you have 30 events? How many images your events do contain in main_pictures and additional_pictures ?

9

Yeah, it worked well during dev with my tests files too. I had like 5 events, with 2 iterations and something like 10 pictures max. I don’t really know from when the queries become so slow, I guess it’s more or less exponential.

So, there are currently 27 events. Each events has at least 2 iteration, up to 6 for now. And each iteration has between 15 to 20 main_pictures, and 20 to 100 additional_pictures.

To give you a bit of context, this is a site for a photographer that covers events every year. So there is an event which contains one iteration per year.

10

Indeed, I could replicate your issues after creating a few more events with pictures of them.

The Media library takes up to 15seconds to load 8 pictures. I’ve added the same pictures to all events.
At the first view, it seems that problem is related to files Relations.

GET /events → 5 seconds response time (10 events)
GET /upload/files → 15 seconds response time (8 pictures)

If I restart strapi after creating events & pictures, it gets back to normal.
After strapi restart:
GET /events → 100ms response time (10 events)
GET /upload/files → 20ms response time (8 pictures)

We will look deeper into this case to have a bit more information about what’s happening to open a bug issue.

11

That’s a good thing that you could reproduce the slow query behavior.

In my case, however, the behavior is a little different. I have no slowdown to access the Media Librairy. And I tried to restart strapi but it doesn’t solve the slowness in requests.

12

Hi guys,

Just checking if you found a way to fix this query behavior ? :slightly_smiling_face:

I guess it’s a deep issue asking for a long time of searches. Maybe you have a workaround in mind I can use waiting for a long term solution ?

1 Like
13

I had the same issue couple days ago. Wasn’t even using images but rather 4 to 5 relation fields on mondoDB within one collection. Couldn’t figure out a fix and since it was the initial stage of the project decide to not use Strapi.
If you guys figure out a fix let me know

1 Like
14

Hi every one,

I’m just checking if there is something new about this issue ?

1 Like
15

Just asking if you found a solution to this problem?

1 Like
16

Any news? I’ve the same problem

1 Like
17

I too have the problem where the requests are very slow even on localhost using the build version. At first it’s about 300-500 ms and successive requests are about 50-150 ms.
When I wait around 10 seconds the request time is 300-500 ms again.

Is there a sleep mode or something similar?

18

I didnt realise this was a bug, But I also have this. If I have to upload a 3 minute 60mb video then it often takes at least 20 minutes

19

I’m facing the same issue after create few relations all of my queries became very slow. please suggest any solution for this problem

20

yes, successive requests are faster. I’m facing the similar issue.