Paging is not performant for the REST rich Task query operation

Description of problem:

Paging is possible with the REST rich Task query, like this:

http://localhost:8080/business-central/rest/query/runtime/task?processinstanceid_max=5&s=4&p=0

This retrieves the first four elements related to tasks that have process instance id's that are less than or equal to 5.

However, the paging for the rich Task query operation is currently done in-memory as opposed to via the SQL query. First, the entire dataset related to the criteria passed is retrieved to the database after which the paged information is retrieved.

This is inefficient and not performant.

Version-Release number of selected component 6.1.0

How reproducible:

Always

Steps to Reproduce:
1. Create a large dataset (10000+ processes, each with multiple tasks)
2. Do a REST rich task query using pagination (page number and page size)

Actual results:

The result will take longer than it should.

Expected results:

The result will return within an acceptable time

Additional info:

See https://github.com/droolsjbpm/droolsjbpm-integration/blob/8343c93f6367e2724e6f85daa733175687530d7a/kie-remote/kie-remote-services/src/main/java/org/kie/remote/services/rest/query/InternalTaskQueryHelper.java#L71