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
I now write
source.addValue("MyParam", null, JDBCType.VARCHAR.getVendorTypeNumber());
Et voila, instant turbo-charged insert statements.