Benchmarking spring data vs JDBI in select from postgres Database

Question

I wanted to compare the performence for Spring data vs JDBI I used the following versions

Spring Boot 2.2.4.RELEASE

vs

JDBI 3.13.0

the test is fairly simple select * from admin table and convert to a list of Admin object

here is the relevant details

with spring boot

public interface AdminService extends JpaRepository<Admin, Integer> {


}

and for JDBI

public List<Admin> getAdmins() {
    String sql = "Select admin_id as adminId, username from admins";
    Handle handle = null;
    try {
        handle = Sql2oConnection.getInstance().getJdbi().open();
        return handle.createQuery(sql).mapToBean(Admin.class).list();
    }catch(Exception ex) {
        log.error("Could not select admins from admins: {}", ex.getMessage(), ex );
        return null;
    } finally {
        handle.close();
    }
}

the test class is executed using junit 5

@Test
@DisplayName("How long does it take to run 1000 queries")
public void loadAdminTable() {
    System.out.println("Running load test");
    Instant start = Instant.now();
    for(int  i= 0;i<1000;i++) {
        adminService.getAdmins(); // for spring its findAll()
    for(Admin admin: admins) {
                if(admin.getAdminId() == 654) {
                    System.out.println("just to simulate work with the data");
                }
            }
    }
    Instant end = Instant.now();
    Duration duration = Duration.between(start, end);
    System.out.println("Total duration: " + duration.getSeconds());

}

i was quite shocked to get the following results

Spring Data: 2 seconds JDBI: 59 seconds

any idea why i got these results? i was expecting JDBI to be faster

score 2 · Answer 1 · edited Sep 23 '20 at 06:25

The issue was that spring manages the connection life cycle for us and for a good reason after reading the docs of JDBI

There is a performance penalty every time a connection is allocated and released. In the example above, the two insertFullContact operations take separate Connection objects from your database connection pool.

i changed the test code of the JDBI test to the following

@Test
@DisplayName("How long does it take to run 1000 queries")
public void loadAdminTable() {
    System.out.println("Running load test");
    String sql = "Select admin_id as adminId, username from admins";
    Handle handle = null;
    handle = Sql2oConnection.getInstance().getJdbi().open();
    Instant start = Instant.now();
    for(int  i= 0;i<1000;i++) {
        
        List<Admin> admins = handle.createQuery(sql).mapToBean(Admin.class).list();
        if(!admins.isEmpty()) {
            for(Admin admin: admins) {
                System.out.println(admin.getUsername());
            }
        }
    }
    handle.close();
    Instant end = Instant.now();
    Duration duration = Duration.between(start, end);
    System.out.println("Total duration: " + duration.getSeconds());
    
}

this way the connection is opened once and the query runs 1000 times

the final result was 1 second

twice as fast as spring

score 1 · Answer 2 · answered May 02 '20 at 06:04

On the one hand you seem to make some basic mistakes of benchmarking:

You are not warming up the JVM.
You are not using the results in any way.

Therefore what you are seeing might just be effects of different optimisations of the VM. Look into JMH in order to improve your benchmarks.

Benchmarks with an external resource are extra hard, because you have so many more parameters to control. One big question is for example if the connection to the database is realistically slow as in most production systems the database will be on a different machine at least virtually, quite possibly on different hardware. Is that true in your test as well?

Assuming your results are real, the next step is to investigate where the extra time gets spent. I would expect the most time to be spent with executing the SQL statements and obtaining the result via the network. Therefore you should inspect what SQL statements actually get executed.

This might point you to one possible answer that JPA is doing lots of lazy loading and hasn't even loaded most of you really need.

Hi thanks for the reply, im doing it all on my local machine so everything is on the same machine , i updated the test code to exclude the lazy loading option — naoru, May 02 '20 at 13:59

Benchmarking spring data vs JDBI in select from postgres Database

2 Answers2

Linked