This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
If you use a PreparedStatement is there any difference (for a varchar column) in setting null or "" (empty string) as parameter value? Because our batch insert takes twice as long when using null values (instead of empty strings) as parameter values.
I'm not sure if it's a real JDBC issue, so I give some information about our environment. Our database is a Microsoft SQL Server 2008 R2, the JDBC driver we use is the Microsoft JDBC4 Driver for SQL Server driver. Our standalone application is Spring-enabled: to connect to the database we use a DriverManagerDataSource and to perform the batch inserts we use a BatchSqlUpdate instance.
I replaced BatchSqlUpdate with JdbcTemplate.batchUpdate which gives me more control about which methods to invoke on the PreparedStatement to set the parameter values. After playing a bit with all the possible alternatives, it's proven that invoking setNull and/or setObject are responsible for the performance issues when using Microsoft JDBC4 Driver for SQL Server.
I experimented a bit more and discovered some interesting facts about this driver:
When you provide the sqlType of the column, you have a performance loss of factor 3-4. So don't use setNull-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).
If you pass a java.util.Date to the setObject-method, you'll get this exeception: The conversion from UNKNOWN to UNKNOWN is unsupported.. Use a java.sql.Date instead
If you create a BigDecimal instance using a double and pass this value to the setBigDecimal-method (e.g. ps.setBigDecimal(parameterIndex, new BigDecimal(123.456));), you'll get this exception: Error converting data type nvarchar to decimal.
In the end I created my own BatchSqlUpdate class (instead of using the one provided by String). When it comes down to setting parameter values on the PreparedStatement I just use the setObject(parameterIndex, value) (so no setNull or setObject with sqlType parameter).
The results using null values:
flush finished in 10562 ms
flush finished in 2723 ms
finished processing table1 in 14841 ms
total added notValid
end import [completed in 15201 ms]
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link: http://aspose.com