topic Re: Convert XMLType to String in Oracle Input Component With Optimisation in Talend Studio

Convert XMLType to String in Oracle Input Component With Optimisation

Anonymous — Tue, 04 Nov 2014 13:40:40 GMT

Hi All,
I am having source with XMLType column in Oracle. i am using below configuration and My job design is having batch processing and that is as follows.
tOracleConnection1, tOracleConnection2 for source and target.
tOracleInput---->Main--->tMap-->main-->tOracleOutput.
tOracleCommit.
tOracleInput Configuration( sorry i can not post actual picture but it is as good as job design)

Selecting only 2 Columns. on is ID and another one XMLType xmls
Advance setting: Convert XMLType to java type is checked.
and xmls column provided for conversion.
Advance setting: Use cursor Option is checked. with 10,000 value.
Advance setting: Trim All also Checked.
tMap Configuration:

converting xmls to string using String.ValueOf(xmls) to string.
tOracleOutput Configuration:

Advance setting: enable parallel execution with 10x executions.
Job Setting configuration:

Extra Tab: multi-threading enabled.
parallelize Buffer units Size to 500000.
JVM Parameters min-1Gb and Max-4 GB.

Now the Problem:

I am having more than 250000 rows in source and I have to process them in Target.
It is taking more than 19 hours to complete the process.
Source XML must be parse and then store into target table as XMLType data type column.

Anyone suggest how can I optimise this job for performance? I know it is taking too much time for 2L records but I tried every Sort of solution but still no improvement.
Note: before posting answer please read all the configuration above.

Re: Convert XMLType to String in Oracle Input Component With Optimisation

Anonymous — Tue, 04 Nov 2014 14:02:18 GMT

hi,
where is the 'traffic jam' ?
I guess it's on reading XMLTYPE
try to read XMLTYPE as byte [] (array] and convert in String (just new String(row1.yourField).
read data with advanced setting & extract could be slow depending on your Xml.
I haven't done any workbench but read in byte[] is 'always' faster.
Don't use shared connection and defined it in your output Oracle component.
try bach size + commit every xxxxxx rows (try 1000 10000). with no parralelize.
add parralelize depending on core's number if necessary.
hope it helps
regards
laurent

Re: Convert XMLType to String in Oracle Input Component With Optimisation

Anonymous — Tue, 04 Nov 2014 14:33:28 GMT

it seems to be we are close to our solution but problem is how to convert source XMLType to binary array in Oracle SQL select?
i have changed input java type to Byte[] but it is giving me wrong result after converting back to string please suggest.

Re: Convert XMLType to String in Oracle Input Component With Optimisation

Anonymous — Wed, 05 Nov 2014 09:26:10 GMT

it is giving me wrong result after converting back to string please suggest

what sort of problem ? still binary in your converted String ?
regards
laurent

Re: Convert XMLType to String in Oracle Input Component With Optimisation

Anonymous — Thu, 06 Nov 2014 07:33:03 GMT

yes it has same type of binary code.
I tries several ways but no luck.

Re: Convert XMLType to String in Oracle Input Component With Optimisation

Anonymous — Thu, 06 Nov 2014 09:11:25 GMT

i've encountered same 'problem' and remove them in the flow.
but for my purpose it was (very more) faster than using XMLTYPE and extracting Oracle method.
so still a workaround but looking for a proper&fast solution
regards

Re: Convert XMLType to String in Oracle Input Component With Optimisation

Anonymous — Thu, 06 Nov 2014 09:16:37 GMT

Yes i have check several option, Extracting using SQL is faster but i need source XML along with records. that thing is taking time. if we are able to convert buyt[] array to String then it is faster than this.

Re: Convert XMLType to String in Oracle Input Component With Optimisation

Anonymous — Thu, 06 Nov 2014 09:29:07 GMT

let us know if you find pretty solution
i'll do the same
regards