Performance issues with paginate()

[fsnlarson]

This may be helpful for people.

The paginate function runs into severe performance issues when the datasets are in the thousands.

We are using fastapi_pagination with SQLAlchemy and with a result of 8000 records. Since paginate requires a Sequence we must load the entire query result into memory and then paginate slices the result. Avg. response time above 1 second.

paginate function in the package.

def paginate(
    sequence: Sequence[T],
    params: Optional[AbstractParams] = None,
    length_function: Callable[[Sequence[T]], int] = len,
) -> AbstractPage[T]:
    params = resolve_params(params)
    raw_params = params.to_raw_params()

    return create_page(
        items=sequence[raw_params.offset : raw_params.offset + raw_params.limit],
        total=length_function(sequence),
        params=params,
    )

Our solution was to do the following, which uses the native offset() and limit() from SQLAlchemy. Obviously this does not apply to other databases ORMs so a better approach would be potentially allowing a slice_function like the length_function and then allowing a queryset-type object to be passed instead of only a sequence.

Our solution

def paginate(
    query: sqlalchemy.orm.query.Query,
    params: Optional[AbstractParams] = None,
) -> AbstractPage[T]:
    params = resolve_params(params)
    raw_params = params.to_raw_params()

    return create_page(
        items=query.offset(params.offset).limit(params.limit).all(),
        total=query.count(),
        params=params,
    )

The new function is 100x faster (.01 seconds)

[uriyyo]

Hi @fsnlarson,
Did you try to use fastapi_pagination.ext.sqlalchemy.paginate? It looks almost the same as your solution:

fastapi-pagination/fastapi_pagination/ext/sqlalchemy.py

Lines 14 to 32 in 5a9ad78

	def paginate_query(query: T, params: AbstractParams) -> T:
	raw_params = params.to_raw_params()
	return query.limit(raw_params.limit).offset(raw_params.offset)
	def _to_dict(obj: Any) -> Any:
	try:
	return obj._asdict()
	except AttributeError:
	return obj
	def paginate(query: Query, params: Optional[AbstractParams] = None) -> AbstractPage:
	params = resolve_params(params)
	total = query.count()
	items = [_to_dict(item) for item in paginate_query(query, params)]
	return create_page(items, total, params)

[fsnlarson]

That extension is the right solution. The docs should maybe be updated to show an example of using an extension.