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+s465427n5833064...@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> >
