One Star

DB Connection timeout?

Hi,
I'v built a Talend flow which must elaborate millions of record getting them from a source file.
Initially, I've used a not shared common Oracle database connection for all the subjobs, but it was very slow; in order to increase the performance, I've used a shared common Oracle database connection for all the subjobs and it became much faster!
But this second solution makes me worry about the DB Connection timeout: I'm scared that keeping the connection opened too much time can be a problem.
Is it a real problem?
Please help me Smiley Sad
Thanks
3 REPLIES
Four Stars

Re: DB Connection timeout?

http://docs.oracle.com/cd/B14117_01/win.101/b10117/features001.htm
do you have rights to change the server properties?
Vaibhav
One Star

Re: DB Connection timeout?

Why you think it could be a problem?
I believe using shared connections for long time should not cause timeout error, unless there's a connectivity problem or a dba has set some connection time.
If the database server is strong enough to hold the large processing data then do not be scared and these days they are usually strong. I do use several jobs to share connections and they pull/push large volumes of data without any connection time out issue.
If you still are scared of the job might see a failure because the connection is open then try not using share connection, but instead local connection contexts to component and the output component you can see in the Advanced settings, "commit every" and "batch size" properties.
Also did you try using tHashInput and tHastOutput to check if they will increase the speed? They do for me.
Also it depends on how you are running the job, via the client or the server TAC or exporting to some unix server. They have varying effects.
One Star

Re: DB Connection timeout?

Why you think it could be a problem?
I believe using shared connections for long time should not cause timeout error, unless there's a connectivity problem or a dba has set some connection time.
If the database server is strong enough to hold the large processing data then do not be scared and these days they are usually strong. I do use several jobs to share connections and they pull/push large volumes of data without any connection time out issue.
If you still are scared of the job might see a failure because the connection is open then try not using share connection, but instead local connection contexts to component and the output component you can see in the Advanced settings, "commit every" and "batch size" properties.
Also did you try using tHashInput and tHastOutput to check if they will increase the speed? They do for me.
Also it depends on how you are running the job, via the client or the server TAC or exporting to some unix server. They have varying effects.

@sanvaibhav: No, I haven't :-(
@gkotpalliwar: I've set the autocommit checkbox at true in the tOracleConnection which are in the subjobs.
How can tHashInput and tHashOutput improve the speed?
The job is executed on a unix server by crontab on the sh launcher generated by Talend