topic Re: How to speed up the performance on tFlowToIterate in Talend Studio

How to speed up the performance on tFlowToIterate

mgchong — Tue, 25 Aug 2015 04:35:53 GMT

Hi Support,
As per subject, i am looking for a solution to improve the performance.
Currently, I am having
tFileInputDelimited > row1 > tFlowToIterate > Iterate > tOracleInput
Appreciate it. Thanks.
Regards,
MG

Re: How to speed up the performance on tFlowToIterate

Anonymous — Tue, 25 Aug 2015 08:35:14 GMT

Hi MG,
What's the rate in your work flow? Have you already used "Cursor" option in Advanced settings of tOracleInput?
Best regards
Sabrina

Re: How to speed up the performance on tFlowToIterate

Anonymous — Tue, 25 Aug 2015 08:39:12 GMT

Ask your self if it a good idea to execute for every line in your file a select statement. I would say it depends on the use case.
tFlowToIterate has absolutely none impact to the performance of a job.

Re: How to speed up the performance on tFlowToIterate

Anonymous — Tue, 25 Aug 2015 08:44:56 GMT

hi all,
tFlowToIterate in fact store the (object) data in its globalMap. That you can retrieve later.
As jlolling no impact to the performance, but could need more memory to keep performance depending on the flow.
regards
laurent

Re: How to speed up the performance on tFlowToIterate

mgchong — Tue, 25 Aug 2015 14:54:24 GMT

Hi Guys,
I gave a try on the cursor but could not see any differences. It took very long time http://screencast.com/t/DgFg3dt1VM
For my use case, I need to iterate the ID to query from the oracle DB eventually write the result to the file.
Any more idea I could achieve this? Appreciate it.
Regards,
MG

Re: How to speed up the performance on tFlowToIterate

Anonymous — Tue, 25 Aug 2015 15:13:44 GMT

Hi, sorry how can we help if we do not have a clue about your use case. Your design sounds reasonable for me if you want to use the input file as a kind of steering logic to create output files as much as you lines in the input file.
I suggest you describe what you are trying to achieve and come back again. So far from the Talend technology point of view I cannot spot any problem.

Re: How to speed up the performance on tFlowToIterate

mgchong — Tue, 25 Aug 2015 15:54:30 GMT

Hi Jlolling,
Basically my use case is to query record from the DB by the customer unique ID. For example, having list of customer unique ID in a csv file then iterate each unique ID to query from the oracle DB finally write the result into a file.
For example;
ID 001 > Query from DB > Query result of ID 001 write to file
follow by 002
....
Is there any alternative way other than using tFlowToIterate to pass each unique ID to the flow?
Regards,
MG

Re: How to speed up the performance on tFlowToIterate

Anonymous — Tue, 25 Aug 2015 16:01:12 GMT

ok, you don not want to have a dedicated file per customer, you want to have all records from the database for the customers mentioned in your text file right?
In this case your approach is bad idea.
In case of I am right with my idea:
You have 2 possibilities (only short drafts):
1. build a combined where condition with chain your customer ids as comma separated list and use this as in clause for your query
2. select your database without using the customer id as where condition and use the customer id from your file as lookup in a tMap und build a inner join between your query result and your file.
Home work to do: Lear about tMap and joining!

Re: How to speed up the performance on tFlowToIterate

mgchong — Wed, 26 Aug 2015 01:21:56 GMT

Hi jlolling,
Thanks for your advice. I will try out your approach.
Regards,
MG

Re: How to speed up the performance on tFlowToIterate

Anonymous — Wed, 26 Aug 2015 08:45:59 GMT

hi all,
I 'm using first jlolling solution in one of my job and it increase performance a lot (you could even use Oracle hint /*+ PARALLEL(XX) */ in your query depending on your db engine capabilities)
ba aware that you've got a limit of 1000 items for your oracle list.
regards