When spaces are loaded from
GET /:spaceId in routes/space.js
The full space (including related cards, connections, etc.) is retrieved from controllers.space.findDetailed(spaceId) in controllers/space.js. The various seperate queries inside findDetailed combined take a long time. I initially did this as a single query but it was crazy slow so I split it into getting all the cards for the space, all the connections etc. seperately.
Goal:
Is there a way to speed this up? I’ve specified indexes in the various model files for each entity (eg cards), but I’m not sure if there’s more that can be done to optimize findDetailed?
It’s a bit tough to optimize this without having access to the production database or a database that’s filled with a bunch of mocked data. Do you have a database seed file somewhere so I can test with a local database? If not, I can create one on my own
Alternatively, I could test on the production DB, which would give me more insights about created indexes and such.
Here are some first thoughts based on the source code:
Are the SQL indexes actually created in the database? I don’t find migration scripts in the source code, and I sometimes have problems with Sequelize not doing automatic migrations like I would expect. I assume they are properly created, but just want to double-check.
I am counting 8 sequential asynchronous calls to the database in controllers.space.findDetailed(spaceId). Did you split the big single query into 8 smaller queries before or after you set the DB indexes? I assume that this would be faster as one optimized SQL query.
If it turns out that the 8 queries are indeed faster than one big query, you could execute the 7 queries after the first one concurrently instead of sequentially, which should speed this up significantly.
you could execute the 7 queries after the first one concurrently instead of sequentially
that’s a great idea! something like promise.all sounds like it could be an easy win
Are the SQL indexes actually created in the database
i believe that the sequelize way is to define the indexes in the model. so no migrations are needed. (adding new columns is also also done by updating the model file directly. This easiness would be dangerous in big orgs, but I think it’s fine for us as.
Did you split the big single query into 8 smaller queries before or after you set the DB indexes? I assume that this would be faster as one optimized SQL query.
I tried making the queries into a single one after having indexes. For the way I did it , it was still very slow. Assuming that how to make the single query fast is non-obvious and that making the queries concurrent makes a huge improvement, joining them into one might not be required.
Do you have a database seed file
no i don’t but that would be a good thing to have for the future anyways because i also have small dev dbs on my local machines that could be beefed up.
Let’s try making the queries concurrent and see how much farther we need to go from there, if at all.
