Performance tests between QueryDSL SQL vs JDBCTemplate

Question

I am trying to convince "the higher ups" to use querydsl sql for our persistence. But they prefer spring jdbctemplate, with the reason being it gives the best raw performance.

Performance is our primary requirement ofcourse. Which is why JPA isnt an option at all. Is the QueryDSL SQL overhead too much to knock it off from our options?

I wanted to know if there are any "recent" performance tests done to show how querydsl sql fares with jdbctemplate and jpa.

I came across this. I want to know the relative performance of querydsl sql when compared against jdbctemplate and a jpa implementation.

Answer 1

I have done some performance tests to compare the overhead/performance comparison between querydsl and jdbctemplate.

Here is what I did
1. Created a spring boot project which uses querydsl and jdbctemplate
2. Created 2 end points, 1 for using querydsl and another for jdbctemplate.
3. The querydsl configuration uses the spring boot autoconfigured datasource to configure its SQLQueryFactory.
4. JdbcTemplate is also autoconfigured with the same autoconfigured datasource.
5. There are 2 implementations of a similary query in the repository, one uses querydsl and the other uses jdbctemplate. The query is relatively complex and consists of couple of inner joins and where clauses.
6. The Service methods has a for loop and calls the repository method in each iteration. No. of iterations is configurable and has been set to 100,000 iterations.
7. System.nanoTime() is used around the service method in the controller to calculate the time it took to execute that many iterations of the repository method.
8. JdbcTemplate took an average of 800ms to execute 100,000 repository calls.
9. Querydsl took an average of 5000ms to execute 100,000 repository calls.
10. Observation: Querydsl is 6 times slower than JdbcTemplate for the same query. The overhead is presumably in the query serialization of querydsl.

QueryDSL Repository Implementation

List<Customer> customers = new ArrayList<>();
Customer customerObj = null;
List<Tuple> customerTuples =queryFactory.select(customer.firstName,customer.status,customer.customerId).
                                from(customer).innerJoin(customerChat).on(customer.customerId.eq(customerChat.senderId)).
                                innerJoin(customerChatDetail).on(customerChat.chatDetailId.eq(customerChatDetail.chatDetailId)).
                                where(customerChatDetail.isRead.eq(true).and(customer.status.eq(true))).fetch();

    for (Tuple row : customerTuples) {
        customerObj = new Customer();
      customerObj.setFirstName(row.get(customer.firstName));
      customerObj.setStatus( row.get(customer.status));
      customerObj.setCustomerId(row.get(customer.customerId));
      customers.add(customerObj);
    }
return customers;

JdbcTemplate Implementation

List<Customer> customers = this.jdbcTemplate.query(
            "select first_name,status,customer_id from customer inner join v_customer_chat on customer.customer_id=v_customer_chat.sender_id inner join v_customer_chat_detail on v_customer_chat.chat_detail_id = v_customer_chat_detail.chat_detail_id where v_customer_chat_detail.is_read = ? and customer.status = ?;",new Object[] {true, true},
            new RowMapper<Customer>() {
                public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Customer customer = new Customer();
                    customer.setFirstName(rs.getString("first_name"));
                    customer.setStatus(rs.getBoolean("status"));
                    customer.setCustomerId(rs.getLong("customer_id"));
                    return customer;
                }
            });  

Basically I am trying to do the exact same thing with different libraries and measure which one incurs more overhead.
1. Execute a relatively complex query with joins.
2. Populate beans from the resultset.

I am using H2 In-memory database. The database holds just a couple of records for each table. And 1 result row that matches the query.

The method is executed in a for loop (1 lakh iterations). And time calculated with the help of System.nanoTime() around the loop.

Its a spring boot project with different end points (one for querydsl and another for jdbctemplate). The configuration for querydsl and queryfactory is done as follows

  @Autowired
public DataSource dataSource;

@Bean
public PlatformTransactionManager transactionManager() {
    return new DataSourceTransactionManager(dataSource);
}

@Bean
public com.querydsl.sql.Configuration querydslConfiguration() {
    SQLTemplates templates = H2Templates.builder().build();
    com.querydsl.sql.Configuration configuration = new com.querydsl.sql.Configuration(templates);

    configuration.setExceptionTranslator(new SpringExceptionTranslator());

    return configuration;
}

@Bean
public SQLQueryFactory queryFactory() {
    Provider<Connection> provider = new SpringConnectionProvider(dataSource);
    return new SQLQueryFactory(querydslConfiguration(), provider);
}