Insert/Update with NULL values in Oracle's JDBC causes performance issue

Question

We have a Spring application connecting to Oracle via SpringJDBC (jdbcTemplate),

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <version>23.3.0.23.09</version>
</dependency>

In our DAO class we have an INSERT executed with jdbcTemplate.update():

String sql = """
                INSERT INTO 
                    MY_TABLE (
                        COL1,
                        COL2,
                        COL3)
                    VALUES (?, ?, ?)
            """;
jdbcTemplate.update(sql, param1, param2, param3/* may be NULL*/);

The 3rd param may be a NULL value getting inserted. When it's not NULL, performance is fast, no issues. But when it happens to be NULL, it takes ~2 min on the first such insert, and it's still pretty slow (about 10-30 sec.) on all subsequent such Inserts.

This is not a DB problem: I verified in the DB (SqlDeveloper) that all INSERTs both with and without NULLs in this column are equally fast directly in the DB, so we can rule out the DB.

Since Oracle treats empty strings as NULLs, we found a good solution: just pass in an empty string for a NULL:

(param3 == null ? "" : param3)

and this fixes the performance issue, and NULL gets inserted. But is this NULL issue a known problem with Oracle's JDBC driver? I don't know if this will always work, like if the NULL/empty equivalence can be turned off in the DB. Are there other workarounds?

I am interested to see where the delay is originating from. You may want to consider profiling your program. — Cardinal System
– Cardinal System, Commented Oct 8, 2024 at 15:33
If COL3 is nullable and of a specific type, you could try casting the NULL to that type explicitly in your SQL: VALUES (?, ?, CAST(? AS VARCHAR2) — gmifflen
– gmifflen, Commented Oct 8, 2024 at 15:51
You can't rule out the database just because you can do an insert through SQL Developer. If Spring is binding those values using proper bind variables, that's not the same as providing literals. There could be a problem, for example, in parse. You won't know for sure until you examine the wait interface (or ASH data) to see what, if anything, your session is doing during those long inserts. If you find nothing, then it's likely a client issue. But there is a chance you'll find some sort of concurrency mechanism, or a trigger that acts conditionally based on client, etc. — Paul W
– Paul W, Commented Oct 8, 2024 at 15:57
I would also try executing the same SQL with a regular PreparedStatement instead of going through Spring. If it performs better that way, then your issue is Spring, not the Oracle driver. — Cardinal System
– Cardinal System, Commented Oct 8, 2024 at 19:40

Kuassi Mensah · Accepted Answer · 2025-05-22 14:49:20Z

2

According to an Oracle JDBC developer, the problem stems from parsing the Spring JDBC INSERT statement.

https://github.com/spring-projects/spring-framework/blob/612dc573d12d04d8d88ea101f90bc1df9f3e343c/spring-jdbc/src/main/java/org/springframework/jdbc/core/StatementCreatorUtils.java#L274

Spring JDBC calls getParameterMetadata() when setting a parameter to null.
Setting the following property to “true”, prevents Spring from using the ParameterMetadata API:

spring.jdbc.getParameterType.ignore=true

This property can be set either in spring.properties or as a System property.

answered May 22 at 14:49

Kuassi Mensah

3151 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

I.Yuldoshev · Accepted Answer · 2025-10-07 12:50:47Z

Apparently, driver tries to fetch metadata when no type is provided. In a similar situation where NamedParamaterJdbcTemplate was used with MapSqlParameterSource as arguements source to insert into temporary table(no constraints, indexes and keys).

The insert into temp table with 30 columns was taking ~250ms without types provided.

new MapSqlParameterSource()
    .addValue("param1", param1)
    .addValue("param2", param1);

With types provided it reduced to ~10ms.

new MapSqlParameterSource()
    .addValue("param1", param1, OracleType.VARCHAR2.getVendorTypeNumber())
    .addValue("param2", param1, OracleType.NUMBER.getVendorTypeNumber());

At first tried using java.sql.Types.NULL as default type where value was NULL, which helped according to spring logs because now it was not printing the log from StatementCreatorUtils

This led into a confusion that types were not causing the degradation and java.sql.Types.NULL was helping, but still ps.getParameterMetaData was being called which was the root cause of problem.

After setting the type of each column, insert performance was improved 25x.

Collectives™ on Stack Overflow

Insert/Update with NULL values in Oracle's JDBC causes performance issue

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related