I have a database that contains say 100,000 rows. I try to query 20,000 rows using an id + a between query on an indexed long. It takes ~1.5 seconds in average, sometime up to 5-10s.
Data is frequently written to the database, about 40 times per second. Is that an issue? About 2400 rows are added per second in total between these writes.
The relativeSamplingTimestamp that is used as index has 2 values for the same timestamp in all cases (2 different data streams with same time). Like:
1
1
2
2
3
3
...
Expected behavior
I would expect it to be under a 100ms?
Actual behavior
Takes 1-10 seconds. Average 1.5s.
Code
Code
```java
Schema:
@Id
public long id
public ToOne localSession;
@Convert(converter = Converters.SerializableConverter.class, dbType = byte[].class)
private HashMap samples;
@Convert(converter = Converters.InstantConverter.class, dbType = Long.class)
private Instant receptionTimestamp;
@Nullable
@Index
private Integer relativeSamplingTimestamp;
@Convert(converter = Converters.InstantConverter.class, dbType = Long.class)
@Index
@Nullable
private Instant absoluteSamplingTimestamp;
Query:
samplesRelativeTimestampBetweenQuery = sampleBox.query().equal(Sample_.localSessionId, 0)
.between(Sample_.relativeSamplingTimestamp, 0, 0).build();
Which i then call with parameters like:
samplesRelativeTimestampBetweenQuery.setParameter(
Sample_.localSessionId, localSessionId)
.setParameters(Sample_.relativeSamplingTimestamp, startTimestamp, endTimestamp)
.find())
```
Seems like between doesn't use the index. In your case "time series" would make sense I guess. It's a special feature that's better than a secondary index and supports between.
Build info
Steps to reproduce
I have a database that contains say 100,000 rows. I try to query 20,000 rows using an id + a between query on an indexed long. It takes ~1.5 seconds in average, sometime up to 5-10s.
Data is frequently written to the database, about 40 times per second. Is that an issue? About 2400 rows are added per second in total between these writes.
The relativeSamplingTimestamp that is used as index has 2 values for the same timestamp in all cases (2 different data streams with same time). Like:
1 1 2 2 3 3 ...
Expected behavior
I would expect it to be under a 100ms?
Actual behavior
Takes 1-10 seconds. Average 1.5s.
Code
Code
```java Schema: @Id public long id public ToOne