How can we improve Ebean's remote performance?

Question

(Disclaimer: this is very open question, what's more I think that there will be not only one correct answer, anyway don't close it please, also consider adding the answers to community wiki)

We are working on Play application using Ebean as main ORM, while we were in dev mode on the local servers, everything was quite nice. But the target environment for the application is the cloud + distant SQL servers. After connecting to the target databases we discovered one thing which in local environment was just unvisible: Ebean performes separate queries many times longer than manually written statements. For an example:

Statement

This query perform serie of subqueries for the resultSet in ~1200ms :

while (resultSet.next()) {
    Statement innerStatement = connection.createStatement();
    ResultSet innerSet
            = innerStatement.executeQuery("SELECT title FROM media WHERE id='" + resultSet.getInt("id") + "'");
    while (innerSet.next()) {
        Logger.info("Statement: " + innerSet.getString("title"));
    }
}

Ebean

Using the same resultSet Ebean needs about ~6500ms for the same operation. (Media model hasn't any relations, so there're no any joins) :

while (resultSet.next()) {
    Media media = Media.find.select("title").where().eq("id", resultSet.getInt("id")).findUnique();
    Logger.info("Ebean: " + media.title);
}

Edit: on local environment difference isn't so terrific as in general all queries are just much faster.

Of course there're many places where queries-series can be replaced with single ones, but it's not always possible. Sometimes we need to query several different tables in one request. In other cases we need to match thousands of records between different databases one by one, etc.

Also we found that, for an example, the Page<Media> class when passed to the Play's view performs separate select for each item of its list. In this case it's easy to getList() in the controller and pass it as List<Media> and add some info for pagination with ordinary type params, but... it was so cool to get it all at once :)

Well the questions...

1. How can be Ebean's configuration optimised?
2. How can be querying optimised (ie. mentioned List instead of Page, or using find.where().in(..., ...) instead of individual fetching of each object with for() statement). What else?
3. Are there other things which can surprise us yet with Ebean (like the querying the Page's items separately )?

Answer 1

I'm kind of guessing here, but this may be because the connections are not pooled.

Check the connection pool settings in 'conf/application.conf' as described in SettingsJDBC

# [...] thread-affine pools, basically
db.default.partitionCount=2

# The number of connections to create per partition. Setting this to 
# 5 with 3 partitions means you will have 15 unique connections to the 
# database. [...]
db.default.maxConnectionsPerPartition=5

# The number of initial connections, per partition.
db.default.minConnectionsPerPartition=5