CODEWITHSUNDEEP
sqlplayframeworkpaginationplayframework-2.0ebean
Rib47
Rib47

Reputation: 2516

Play 2 framework 2.2.4 (ebean): pagination generates mutiple sql queries per one page when call Page<>.getList()

We are using Play 2 Framework 2.2.4 (with eBean ORM integerated) in our project. Our pagination looks like:

public static Page<Users> page(int pageNum, int pageSize) {
    Page<Users> page = find.where().findPagingList(pageSize).getPage(pageNum);
    return page;
}

...

Page<User> currentPage =  page(0, 10); // for example

and then in view:

@for(user <- currentPage.getList) {
    // display information for every user
    ...
}

When I turn on logging of SQL queries to console, I was surprised - to get user list for one page Ebean makes SQL request for EVERY page!

For our 329 users this means 33 total SQL queries:

[debug] c.j.b.PreparedStatementHandle - select t0.user_id c0, t0.user_name c1, t0.first_name c2, t0.last_name c3, t0.user_full_name c4, t0.sex_id c5, t0.status c7, t0.role_id c8, t0.phone c9, t0.email c10 from users t0 order by user_id
limit 11
[debug] c.j.b.PreparedStatementHandle - select t0.user_id c0, t0.user_name c1, t0.first_name c2, t0.last_name c3, t0.user_full_name c4, t0.sex_id c5, t0.status c7, t0.role_id c8, t0.phone c9, t0.email c10 from users t0 order by user_id
limit 11 offset 10
[debug] c.j.b.PreparedStatementHandle - select t0.user_id c0, t0.user_name c1, t0.first_name c2, t0.last_name c3, t0.user_full_name c4, t0.sex_id c5, t0.status c7, t0.role_id c8, t0.phone c9, t0.email c10 from users t0 order by user_id
limit 11 offset 20
...
[debug] c.j.b.PreparedStatementHandle - select t0.user_id c0, t0.user_name c1, t0.first_name c2, t0.last_name c3, t0.user_full_name c4, t0.sex_id c5, t0.status c7, t0.role_id c8, t0.phone c9, t0.email c10 from users t0 order by user_id
limit 11 offset 320

With some experiments I discover, that this plenty of SQL queries appears exactly if method Page<T>.getList() is calling. Even if it is called alone without any other actions.

I don't understand - what is the reason for all that queries?

Is this some kind of a bug?

Upvotes: 1

Views: 525

Answers (2)

Rajat Verma
Rajat Verma

Reputation: 2590

List<Notification> notifications = Ebean.find(Notification.class).where()
                .eq("notification_belong_to_user_id", userId)
                .order().desc("notificationDate")
                .findPagedList(pageNo, (int) pageSize)
                .getList();

Upvotes: 0

marcospereira
marcospereira

Reputation: 12214

Play 2.2.4 uses Ebean version 3.2.2, and this version of Ebean uses a "fetch ahead" strategy to get all the pages when you call getPage.

You can change this by setting fetch ahead property to false, like this:

public static Page<Users> page(int pageNum, int pageSize) {
    Page<Users> page = find.where()
                           .findPagingList(pageSize)
                           .setFetchAhead(false)
                           .getPage(pageNum);
    return page;
}

Upvotes: 1

Related Questions