DependencyTrack / DependencyTrack/dependency-track
Investigate areas where queries with `null` parameters are bypassing the DataNucleus query compilation cache
- 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
Assessment
This issue has not been assessed yet.