/api/v1/vulnerability/component/{uuid} endpoint performs horribly for large portfolios

[nscuro]

Current Behavior

The /api/v1/vulnerability/component/{uuid} REST API endpoint takes extremely long to respond in large (>10k projects) portfolios (occasionally >30sec).

The persistence logic for serving the endpoint is here: https://github.com/DependencyTrack/hyades-apiserver/blob/c55d7158f881b4e8a24bfa5f2f30032b5dd654d4/src/main/java/org/dependencytrack/persistence/VulnerabilityQueryManager.java#L351-L381

I suspect that collecting the projects affected by each vulnerability one-by-one, in a for loop, is the culprit. By the looks of it, it could be a single SQL query, which would perform a lot better.

Steps to Reproduce

Expected Behavior

/api/v1/vulnerability/component/{uuid} endpoint should be responsive.

Hyades Version

5.6.0-SNAPSHOT

Repository Type

Hyades apiserver

Browser

N/A

Checklist

• I have read and understand the contributing guidelines
• I have checked the existing issues for whether this defect was already reported

[nscuro]

Actually similarly bad query patterns can be seen for /api/v1/vulnerability/project/<uuid> as well: https://github.com/DependencyTrack/hyades-apiserver/blob/c55d7158f881b4e8a24bfa5f2f30032b5dd654d4/src/main/java/org/dependencytrack/persistence/VulnerabilityQueryManager.java#L463-L480