VCIO-next: API performance issues (packages endpoints)

[tdruez]

Let's focus on the Package-related endpoints for now as those are the ones used to collect vulnerability data in DejaCode.

Those tests were run on a clean install of VCIO with only the nginx.NginxImporter data set.

Package.objects.all().count()  # 88
Vulnerability.objects.count()  # 39

It's a very small amount of data but somehow looking at a single Package triggers over a thousand queries.

• Package list /api/packages/ (on only 88 packages) -> 6,124 queries: 5706 similar queries. Duplicated 73 times.
• Package details /api/packages/63 -> 1,329 queries: 1230 similar queries. Duplicated 16 times.
• Bulk search /api/packages/bulk_search (providing the 88 purl): 39,925 queries.

This is quite problematic in the context of batch data collection using the VCIO API.
The PackageSerializer and related QuerySets require optimization.
Once done, make sure to implement unit test using the assertNumQueries to make sure that future code change do not add uncontrolled queries back.

Related issues:

• VCIO-next: API performance issues (vulnerabilities endpoint) #1492
• CRAVEX: Vulnerability Lookup and base app dejacode#94 (comment)
• VCIO-next: For packages, enable sorting versions in database queries, or store additional computed fields #1549

For bulk lookup, we track this here:

• VCIO-next: Improve VCIO bulk API package lookup performance #1561