Findings search / vulnerability audit performs pagination in-memory

[nscuro]

Current Behavior

The findings search / global vulnerability audit logic performs pagination in-memory, instead of at part of the SQL query:

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

Lines 119 to 124 in 3837f0c

	final Query<Object[]> query = pm.newQuery(Query.SQL, Finding.QUERY_ALL_FINDINGS + queryFilter + (this.orderBy != null ? " ORDER BY " + sortingAttributes.get(this.orderBy) + " " + (this.orderDirection == OrderDirection.DESCENDING ? " DESC" : "ASC") : ""));
	PaginatedResult result = new PaginatedResult();
	query.setNamedParameters(params);
	final List<Object[]> totalList = query.executeList();
	result.setTotal(totalList.size());
	final List<Object[]> list = totalList.subList(this.pagination.getOffset(), Math.min(this.pagination.getOffset() + this.pagination.getLimit(), totalList.size()));

This means it loads everything into memory first, just to discard a big chunk of it immediately. This leads to poor performance on large portfolios and can even lead to OutOfMemoryExceptions.

Steps to Reproduce

1. See code snippet above.

Expected Behavior

Pagination should happen in the database, using offset / limit clauses in the SQL query:

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

Lines 1579 to 1599 in 3837f0c

	/**
	* @since 4.12.0
	* @return A SQL {@code OFFSET ... LIMIT ...} clause if pagination is requested, otherwise an empty string
	*/
	public String getOffsetLimitSqlClause() {
	if (pagination == null || !pagination.isPaginated()) {
	return "";
	}
	final String clauseTemplate;
	if (DbUtil.isMssql()) {
	clauseTemplate = "OFFSET %d ROWS FETCH NEXT %d ROWS ONLY";
	} else if (DbUtil.isMysql()) {
	// NB: Order of limit and offset is different for MySQL...
	return "LIMIT %s OFFSET %s".formatted(pagination.getLimit(), pagination.getOffset());
	} else {
	clauseTemplate = "OFFSET %d FETCH NEXT %d ROWS ONLY";
	}
	return clauseTemplate.formatted(pagination.getOffset(), pagination.getLimit());
	}

Dependency-Track Version

4.12.0-SNAPSHOT

Dependency-Track Distribution

Container Image, Executable WAR

Database Server

N/A

Database Server Version

No response

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