Hikaricp: 1 Connection per query vs Multiple Queries per 1 connection

[KaoruDev]

Environment

HikariCP version: 2.4.6
JDK version     : 1.8.0_111
Database        : PostgreSQL
Driver version  : 9.5.2

Hi Brett, I got a newbish question, is it preferred to obtain a connection and run multiple queries, or is it preferred to run 1 query per connection?

For example:

1 connection for multiple queries:

try (Connection conn = dataSource.getConnection()) {
    List<ItemRecords> items = DSL.using(conn)
        .select()
        .from(ITEMS)
        .fetchInto(ItemsRecord.class);
    items.forEach((item) -> {
        DSL.using(conn).delete(ITEMS).where(ITEMS.ID.eq(item.getId())).execute();
    });
}

Or 1 connection per query:

List<ItemRecords> items = new ArrayList();
try (Connection conn = dataSource.getConnection()) {
    items = DSL.using(conn)
        .select()
        .from(ITEMS)
        .fetchInto(ItemsRecord.class);
}

items.forEach((item) -> {
    try (Connection conn = dataSource.getConnection()) { 
      DSL.using(conn).delete(ITEMS).where(ITEMS.ID.eq(item.getId())).execute();
    }
});

I'd assume the latter is preferred so that the system can interweave other incoming web requests and avoid being blocked a long list of queries. I'm also assuming multiple calls to getConnection() is cheap.

Thank you!

[brettwooldridge]

@KaoruDev There is no clear answer. In terms of performance, multiple queries on a single connection will perform better. A single query per connection will be more expensive.

This is not because connection acquisition from the pool is expensive; from the perspective of the pool itself, you can obtain and return connections at a rate of over 50,000 per millisecond. However, with respect to the database, each "close" of the connection will require either a commit or rollback operation.

I don't know which framework you are using (not sure what DSL is), but in terms of looping over the items and issuing deletes, you will be much better off taking advantage of JDBC batch operations.

In terms of pure JDBC, this code...

try (Connection conn = dataSource.getConnection();
      PreparedStatement stmt = conn.prepareStatement("DELETE FROM item WHERE id=?")) {
  items.forEach((item -> {
    stmt.setInt(1, item.getId());
    stmt.addBatch();
  }
  stmt.executeUpdate();
  conn.commit();
}

Will perform 10-100x faster than deleting each item individually.

So, if you want to enable more "concurrency", you can certainly perform the two operations, "select" and "batch delete" on two separate connections, while retaining performance. But I would not recommend performing each "delete" operation on its own connection.

[KaoruDev]

However, with respect to the database, each "close" of the connection will require either a commit or rollback operation.

👏 this is what I was assuming was going on under the hood but wasn't 100% clear on, thank you very much for the help! Really appreciate it 🎉