DependencyTrack / DependencyTrack/dependency-track

Investigate areas where queries with `null` parameters are bypassing the DataNucleus query compilation cache

Open
#2,540 0 comments 0 reactions 0 assignees View on GitHub
enhancement p3
Dominant language
Java
Stars
4.2k
Forks
811
Avg merge
8h 39m
Merged PRs (30d)
237

Description

### Current Behavior

DataNucleus will compile every single JDOQL query into a generic expression tree, and from there into SQL.

It has caches in place for each of these steps, refer to https://www.datanucleus.org/products/accessplatform_6_0/jdo/query.html#cache

While investigating a different issue, I came across this check in DataNucleus:

https://github.com/datanucleus/datanucleus-rdbms/blob/datanucleus-rdbms-6.0.3/src/main/java/org/datanucleus/store/rdbms/query/JDOQLQuery.java#L313-L328

Essentially, if any of the provided parameters to a query is `null`, the query compilation cache will be bypassed. This can have a big impact on resource utilization in cases where a certain query is both:
* Used very frequently
* *Expecting* `null` parameter values

This is the case for our `getVulnerableSoftwareByCpe23` method for example, which is invoked for every single CPE during NVD mirroring:

https://github.com/DependencyTrack/dependency-track/blob/93321f8a82f2eee4c2d42bb6ffc664ee54a82b4d/src/main/java/org/dependencytrack/persistence/VulnerableSoftwareQueryManager.java#L126-L133

Depending on the reported version range, the `*Excluding` and `*Including` parameters may be `null`.

The solution is include `null` values as part of the JDOQL query, instead of passing it as parameter:

```java
final String myParam = null;
final var query = pm.newQuery(...);
if (myParam == null) {
query.setFilter("field == null");
} else {
query.setFilter("field == :myParam");
query.setParameters(myParam);
}
```

This way, the query cache can be utilized.

### Proposed Behavior

Identify other areas in the code where queries are called frequently, and parameters may be `null` in certain cases.

Apply the optimization outlined above to ensure that the query compilation cache can be utilized.

### Checklist

- [X] I have read and understand the [contributing guidelines](https://github.com/DependencyTrack/dependency-track/blob/master/CONTRIBUTING.md#filing-issues)
- [X] I have checked the [existing issues](https://github.com/DependencyTrack/dependency-track/issues) for whether this enhancement was already requested

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.