[resolved] Slow fetch from oracle when there's an update at the end

[Anonymous]

Hello.
I have a job which starts with a simple select from Oracle, cursor size is 5000
"select * from table where fetched=0".
the rows are sent to another destination, let's say MSSQL.
When I run this job like so, it runs fast and fetches the data from Oralce in 5000 bulks, and commits the 
data in MSSQL every 5000 - Great.
I need to update the record in Oracle ("update table set fetched=1 where id="+row1.id).
I've chosen in the tOracleOutput to commit every 5000.
When running this job, it slowed down significantly and fetches the data 5 rows at a time.
Changed the tOracleOutput to tOracleRow - same behavior.
Any thoughts why?
Thanks

[Anonymous]

Hi  sysmq,
Could you please upload your job setting screenshots into forum?
Best regards
Sabrina

[Anonymous]

Hi, Sabrina.
The job is very simple.
tOracleInput -> tFileOutputDelimited -> tOracleOutput
tOracleInput: select id, name from emp_table where fetched=0;
tOracleOutput: update emp_table set fetched=1 where id=row2.id;
That's it.
But it runs very slowly. If I deactivate the tOracleOutput, it runs fast.
Thanks,

[Anonymous]

Hi,
Have you tried to specify the number of records to be processed in each batch by checking " Use batch" box? 

tOracleOutput: update emp_table set fetched=1 where id=row2.id;

How did you set this query in tOracleOutput? 5 rows/s is not normal speed for a simple job. Screenshots will be helpful for us to address your issue quickly.
Best regards
Sabrina

[Anonymous]

I think a screen shot would be helpful.
Is this all part of the same SubJob?
Are you using tOracleConnection?
With your tOracleOutput, could your Job be architected in such a way that you're establishing a new connection for each update?
I guess you're updating on a primary key but if not, have you indexed?

[Anonymous · Accepted solution]

Hi, I've checked number of records to bulk + and compared it to the curser size in the tOracleInput, and it works.
Thanks,