topic Re: Qurery Performance in Talend Studio

Qurery Performance

lmit — Tue, 29 Jan 2019 14:21:01 GMT

Hi All,

We are using Oracle DB , while executing queries in talend , its taking more time

so I wanted to know can we write queries using indexes instead of table name or can anyone suggest any other performance tuning methods while using Oracle DB

Thanks,

lmit

Re: Qurery Performance

David_Beaty — Tue, 29 Jan 2019 14:30:33 GMT

Hi,

If you run the query through something like SQL Developer, does it still take a long time to run?

Re: Qurery Performance

Anonymous — Tue, 29 Jan 2019 16:57:09 GMT

Hi,

Talend is just a medium to execute your SQL queries created within the tOracleInput component.

So if a query is taking more time in executing, the place I would look is the explain plan section of your SQL Developer.

Please refer the below link for an example to verify the explain plan.

https://stackoverflow.com/questions/30070910/how-do-i-view-the-explain-plan-in-oracle-sql-developer

Now, there are sophisticated tools provided by Oracle like SQL Tuning Advisor which also can help you to verify whether your query is fully optimized or not.

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/TuningAdvisor/TuningAdvisor.htm

Talend is just providing the mechanism to run these queries in a hassle free manner. Hope you got my point.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Qurery Performance

lmit — Wed, 30 Jan 2019 05:30:35 GMT

Thanks David for your reply no if I run the query in SQL Developer its not taking time and my query is simple select query by joining 3 tables

Thanks,

lmit

Re: Qurery Performance

lmit — Wed, 30 Jan 2019 05:33:50 GMT

Hi Nikhil,

Thanks for your reply but if I ran the same query in SQL Developer its taking less time and its a simple select query not much big logic also

only if I run the query in talend its taking more time

Thanks,

lmit

Re: Qurery Performance

Anonymous — Wed, 30 Jan 2019 05:38:47 GMT

Hi,

I hope you are using tOracleInput to extract the data. When you are telling there is a difference, what is the amount of time difference you are facing to extract the query? And how much is the data volume you are trying to extract?

How about the amount of memory you have allocated for the Talend job? Also, did you cross check with your DBA Team to see whether there is any difference in optimization plan while executing the job from Talend?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Qurery Performance

David_Beaty — Wed, 30 Jan 2019 09:36:43 GMT

Hi,

Not sure if this is going to be the problem, but try putting this into the additional parameters on the oracle connection component:

useUnicode=true

I've had problems previously where NOT setting this caused it to not use an index on a table was updating, which caused massive performance problems - as you would expect.