You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@camel.apache.org by Saiteja Parna <sa...@gmail.com> on 2019/04/12 18:42:39 UTC
Poor Performance of Camel-Sql Batch insertions with Oracle DB
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.
Re: Poor Performance of Camel-Sql Batch insertions with Oracle DB
Posted by Saiteja Parna <sa...@gmail.com>.
Yes, I did tried those configurations. But still same slow down. My test
case works fine for 11g Oracle drivers.
jdbc.pool.maxTotal
jdbc.pool.minIdle
jdbc.pool.testOnBorrow
InitialSize too.
On Thu, May 23, 2019 at 2:20 PM FabryProg <fa...@gmail.com> wrote:
> Hi guy
>
> Perhaps the error is another: do you tried to change initial and max pool
> size ? Or connectionharvest settings?
>
>
> King regards
>
>
> Il Gio 23 Mag 2019, 23:12 Saiteja Parna <sa...@gmail.com> ha
> scritto:
>
> > This issue is resolved. My post might help others.
> > Batch Update/Insert/Delete has issues with Oracle_Jdbc drivers 12c and
> > above.
> > If any one using Camel-sql or any other Camel database components with
> > Oracle_jdbc drivers (12c and latest) will see very slow performance issue
> > with batch insert/update/delete with very big records(like a record with
> 90
> > columns).
> > Oracle team used my test case(Spring JDBC with Oracle Driver) and
> confirmed
> > the bug.
> >
> >
> >
> >
> >
> >
> > On Mon, Apr 15, 2019 at 10:36 AM Saiteja Parna <sa...@gmail.com>
> > wrote:
> >
> > > Hello,
> > >
> > > Batch update/insert are deprecated in the Oracle drivers 12c. Please
> > Check
> > > the description below.
> > >
> > > *Oracle*:
> > > Oracle update batching was deprecated in Oracle Database 12c Release 1
> > > (12.1). Starting in Oracle Database 12c Release 2 (12.2), Oracle update
> > > batching is a no operation code (no-op). This means that if you
> implement
> > > Oracle update batching in your application, using the Oracle Database
> 12c
> > > Release 2 (12.2) JDBC driver, then the specified batch size is not set
> > and
> > > results in a batch size of 1. With this batch setting, your application
> > > processes one row at a time. Oracle strongly recommends that you use
> the
> > > standard JDBC batching if you are using the Oracle Database 12c
> Release 2
> > > (12.2) JDBC driver.
> > >
> > >
> > > *Question*: As per the above description, It is recommended to set
> Batch
> > > Size. But i don't see BatchSize property for Camel-SQL document.
> > > I tried *template.batchSize=500* on camel-sql endpoint but it didn't
> > > work. Still it is taking 30 mins to insert 4000 records.
> > >
> > > If anyone wants to replicate the issue, i copied my blueprint in the
> > above
> > > email.
> > >
> > >
> > >
> > >
> > > On Fri, Apr 12, 2019 at 4:04 PM sai [via Camel] <
> > > ml+s465427n5833064h17@n5.nabble.com> wrote:
> > >
> > >> 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&fixedRate=true&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 <[hidden email]
> > >> <http:///user/SendEmail.jtp?type=node&node=5833064&i=0>>
> > >> 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.
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >>
> > >>
> > >> ------------------------------
> > >> If you reply to this email, your message will be added to the
> discussion
> > >> below:
> > >>
> > >>
> >
> http://camel.465427.n5.nabble.com/Poor-Performance-of-Camel-Sql-Batch-insertions-with-Oracle-DB-tp5833056p5833064.html
> > >> To unsubscribe from Poor Performance of Camel-Sql Batch insertions
> with
> > >> Oracle DB, click here
> > >> <
> >
> http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5833056&code=c2FpdGVqYXAuamF2YUBnbWFpbC5jb218NTgzMzA1NnwtMTk5ODYwNzMyMA==
> > >
> > >> .
> > >> NAML
> > >> <
> >
> http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
> > >
> > >>
> > >
> >
>
Re: Poor Performance of Camel-Sql Batch insertions with Oracle DB
Posted by FabryProg <fa...@gmail.com>.
Hi guy
Perhaps the error is another: do you tried to change initial and max pool
size ? Or connectionharvest settings?
King regards
Il Gio 23 Mag 2019, 23:12 Saiteja Parna <sa...@gmail.com> ha
scritto:
> This issue is resolved. My post might help others.
> Batch Update/Insert/Delete has issues with Oracle_Jdbc drivers 12c and
> above.
> If any one using Camel-sql or any other Camel database components with
> Oracle_jdbc drivers (12c and latest) will see very slow performance issue
> with batch insert/update/delete with very big records(like a record with 90
> columns).
> Oracle team used my test case(Spring JDBC with Oracle Driver) and confirmed
> the bug.
>
>
>
>
>
>
> On Mon, Apr 15, 2019 at 10:36 AM Saiteja Parna <sa...@gmail.com>
> wrote:
>
> > Hello,
> >
> > Batch update/insert are deprecated in the Oracle drivers 12c. Please
> Check
> > the description below.
> >
> > *Oracle*:
> > Oracle update batching was deprecated in Oracle Database 12c Release 1
> > (12.1). Starting in Oracle Database 12c Release 2 (12.2), Oracle update
> > batching is a no operation code (no-op). This means that if you implement
> > Oracle update batching in your application, using the Oracle Database 12c
> > Release 2 (12.2) JDBC driver, then the specified batch size is not set
> and
> > results in a batch size of 1. With this batch setting, your application
> > processes one row at a time. Oracle strongly recommends that you use the
> > standard JDBC batching if you are using the Oracle Database 12c Release 2
> > (12.2) JDBC driver.
> >
> >
> > *Question*: As per the above description, It is recommended to set Batch
> > Size. But i don't see BatchSize property for Camel-SQL document.
> > I tried *template.batchSize=500* on camel-sql endpoint but it didn't
> > work. Still it is taking 30 mins to insert 4000 records.
> >
> > If anyone wants to replicate the issue, i copied my blueprint in the
> above
> > email.
> >
> >
> >
> >
> > On Fri, Apr 12, 2019 at 4:04 PM sai [via Camel] <
> > ml+s465427n5833064h17@n5.nabble.com> wrote:
> >
> >> 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&fixedRate=true&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 <[hidden email]
> >> <http:///user/SendEmail.jtp?type=node&node=5833064&i=0>>
> >> 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.
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >> ------------------------------
> >> If you reply to this email, your message will be added to the discussion
> >> below:
> >>
> >>
> http://camel.465427.n5.nabble.com/Poor-Performance-of-Camel-Sql-Batch-insertions-with-Oracle-DB-tp5833056p5833064.html
> >> To unsubscribe from Poor Performance of Camel-Sql Batch insertions with
> >> Oracle DB, click here
> >> <
> http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5833056&code=c2FpdGVqYXAuamF2YUBnbWFpbC5jb218NTgzMzA1NnwtMTk5ODYwNzMyMA==
> >
> >> .
> >> NAML
> >> <
> http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
> >
> >>
> >
>
Re: Poor Performance of Camel-Sql Batch insertions with Oracle DB
Posted by Saiteja Parna <sa...@gmail.com>.
This issue is resolved. My post might help others.
Batch Update/Insert/Delete has issues with Oracle_Jdbc drivers 12c and
above.
If any one using Camel-sql or any other Camel database components with
Oracle_jdbc drivers (12c and latest) will see very slow performance issue
with batch insert/update/delete with very big records(like a record with 90
columns).
Oracle team used my test case(Spring JDBC with Oracle Driver) and confirmed
the bug.
On Mon, Apr 15, 2019 at 10:36 AM Saiteja Parna <sa...@gmail.com>
wrote:
> Hello,
>
> Batch update/insert are deprecated in the Oracle drivers 12c. Please Check
> the description below.
>
> *Oracle*:
> Oracle update batching was deprecated in Oracle Database 12c Release 1
> (12.1). Starting in Oracle Database 12c Release 2 (12.2), Oracle update
> batching is a no operation code (no-op). This means that if you implement
> Oracle update batching in your application, using the Oracle Database 12c
> Release 2 (12.2) JDBC driver, then the specified batch size is not set and
> results in a batch size of 1. With this batch setting, your application
> processes one row at a time. Oracle strongly recommends that you use the
> standard JDBC batching if you are using the Oracle Database 12c Release 2
> (12.2) JDBC driver.
>
>
> *Question*: As per the above description, It is recommended to set Batch
> Size. But i don't see BatchSize property for Camel-SQL document.
> I tried *template.batchSize=500* on camel-sql endpoint but it didn't
> work. Still it is taking 30 mins to insert 4000 records.
>
> If anyone wants to replicate the issue, i copied my blueprint in the above
> email.
>
>
>
>
> On Fri, Apr 12, 2019 at 4:04 PM sai [via Camel] <
> ml+s465427n5833064h17@n5.nabble.com> wrote:
>
>> 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&fixedRate=true&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 <[hidden email]
>> <http:///user/SendEmail.jtp?type=node&node=5833064&i=0>>
>> 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.
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>> ------------------------------
>> If you reply to this email, your message will be added to the discussion
>> below:
>>
>> http://camel.465427.n5.nabble.com/Poor-Performance-of-Camel-Sql-Batch-insertions-with-Oracle-DB-tp5833056p5833064.html
>> To unsubscribe from Poor Performance of Camel-Sql Batch insertions with
>> Oracle DB, click here
>> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5833056&code=c2FpdGVqYXAuamF2YUBnbWFpbC5jb218NTgzMzA1NnwtMTk5ODYwNzMyMA==>
>> .
>> NAML
>> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>
>
Re: Poor Performance of Camel-Sql Batch insertions with Oracle DB
Posted by Saiteja Parna <sa...@gmail.com>.
Hello,
Batch update/insert are deprecated in the Oracle drivers 12c. Please Check
the description below.
*Oracle*:
Oracle update batching was deprecated in Oracle Database 12c Release 1
(12.1). Starting in Oracle Database 12c Release 2 (12.2), Oracle update
batching is a no operation code (no-op). This means that if you implement
Oracle update batching in your application, using the Oracle Database 12c
Release 2 (12.2) JDBC driver, then the specified batch size is not set and
results in a batch size of 1. With this batch setting, your application
processes one row at a time. Oracle strongly recommends that you use the
standard JDBC batching if you are using the Oracle Database 12c Release 2
(12.2) JDBC driver.
*Question*: As per the above description, It is recommended to set Batch
Size. But i don't see BatchSize property for Camel-SQL document.
I tried *template.batchSize=500* on camel-sql endpoint but it didn't work.
Still it is taking 30 mins to insert 4000 records.
If anyone wants to replicate the issue, i copied my blueprint in the above
email.
On Fri, Apr 12, 2019 at 4:04 PM sai [via Camel] <
ml+s465427n5833064h17@n5.nabble.com> wrote:
> 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&fixedRate=true&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 <[hidden email]
> <http:///user/SendEmail.jtp?type=node&node=5833064&i=0>>
> 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.
> >
> >
> >
> >
> >
> >
>
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://camel.465427.n5.nabble.com/Poor-Performance-of-Camel-Sql-Batch-insertions-with-Oracle-DB-tp5833056p5833064.html
> To unsubscribe from Poor Performance of Camel-Sql Batch insertions with
> Oracle DB, click here
> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5833056&code=c2FpdGVqYXAuamF2YUBnbWFpbC5jb218NTgzMzA1NnwtMTk5ODYwNzMyMA==>
> .
> NAML
> <http://camel.465427.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
Re: Poor Performance of Camel-Sql Batch insertions with Oracle DB
Posted by Saiteja Parna <sa...@gmail.com>.
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&fixedRate=true&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 <sa...@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.
>
>
>
>
>
>
Re: Poor Performance of Camel-Sql Batch insertions with Oracle DB
Posted by Zoran Regvart <zo...@regvart.com>.
Hi Saiteja,
several things, you're not providing much detail but you seem to have
narrowed the performance issue to a JDBC driver version change, why
don't you revert that change and try again?
Also you seem to be using Red Hat version of Camel, that should
entitle you to support, though in this case support from Oracle might
be appropriate.
It is going to be extremely difficult to help you if you don't provide
a scenario or a test that someone else can repeat. Performance issues
can be caused by multitude of issues, something relating to your
environment is the most likely cause.
So, either trying to narrow down the root cause or contacting support
for the products you're using should be your priorities,
zoran
On Fri, Apr 12, 2019 at 8:42 PM Saiteja Parna <sa...@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.
--
Zoran Regvart