[
https://issues.apache.org/jira/browse/DBUTILS-123?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Bob Mecca updated DBUTILS-123:
------------------------------
Description:
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.
was:
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.
> 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: OracleQueryRunner.java, 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)
