[resolved] Improve the data transfer rate through tOracleRow and tParseRecordSet

[Anonymous]

Hi,
I have a scenario where for each input record I have to select data (two ids) from other tables (using select in select query; select ID, Name from tableA where ID_1 IN (select ID from tableB where key='input.record.key' )). I'm using tOracleInput, tOracleRow and tParseRecordSet in the flow as per given below image. The result will be passed through tParseRecordSet and extract the selected values. 
I have around 700000 records in the input table, but the data transfer rate is around 20 rows/s. How can I improve the transfer rate? Is there any other way to get done the same thing. Please advise on this.

Thanks and Regards,
Asanka.

[Anonymous]

Hi Asanka,
You can break your query based lookup to Talend tMap
- Take TalendInput
- Use tMap
- Use lookup data from your where clause and put it into the flat file and use as a lookup
- Use inner join and if the lookup data is more, use a file system storage
and then check the performance...
Reason for slowness is for each row from input you are making a round trip to database for getting one more row...your job could look like
                  Lookupdata
                        |
tDBInput------>tMap-- ParseRecordSet-->tLogRow
Try this way..
Vaibhav

[Anonymous]

Hi Vaibhav,
Thanks for your reply. 
In my case I'm not allowed to use flat files, and the other thing is I'm not getting the lookup option that you suggested. Can you please elaborate it more with my sample query of the initial post (I have to select data from two tables instead of the input table)?
Thanks and Regards,
Asanka.

[Anonymous]

Hi Asanka,
- Use following query for the source for tMap using query select ID, Name from tableA 
- Use another DB component in the lookup using query select ID from tableB 
Use tMap to inner join with input table and lookup table for ID.tableA=ID.tableB and other joins in your where clause
I hope now it is clear.. 

Vaibhav

[Anonymous]

Hi Vaibhav,
I tried as per you suggested. Following image depicts sample job design. I load required fields from TableA and TableB, then join input.record.key with TableB's corresponding column in order to get TableB's ID. Then join TableA with Table B using the TableB's ID with corresponding column of the TableA in order to get the desired record from TableA (ID, Name from TableA). 
When the job is run I noticed that the first join works fine and the ID is selected, but when join TableA with TableB using that ID it doesn't give any result (it seems the ID selected from first join doesn't pass to the second join)even though there are matching result for the join.  What would be the reason for this?  

Thanks and Regards,
Asanka. 

[Anonymous]

if you are not using SP to get result then you can use tOracleInput instead of tOralcleRow. 
In advance property of tOracleInput you can use cursor option with 10K size.