Retrieving total count and paginating large datasets with Compose SDK

[herajapakse]

I'm currently working on implementing a custom table to handle a large dataset in Sisense, and I'm encountering some performance challenges related to pagination. Here's the scenario I'm facing:

Requirements:

1. Retrieve the total number of records that match a filter condition (to get the total count for display and pagination).
2. Efficiently paginate the results by fetching only the records for the current page, based on page size (e.g., 10, 25, 50 records per page), without retrieving all records at once.

The Challenge:

While I can use offset and count to paginate the records and fetch only the records for the current page, I don't have a way to determine the total number of records that match the filter condition without retrieving all of the records first.

For large datasets, fetching the entire dataset before pagination is causing performance issues, since Sisense retrieves all the records matching the filter condition before applying pagination, which is inefficient.

What I'm Looking For:

• Is there a way to retrieve the total count of records matching the filter condition without fetching the full dataset?
• How can I fetch only the records for the current page (using offset and count), while still being able to display the total number of matching records?

Does anyone has experience implementing efficient pagination in Sisense for large datasets or can share any best practices, examples, or solutions?

[DRay]

Hello @herajapakse,

Thank you for reaching out. I see your question hasn't gotten a response yet, so I'm asking internally to try and get you an answer. 

[DRay]

Hi @herajapakse,

Currently, there is no way to do this. 

[steve · Accepted solution]

Hi @herajapakse 

Thanks for the detailed post and clear explanation of what you're trying to achieve.

Unfortunately, as David mentioned, getting the total row count of a query while only requesting a limited amount of rows is not currently supported. We have investigated the situation and are waiting on an enhancement from the backend API which would enable us to request the total number of rows.

Previously it has been suggested you could execute a separate query to count the number of rows instead of the aggregation you want in the paginated results, but it may not be a reliable solution for all cases.

For now you can implement paged queries with count and offset, just without the total row count at this time.

Steve

[herajapakse]

@DRay Thank you for following up and getting an answer for the post

@steve Thank you for the answer. Do you happen to know if there's a planned timeframe for when the solution will be delivered?