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);
}
