Re: Poor Performance of Camel-Sql Batch insertions with Oracle DB

[Saiteja Parna]

 I have tested with old driver. I don't see any performance issues.

I don't think it is environment issue, because i tested my route in
different machines with different oracle databases.

Following is the blueprint and scenario for new jdbc driver:
Remote Oracle Database: 12.2.0.1
Fuse : 6.3.0.redhat-317
Blueprint:
<?xml version="1.0" encoding="UTF-8"?>
<blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0";
    xmlns:camel="http://camel.apache.org/schema/blueprint";
    xmlns:cm="http://aries.apache.org/blueprint/xmlns/blueprint-cm/v1.1.0";
    xmlns:ext="http://aries.apache.org/blueprint/xmlns/blueprint-ext/v1.0.0";
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
    xsi:schemaLocation="http://www.osgi.org/xmlns/blueprint/v1.0.0
    https://www.osgi.org/xmlns/blueprint/v1.0.0/blueprint.xsd
    http://camel.apache.org/schema/blueprint
http://camel.apache.org/schema/blueprint/camel-blueprint.xsd";>

    <cm:property-placeholder id="blueprintProp"
persistent-id="camelSql.Oracle" update-strategy="reload">
        <cm:default-properties>
        </cm:default-properties>
    </cm:property-placeholder>

    <bean id="shutdown"
class="org.apache.camel.impl.DefaultShutdownStrategy">
        <property name="timeout" value="30"></property>
    </bean>

    <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource">
        <property name="URL" value="jdbc:oracle:thin:
@ldap://remothost:port/database,cn=OracleContext,dc=mydb"/>
        <property name="user" value="test"/>
        <property name="password" value="*******"/>
    </bean>
    <bean id="sql" class="org.apache.camel.component.sql.SqlComponent">
      <property name="dataSource" ref="dataSource"/>
    </bean>

    <camelContext id="sqlOracleCC" xmlns="
http://camel.apache.org/schema/blueprint";>
        <route id="timerToSqlRoute">
            <from
uri="timer://test?delay=2000&amp;fixedRate=true&amp;period=20000000" />
            <log loggingLevel="INFO" message="Route Started" />
            <to uri="sql:select * from table" />
            <log loggingLevel="INFO" message="${in.header.CamelSqlRowCount}
records are retrieved" />
            <!-- Sql Insert has around 90 columns -->
            <toD uri="sql:INSERT INTO
table2(column1,column2,column3,column4) VALUES
(:#column1,:#column2,:#column4,:#column4)?batch=true" />
            <log loggingLevel="INFO" message="Successfully inserted" />
        </route>
    </camelContext>
</blueprint>

On Fri, Apr 12, 2019 at 11:42 AM Saiteja Parna <saitejap.j...@gmail.com>
wrote:

> Fuse: 6.3.0.redhat-317  R10
> My Route in blueprint: Timer to SqlSelect to SqlInsert(batch=true)
> Camel-sql retrieves 4000 records and does batch insertion to Oracle db.
> Camel-sql is taking 30 mins to insert 4000 records to Oracle
> database(remote) 12.2.0.1.
> Each record has 90 columns with no indexes, constraints...
>
> We have upgraded jdbc driver to 12.2.0.1 and started having slow
> performance. Previously it was 11.2.0.4, Same transaction takes 1 second to
> process.
>
> Anyone faced the similar issue? Am i missing any property?
>
> Thank you.
> Sai.
>
>
>
>
>
>