commons-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bob Mecca (JIRA)" <>
Subject [jira] [Updated] (DBUTILS-123) Performance issue with setNull(...) usage in fillStatement(...) method
Date Mon, 13 Apr 2015 20:27:12 GMT


Bob Mecca updated DBUTILS-123:

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:
>             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:
> 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

View raw message