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

Six Stars

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

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

Accepted Solutions
Six Stars

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

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

All Replies
Moderator

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

Hi sysmq,
Could you please upload your job setting screenshots into forum?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

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

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,
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

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

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?
Six Stars

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

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