topic [resolved] tOracleInput component is painfully slow when using a "With Clause" in Talend Studio

[resolved] tOracleInput component is painfully slow when using a "With Clause"

Anonymous — Wed, 15 Jul 2015 10:55:53 GMT

Has anyone else noticed that the tOracleInput component is painfully slow when processing large sets of data using a With Clause? I have a query that I am running in a tOracleInput which takes 35 minutes. In SQL Developer it takes under 10 seconds. I'm using a cursor and have tried several different cursor sizes to experiment. The purpose of the query is to find duplicates in the dataset and return all rows with an identifier showing the type of duplicate. As I said, for 20,000 + rows it runs in under 10 seconds (the db is not a very high spec machine). The query could possibly be optimised a little (it is a first attempt) and I have already indexed relevant columns and gather stats before I run the query in the job. I just can't figure out why SQL developer will return the data in under 10 seconds, yet Talend takes 35 + minutes. Any ideas?
I am using Oracle 11 and Talend Platform for Data Management 5.6.1.

Re: [resolved] tOracleInput component is painfully slow when using a "With Clause"

Anonymous — Mon, 20 Jul 2015 03:42:34 GMT

Hi rhall,
Did you also use tOracleConnection component in your work flow?
Performance issue is usually caused by the DB connection or the job design, would you mind uploading some screenshots of your job design?

Best regards
Sabrina

Re: [resolved] tOracleInput component is painfully slow when using a "With Clause"

Anonymous — Mon, 20 Jul 2015 09:14:17 GMT

I didn't use a tOracleConnection as I like to be able to set the commit rate, however if you could set the commit rate AND use the tOracleConnection that would be good. This needs to be enabled. I know there are ways around this, but they are clumsy.
The job design was relatively simple, but I removed everything but the query and a tlogrow. It was still much slower than SQL Developer.
However, I think I found my issue. This happened while I was debugging the job. During this process I was killing the job midway through and it crashed a few times. It looks like Talend does not release connections from the Studio when they are not closed elegantly and this caused my issue. In SQL Developer I was using the same connection to carry out all of my tests. When I shutdown the Studio it released all of the connections and I was able to run the query in a reasonable amount of time.