[jira] [Created] (DBUTILS-123) Performance issue with setNull(...) usage in fillStatement(...) method

["Bob Mecca (JIRA)" <ji...@apache.org>]

Bob Mecca created DBUTILS-123:
---------------------------------

             Summary: Performance issue with setNull(...) usage in fillStatement(...) method
                 Key: DBUTILS-123
                 URL: https://issues.apache.org/jira/browse/DBUTILS-123
             Project: Commons DbUtils
          Issue Type: Improvement
    Affects Versions: 1.6
         Environment: Encountered with Oracle 11g and JDK 1.6.  I am not sure if this affects other databases.
            Reporter: Bob Mecca
            Priority: Minor


The AbstractQueryRunner.fillStatement(PreparedStatement stmt, Object... params) method processes the params array, and if it detects one of the values to be NULL, it uses the PreparedStatement.setNull(parameterIndex, sqlType) method for that item.

The issue is when it has to use VARCHAR as the sqlType.  This is OK 
if the target column is a VARCHAR, but can lead to a performance issue 
if the column is a NUMBER, especially if the total number of records 
to be inserted is large and a significant portion of the records have 
a NULL value and, as it turns out, some do not.  This effect is multiplied if there are a number of columns on the target table which were NUMBER.

I will upload several attachments: (1) a Java class with which the issue can be replicated/seen, (2) another Java class with some sample code which I've cobbled together as a test for a workaround.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)