Play 2.2 pagination with very large databases

Question

I am writing a Play 2.2.1 application to display the content of very large postgres tables (at least O(10^8)). To do this I modified the the computer database sample database.

My page method is almost directly taken from the computer class in the example:

public static Page<Lemma> page(int page, int pageSize, String sortBy, String order, String filter) {
    if(filter.isEmpty()) {
            return 
                find.where()
                    .findPagingList(pageSize)
                    .setFetchAhead(true)
                    .getPage(page);
    } else {
        return 
            find.where()
                .ilike("name", "%" + filter + "%")
                .orderBy(sortBy + " " + order)
                .findPagingList(pageSize)
                .setFetchAhead(true)
                .getPage(page);
    }
}

If the filter is empty, for performance issues I avoid any ordering. I my toy DB with 20,000,000 entries it works well, but with a more realistic example of 200,000,000 documents I always got timed-out connections.

The "name" column is indexed, and querying directly the database is very fast. I always run with the "-Xmx4096m" option for JVM.

What I am missing? What I am doing wrong? Any suggestion?

Update

The bottleneck seems to be the query SELECT COUNT(*) FROM lemma t0 ..., that is sent to display the number of documents found. Any idea to improve performance? Any suggestion for implementing a cursor in ebean as suggested by Peter?

What do you pass to the `sortBy` parameter when filtering ? Is this field indexed ? — mguillermin, Mar 06 '14 at 07:58
@mguillermin: I am passing to sortBy the 'name' field which is of course indexed. If I perform the same sorting query in psql the time taken is again negligible. — davide, Mar 06 '14 at 09:10

score 0 · Answer 1 · answered Mar 06 '14 at 10:50

0

This sounds like a JDBC issue, since your query runs fast on postgres and that you are using the same DB for both.

Suggestion:

Try to check if under the hood you're using the OFFSET and LIMIT to get only the data you want, chances are your driver is loading the whole data set in memory and then picking the data you want.

If that is the case, use a cursor instead, this helps loading only small edible chunks of your data sets to memory.

answered Mar 06 '14 at 10:50

Peter

7,020
4
31
51

`findPagingList(int pageSize)` put a limit of `pageSize + 1` on the query (I found it out because I also tried to use rawSql to build the initial query and there I was not allowed to put a limit statement explicitly). – davide Mar 06 '14 at 10:58
Anyway, is there a way to print the query which is sent to the DB with ebean? (However, if `filter.isEmpty()` there is no query (apart the limit put by `findPagingList()`), which is always the case for the home page). – davide Mar 06 '14 at 11:02
1

Apparently you have to add the following line to your `application.conf` `db.default.logStatements=true`. Here is another [thread](http://stackoverflow.com/questions/9719601/play-framework-2-0-and-ebean-sql-logging) that handles that same question. – Peter Mar 06 '14 at 12:34

Play 2.2 pagination with very large databases

1 Answers1