whats the reason for db performance degrade by using "use an existing connection" check in DBOutput components

Four Stars

whats the reason for db performance degrade by using "use an existing connection" check in DBOutput components

Hi All,

I have a scenario where I am loading data from file to database and also from database to database(oracle) without any transformations.I have created tOracleConnection component and trying to use it in all the DB components of job especially in the DB output components the batch size and commit every options are disabled when i check the "use an existing connection" check box.

May I know the reason for that and what can I do to improve the performance of loading the data by using the existing connection option?

I have read in many posts saying that we need to disable the "use an existing connection" to use the above options. Can I know the strict reason for that?

Then whats the advantage of creating tOracleConnection component if it doesn't allow to enable the options on reuse of the connection in output components?

 

Quick reply would be appreciated

Four Stars

Re: whats the reason for db performance degrade by using "use an existing connection" check in DBOutput components

Hi Guys,

Could anyone please help me with this??

Thanks in advance

Eight Stars

Re: whats the reason for db performance degrade by using "use an existing connection" check in DBOutput components

Yes. I also faced same Issue. Better You use connection from Repository and play with Commit Every and Batch Size. If Your Database is not RDS better try to implement Bulk Load. It improves Job Performance. 

Four Stars

Re: whats the reason for db performance degrade by using "use an existing connection" check in DBOutput components

Hi,

Then whats the reason or advantage of creating oracle connection if we are unable to use in tOracleOutput with Batch Size and Commit Every enabled?

 

For example consider I have a job which have 3 DBinput and 3 DBoutput components pointing to same DB.Then if I use connection from repository for the output components then it will create a total of 4 (1 oracle conn + 3 in output components seperately) connections right?

So whats the advantage of tOracle Conncetion then?

Four Stars

Re: whats the reason for db performance degrade by using "use an existing connection" check in DBOutput components

Hi @sabrina,

 

Could you please help me with this??

 

Thanks,

KiraN Kumar

Moderator

Re: whats the reason for db performance degrade by using "use an existing connection" check in DBOutput components

Hello,

This is a workaround in this topic:https://community.talend.com/t5/Design-and-Development/Commit-every-with-existing-connection/m-p/757... for your issue.

Additionally, have you tried to take tOracleoutputBulkExe as your output?

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.