The vulnerability query API performs M*N queries

[grahamc]

Current Behavior

This curl command can take 10s of minutes to complete:

curl -v --header 'X-Api-Key: ...' https://dtrack/api/v1/vulnerability/project/uuid-goes-here

it appears to be caused by an M*N query pattern.

On one of my projects I have ~5k components. That API endpoint calls getVulnerabilities, which:

dependency-track/src/main/java/org/dependencytrack/persistence/VulnerabilityQueryManager.java

Lines 479 to 494 in dc1241c

	*/
	public List<Vulnerability> getVulnerabilities(Project project, boolean includeSuppressed) {
	final List<Vulnerability> vulnerabilities = new ArrayList<>();
	final List<Component> components = getAllComponents(project);
	for (final Component component: components) {
	final Collection<Vulnerability> componentVulns = pm.detachCopyAll(
	getAllVulnerabilities(component, includeSuppressed)
	);
	for (final Vulnerability componentVuln: componentVulns) {
	componentVuln.setComponents(Collections.singletonList(pm.detachCopy(component)));
	componentVuln.setAliases(new ArrayList<>(pm.detachCopyAll(getVulnerabilityAliases(componentVuln))));
	}
	vulnerabilities.addAll(componentVulns);
	}
	return vulnerabilities;
	}

1. Queries all the components,
2. For each component, runs getAllVulnerabilities which:
   a. queries for vulnerabilities
   b. For each matched vulnerability queries for aliased vulnerabilities
3. For each found component vulnerability, queries for vulnerability aliases again

I'm pretty sure this is performing many tens of thousands of queries, since this process takes tens of minutes to complete on my project with ~5k components and a few hundred identified vulnerabilities.

Steps to Reproduce

1. Upload a large SBOM (I can provide one privately)
2. Upload the SBOM again as VEX data and trigger analysis
3. Query for impacted vulnerabilities

Expected Behavior

I would expect this process to take a second or so under normal circumstances, though even a minute would be a lot better :).

Dependency-Track Version

4.14.2

Dependency-Track Distribution

Container Image

Database Server

PostgreSQL

Database Server Version

17

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

[grahamc]

btw I would be happy to sponsor the resolution of this issue.

[nscuro]

Note, this was improved somewhat in v5 already: https://github.com/DependencyTrack/hyades-apiserver/blob/6f7575962087e6d66ecc40e5c7758f17cafd0ed5/apiserver/src/main/java/org/dependencytrack/persistence/VulnerabilityQueryManager.java#L405-L420

Basically instead of querying components -> vulnerabilities, we query vulnerabilities in the project first and only then load affected components. And the latter is done in a single query, not one query per vulnerability.

Separately, I'd recommend you use the /api/v1/finding/project/<uuid> endpoint instead. That one is backed by a proper SQL query, even in v4, and performs any additional enrichment in batches. It's also what the UI uses.

[grahamc]

Cool, thanks @nscuro! Any chance you could email me? Graham @ determinate dot systems. I sent you one a while ago but never heard back :). No is fine, but I'd appreciate it and just wanted to try again.

[nscuro]

@grahamc Found the email and responded. Somehow went under in my inbox.