Cassandra .setFetchSize() on statement is not honoured

Question

I want to implement pagination on the UI, so I set fetchSize like this:

boundStatement.setFetchSize(20)

But the setFetchSize() is not honored. My table has 400 rows as of now and all 400 rows are retrieved. When I retrieve the next set of rows using

rs.getExecutionInfo().getPagingState();

then the next 380 rows are retrieved. So the paging state is correctly set and retrieved, but why is the driver retreiving all 400 rows from the table and how can I avoid this or make it retrieve only 400

Portion of code:

....
....

// "SELECT * FROM abc.sometable"
BoundStatement boundStatement = pStmt.bind();
boundStatement.setFetchSize(20);

if (pagingState != null) {
  boundStatement.setPagingState(PagingState.fromString(pagingState));
}
ResultSet rs = session.execute(boundStatement);
PagingState nextPage = rs.getExecutionInfo().getPagingState();
int remaining = rs.getAvailableWithoutFetching();
List<?> list = new ArrayList<>();
for (Row row : rs) {
  list.add(getValidObjectFromRow(row));
}
 ....

Cassandra version - 3.7 and cassandra driver version - 3.1.0

Thanks!

Answer 1

setFetchSize controls the page size, but it doesn't control the maximum rows returned in a ResultSet.

for (Row row : rs) {
    list.add(getValidObjectFromRow(row));
}

In the above code from your sample code, it will iterate over the first 20 rows, and when it reaches the end of the current results, it will fetch more until no more rows match the query.

If you want to limit the number of rows returned, you have a few options.

1. Add LIMIT X (where x is the number of rows you want) to the end of your query.
2. Only iterate until there are no more rows, i.e.:

while(rs.getAvailableWithoutFetching() > 0) {
    list.add(getValidObjectFromRow(rs.one()));
}

This will read from the ResultSet until there are no more rows in the page, at which point it will stop iterating.