Build: reducing query count, prefetching, or intermediate tables

[agjohnson]

After a few different attempts, it seems like the query complexity added with prefetching causes queries to take much longer than without prefetching (however query count is much much lower, 30 vs 120 queries).

For more background on the removal, see:

• Try reverting prefetch changes for project/version listing views #11621

The most expensive queries here are getting the latest build (which I think can be external) and getting the latest, non-external, successful build.

Some options here are:

• Use caching after database query. Saves some lookups but initial is still expensive and savings inconsistent.
• Use materialized views to cache at the database level. Managing these views is harder and outside ORM
• Use an intermediate table for latest/latest successful build on Project. This requires updating the relationship after each build.

[humitos]

Use an intermediate table for latest/latest successful build on Project. This requires updating the relationship after each build.

Do we need an intermediate table for this? Why not just OneToOne fields on the Project object?

[agjohnson]

Yup, it could be any relation, not necessarily an intermediate table.

[humitos]

I thought a little more about this and I realized that:

• We need to use an API call from the builder to update the DB object
• We can use on_success and on_failure Celery handlers to update lastest_successful_build and latest_builds fields respectively
• It's not clear if we need these latest builds to be linked to the Project or to the Version objects 🤔

[agjohnson]

It's not clear if we need these latest builds to be linked to the Project or to the Version objects

Good point, I think both if we want to speed up both pages. Project and version listing both show the latest build and use has_good_build.