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

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

     [ https://issues.apache.org/jira/browse/DBUTILS-123?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Bob Mecca updated DBUTILS-123:
------------------------------
    Attachment: PrepartedStatementTest.java

Creates a blank table and then has some various scenarios which you can run to test the performance inserting 10,000 numbers into a table.

> 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
>         Attachments: PrepartedStatementTest.java
>
>
> 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)