Performance comparison between custom row mapper and Spring’s bean property row mapper

Custom Row Mapper Vs Spring’s Bean Property Row Mapper

Nov 14′ 2015

For quering relational database tables from Java layer, developers have an option of either using JPA (Java persistence API) to directly map java objects with relational database tables or they can directly write SQL queries and execute them using JDBC connection (or Spring JDBC templates).  In option 2 of writting SQL queries using Spring JDBC templates, Spring provides their default implementation of BeanPropertyRowMapper class which maps the output of SQL query with the custom object provided to the jdbc template. But at the same time Spring’s documentation states that it’s more of convenience than high performance and if you need high performance consider custom row mapper implemenetation.

In this article, I have tried to compare the performance of BeanPropertyRowMapper with custom row mapper.

 

Performance Test

Below is the database table being used to perform these tests. These tests are run on Windows 8 machine with 16GB RAM, Intel Core -i7 processor. JDBC calls are being made to Oracle Server running locally with no other user tables present. In different enviroments and different data sets, these test results might differ and different performance might be observed.

Database table

Table Structure – 

1) Table Name – TestPerformance

2) 20 Varchar2(32) columns containing string values

    20 Number(15) columns containing numbers

Note – For the sake of simplicity, I have named columns as STR1, STR2, …STR20 and NUM1, NUM2, …..NUM20. 

3) Total Number of test records inserted in table 5000

Column Name
Data Type
ID NUMBER –> Primary key
STR1 VARCHAR2(32)
STR2 VARCHAR2(32)
STR3 VARCHAR2(32)
STR4 VARCHAR2(32)
STR5 VARCHAR2(32)
STR6 VARCHAR2(32)
STR7 VARCHAR2(32)    
STR8 VARCHAR2(32)
STR9 VARCHAR2(32)
STR10 VARCHAR2(32)
STR11 VARCHAR2(32)
STR12 VARCHAR2(32)
STR13 VARCHAR2(32)
STR14 VARCHAR2(32)
STR15 VARCHAR2(32)
STR16 VARCHAR2(32)
STR17 VARCHAR2(32)
STR18 VARCHAR2(32)
STR19 VARCHAR2(32)
STR20 VARCHAR2(32)
NUM1 NUMBER(15)
NUM2 NUMBER(15)
NUM3 NUMBER(15)
NUM4 NUMBER(15)
NUM5 NUMBER(15)
NUM6 NUMBER(15)
NUM7 NUMBER(15)
NUM8 NUMBER(15)
NUM9 NUMBER(15)
NUM10 NUMBER(15)
NUM11 NUMBER(15)
NUM12 NUMBER(15)
NUM13 NUMBER(15)
NUM14 NUMBER(15)
NUM15 NUMBER(15)
NUM16 NUMBER(15)
NUM17 NUMBER(15)
NUM18 NUMBER(15)
NUM19 NUMBER(15)
NUM20 NUMBER(15)

 

Query performance between Custom Row Mapper vs Spring’s BeanPropertyRowMapper

Query run – “Select * from TestPerformance where rownum < :i” 

 

Record Set size returned from database – 500

Performance Run 
Custom Row Mapper (msec)
Spring’s BeanPropertyRowMapper (msec)
Run 1 63 164
Run 2  61 116
Run 3 76 83
Run 4 74 81
Run 5 79 88
Run 6 64 89
Run 7         68 48
Run 8 57 61
Run 9 56 56
Run 10 45 62
Average 64 84
Median 64 83

 

Record Set size returned from database – 5000

Performance Run 
Custom Row Mapper (msec)
Spring’s BeanPropertyRowMapper (msec)
Run 1 251 454
Run 2  179 379
Run 3 215 314
Run 4 180 369
Run 5 192 300
Run 6 176 349
Run 7         169 302
Run 8 175 365
Run 9 190 334
Run 10 180 327
Average 191 349
Median 180 341

 

Record Set size returned from database – 50000

Performance Run 
Custom Row Mapper (msec)
Spring’s BeanPropertyRowMapper (msec)
Run 1 2101 3400
Run 2  1785 3317
Run 3 1537 3168
Run 4 1564     3139
Run 5 1510 3123
Run 6 1505 3145
Run 7         1516 3080
Run 8 1508 3256
Run 9 1665 3102
Run 10 1550 3098
Average 1624 3182
Median 1543 3142

 

 

Avg Number comparison for different Resultset sizes

X axis – Result set size

Y axis – Time taken in msec

 

Conclusion

In terms of performance, it’s always better to create custom RowMapper but if the size of resultset is not big and performane is not on priority, Spring’s bean property row mapper comes handy and takes less effort to develop DAO classes. 


Leave a Reply

Your email address will not be published. Required fields are marked *