Slice incompatible with QuerydslPredicateExecutor?

[membersound]

I'm trying to execute a findAll() paged query with a Slice to avoid execution of the count query for each page (large database):

public interface MyRepository extends
		JpaRepository<MyEntity, Long>,
		QuerydslPredicateExecutor<QMyEntity> {
		
	Slice<MyEntity> findAllBy(com.querydsl.core.types.Predicate predicate, Pageable pageable);
}

The above fails with:

Caused by: java.lang.IllegalArgumentException: At least 1 parameter(s) provided but only 0 parameter(s) present in query.
	at org.springframework.util.Assert.isTrue(Assert.java:139) ~[spring-core-5.3.21.jar:5.3.21]
	at org.springframework.data.jpa.repository.query.QueryParameterSetterFactory$CriteriaQueryParameterSetterFactory.create(QueryParameterSetterFactory.java:298) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createQueryParameterSetter(ParameterBinderFactory.java:140) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createSetters(ParameterBinderFactory.java:129) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createSetters(ParameterBinderFactory.java:121) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createCriteriaBinder(ParameterBinderFactory.java:72) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.jpa.repository.query.PartTreeJpaQuery$QueryPreparer.getBinder(PartTreeJpaQuery.java:328) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.jpa.repository.query.PartTreeJpaQuery$QueryPreparer.createQuery(PartTreeJpaQuery.java:234) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.doCreateQuery(PartTreeJpaQuery.java:106) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:233) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.jpa.repository.query.JpaQueryExecution$SlicedExecution.doExecute(JpaQueryExecution.java:149) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:156) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:144) ~[spring-data-jpa-2.7.1.jar:2.7.1]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137) ~[spring-data-commons-2.7.1.jar:2.7.1]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121) ~[spring-data-commons-2.7.1.jar:2.7.1]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:160) ~[spring-data-commons-2.7.1.jar:2.7.1]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:139) ~[spring-data-commons-2.7.1.jar:2.7.1]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.21.jar:5.3.21]
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:81) ~[spring-data-commons-2.7.1.jar:2.7.1]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.21.jar:5.3.21]
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.21.jar:5.3.21]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.21.jar:5.3.21]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.21.jar:5.3.21]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.21.jar:5.3.21]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.21.jar:5.3.21]
	... 112 more
2022-07-26 17:22:13,244 [cbdbpT][anonymousUser] WARN [http-nio-8070-exec-1] o.s.w.s.m.m.a.ExceptionHandlerExceptionResolver: Resolved [org.springframework.dao.InvalidDataAccessApiUsageException: At least 1 parameter(s) provided but only 0 parameter(s) present in query.; nested exception is java.lang.IllegalArgumentException: At least 1 parameter(s) provided but only 0 parameter(s) present in query.]

[membersound]

The following answer shows a possible solution how to add a slice without count query for querydsl:

https://stackoverflow.com/a/69364616/1194415

[gregturn]

Essentially, we're not going to implement such a specialized variant of findAll out of the box.

If you need such a flavor of query based on such specific needs, by all means implement it using the hooks provided.

And if I may offer...page-based queries often involve doing count operations. Side stepping the count operation will tend to side step probably most/all of our Page/Slice-based options. I'm wondering if the count operation is so taxing, it could be a sign that your database tables need a little performative analysis.

• Are the statistics on your tables up to date and being run often enough? (I've seen CRON jobs that got disabled and leave you with 6-month old statistics that end up clobbering performance on the whole).
• Do you have proper indexing setup for the relevant tables so that counts aren't costing a fortune?

And if neither of these reveal a lingering issue...is the table itself growing "too big".

I say this because I once worked on a system that started off with 100,000 rows, and contained the historical record of all "events" in our system including timestamps. We linked all kinds of records to it. When that table grew to 10 MM rows, all our original queries suffered horribly.

We realized 90% of "live" operations only needed the last 24 hours of data, so our DBA fashioned a "mirror" table through a pre-insert trigger such that every row inserted into the BIG table ALSO got inserted into the SMALL table. And then that table was truncated every hour to only hold 24-hours of data.

Our "live" queries we could write joining with this much smaller table that we knew wouldn't grew out of hand down the road.

[membersound]

My use case is as follows: I have a table with cache data, between 20-50mio rows. The cache gets loaded several times a day. To speed up performance on database batch imports, there is only one index (of the minimal necessary 4-5 fields for our applications). The index suits all normal application queries.

Anyways, if I want to have a deeper look into that cache, I have to query for rows that are not contained in the indexed fields. That may take up to several seconds, even up to 30s. Which is still not the problem, BUT: as written, a slice query also always executes a count, which simply repeats the query that already too several seconds. So in the end, the time taken sums up x2. And that's way too long then.

As commented above, I could solve it with the workaround presented at stackoverflow, implementing a SliceRepository that is running the JPAQuery manually on the EntityManger.

Maybe you could have a look at the linked solution? The idea is to fetch "Pagesize+1", and then tell the SliceImpl if it hasNext elements based on the returned result size. This way, a count query can be prevented. Of course at cost that the total count of possible rows is not known during pagination. But it's known if there is at least one next page, which is sufficient for such large tables!

I tend to say: mostly the information of how many pages are yet to come is often irrelevant during pagination of large tables. You mostly only want to know if you can go one page further. But Spring forces the count query in any case, maybe this could be optimized so users can opt-out of the count query.

[gregturn]

I have a better understanding of your scenario. And I had already read your proposed solution over on stackoverflow.

The solution is quite specialized for such a huge table. I don't think it lends itself to being incorporated into a more broad toolkit. I frankly don't think it qualifies as an option.

This is exactly why we offer alternatives where you can fashion your alternative solution using the hooks provided.