Fix SQL N+1 problem in Packages::Go::RefreshPackagesService
In working on !29642 (closed), I have introduced a SQL N+1 problem. I am creating this issue in order to whitelist the API so I can run tests and verify that the logic is valid.
Packages::Go::RefreshPackagesService
scans a repository for semver tags containing go.mod
files, and creates a Packages::Package
for each (path, tag) pair. These packages may also have Packages::PackageFile
entries. To avoid duplicating existing records, Packages::Go::CreatePackageService
queries the database to check for existing records. This creates an N+1 SQL problem. The solution should be to preload records to limit the number of SQL queries, but my attempts have failed. From this discussion !29642 (comment 348692838):
The above means
RefreshPackagesService
has a serious N+1 SQL query problem, queryingproject.packages
andpackage.project_files
. I tested a variety of ideas, but nothing reliably preventedCreatePackageService
from making tons of queries. You can see here that I tried creating a Project scope. I did some tests in the rails console, and it seems likewhere
queries (e.g.Package#with_file_name
) skip the preloaded results. I don't have much experience with Rails or ActiveRecord, so I would appreciate suggestions.