Inserting nulls or empty Strings

Question

Our standalone Java application inserts records (in bulk) into a MS SQL Server 2008 R2 using Microsoft JDBC4 Driver for SQL Server as driver.

There is a huge difference in processing time between using empty strings or null values (as the parameter values set on the PreparedStatement).

Using null values:
begin import
flush finished in 63860 ms
flush finished in 20378 ms
finished processing table1 in 85957 ms
total[6280] added[6131] notValid[149]
end import [completed in 86340 ms]

Using empty strings:
begin import
flush finished in 15460 ms
flush finished in 3211 ms
finished processing table1 in 20334 ms
total[6280] added[6131] notValid[149]
end import [completed in 20702 ms]

When I change thedriver to jTDS JDBC Driver there is (almost) no difference anymore in processing time.

Using null values:
begin import
flush finished in 11719 ms
flush finished in 2606 ms
finished processing table1 in 16019 ms
total[6280] added[6131] notValid[149]
end import [completed in 16099 ms]

Using empty strings:
begin import
flush finished in 18082 ms
flush finished in 4051 ms
finished processing table1 in 23786 ms
total[6280] added[6131] notValid[149]
end import [completed in 23868 ms]

Is this a known issue?

Kind regards,

Roel

Answer 1

After some more fine-grained experiments I discovered calling setNull and/or setObject on the PreparedStatement is causing these performance issues.

Where can I file this bug?

Answer 2

A little more investigation revealed: when you provide the sqlType of the column, you have a performance loss of factor 3-4. So don't use [tt]setNull[/tt]-method (you are required to pass the sqlType). The setObject-method has a variant with sqlType and one without sqlType, use the 2-parameter variant (without sqlType).

Answer 3

Hi,

you may file all bugs/improvements/request on the connect website: http://connect.microsoft.com 

Janos

---

There are 10 type of people. Those who understand binary and those who do not.

My Blog | Hire Me

Answer 4

This bug has been filed here.