Strapi REST API responses are very slow if `populate=*` is used

[komape]

Bug report

Required System information

• Node.js version: v16.13.0
• NPM version: 8.8.0
• Strapi version: v4.1.9
• Database: SQLite
• Operating system: macOS v12.3.1

Describe the bug

Requesting the first page with a page size of 10 and an overall count of around 3500 elements takes nearly 20 seconds if I add populate=* to the query params. Without it takes 20 to 30ms (Strapi runs locally). The elements contain images and some component elements. Is it normal that it takes so much more time? No filters or similar are used...

Steps to reproduce the behavior

1. Request http(s)://$HOST/api/$COLLECTION?populate=*&pagination[pageSize]=10&pagination[page]=2
2. Request http(s)://$HOST/api/$COLLECTION?pagination[pageSize]=10&pagination[page]=2

Expected behavior

Using populate=* should not take nearly 20 seconds if it takes 20ms without.

Screenshots

n/a

Code snippets

n/a

Additional context

n/a

[derrickmehaffy]

I wouldn't called 20 seconds "normal" but there should be an exponential increase yes, though it would largely depend on how many items are populated because of it. Caching could help here I think via something like the REST Cache

[komape]

REST Cache isn't really improving the response time :/

[derrickmehaffy]

There are some key information missing here:

• We don't know how complex your model structure is
• We don't know how many different types of relations/components/ect you are populating

Likewise

Database: SQLite

This limits the database input/output to the speed of your storage (assuming nothing else is pulling/pushing from your storage at the same time) as SQLite has absolutely no speed benefits from indexes, database server memory caches, ect and because it's a single file you may also be hitting your OS's maximum file connection limits.

[komape]

So far the model has three components, each having one text field. All in all there are nearly 4000 objects stored. I'm the only client on the machine and as I sad, if I pull the data directly via SQL queries it's much faster. For now, I switched to PostgreSQL but it would be awesome to get back to SQLite because it's just more straightforward and fulfills my requirements (small low traffic web app).

[derrickmehaffy]

So far the model has three components, each having one text field. All in all there are nearly 4000 objects stored. I'm the only client on the machine and as I sad, if I pull the data directly via SQL queries it's much faster. For now, I switched to PostgreSQL but it would be awesome to get back to SQLite because it's just more straightforward and fulfills my requirements (small low traffic web app).

We absolutely 100% do not recommend nor support running Strapi in production using SQLite and at the moment we have no intention to. It is strictly for rapid prototyping, testing, demos, ect.

If you need any level of performance you'll be asked to test on an actual database.

I'm going to set this one as closed as it appears moving to PG more or less solved your performance issues.

[komape]

Can you explain why you don't intend to support SQLite?

According to SQLite itself it's totally capable handling the traffic of low traffic websites [1]. Combined with the easy setup and usage it's very interesting.

[1] https://www.sqlite.org/whentouse.html#website

[Paratron]

Wait a second sqlite is f***ing fast!

And its a "real database", what the hell.

Btw, I came here because I have performance issues in a 4000ish items collection, too - but only in the admin interface. 14s to show the first 10 items in a list. 60ms for the same query over the Strapi REST api. Something is weird here.

Okay I didnt use populate on REST but imho the admin list call shouldnt either.

[derrickmehaffy]

Wait a second sqlite is f***ing fast!

And its a "real database", what the hell.

Btw, I came here because I have performance issues in a 4000ish items collection, too - but only in the admin interface. 14s to show the first 10 items in a list. 60ms for the same query over the Strapi REST api. Something is weird here.

Okay I didnt use populate on REST but imho the admin list call shouldnt either.

The admin does and SQLite has decent single table performance but not when it comes to joins and there are joins regardless, especially in the admin with the system fields (created/updated by).

If you compare the performance of SQLite 3 to a minimum spec MySQL you'll see the difference.

We added SQLite literally to use in demos and SQLite has literally no redundancy. This is why we don't recommend it in production (plus the performance issues since it's a single file you run into OS limits of total active watchers, file size limitations, fragmentation, ect)

---

I won't battle the semantics here, SQLite is not a database we recommend for real world usage and we have absolutely no expectation to focus on its performance unless the fixes affect the other databases as well. SQLite might get performance increases by chance through more optimizion of the database logic.

Btw when I refer to a "real database" I mean a database service, which has query caching, multi file storage, memory indexes, access security, backup tooling baked in, fault tolerance and load balancing capabilities, scaling and clustering, active failover capabilities.

Typical database features that aren't possible on a single file.

[komape]

Thanks for the explanation! Would still appreciate it if it gets better support for users like me who want to use it in production on their own responsibility 🙏

[derrickmehaffy]

Thanks for the explanation! Would still appreciate it if it gets better support for users like me who want to use it in production on their own responsibility 🙏

Please feel free to submit pull requests :)

The benefits of open source go both ways as our team is small and focus must be razor sharp on what we believe the important topics are. As such we can't prioritize everything, but those in the community are welcome to work on topics that impact them or interest them.

[strapi-bot]

This issue has been mentioned on Strapi Community Forum. There might be relevant details there:

https://forum.strapi.io/t/sqlite-for-production/1679/9