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 <saitejap.j...@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+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> >> >
