Slow performance with NamedParameterJdbcTemplate

Today I tried inserting 256 rows in a single, empty PostgreSQL table which has only one index on it using Spring’s NamedParameterJdbcTemplate . To my surprise the single transaction took over 3 minutes to complete, over 500ms per INSERT statement. To make things worse, the same inserts during integration testing on an H2 database completed within a second.

My first guess was that I had an issue with the TOAST tables since the actual table has 28 columns and most of them are VARCHAR(256). As I didn’t not find any issue with it, I continued my quest … just up to the point where I replaced all named parameters to hardcoded values and used and EmpySqlParameterSource() instead. To my great surprise, this resulted in sub-second completion of all inserts.

So obviously, there had to be an issue with the NamedParameterJdbcTemplate, right ? I fired up VisualVM to verify my idea and sampled the CPU time of all org.springframework classes:

The obvious pain point is the setNull() method of the StatementCreatorsUtil and looking at the source code it’s quite obvious what’s going on: every time I set a null value in a statement, this method tries to find out what SqlType the null value should be because I didn’t suggested it myself.

I decided to not waste more time on this issue so I just fixed my code by re-writing parts of my code. Instead of writing

source.addValue("myParam", null);

I now write

source.addValue("MyParam", null, 
     JDBCType.VARCHAR.getVendorTypeNumber());

 

Et voila, instant turbo-charged insert statements.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s