DB Connection Best Practices

[Tech8]

What the best practices are in terms of db connections? I see that some people use only 1 connection and im using between 3 and 5 when im creating a complex job (having only 1 database used in this job). I use to always have 1 connection for querys and then the rest for updates, inserts... 

My question is: Am i using to many connections and this will lead me to problems in the future? In some cases im forced to use several connections because once are Auto-commit and other non.

[Anonymous · Accepted solution]

Hi,

    DB connections are a way to reuse the connectivity between database and Talend especially when the transaction need to be either committed or rollbacked in one shot. In an ideal method, if you can reuse the connection for collecting data from multiple tables (especially for lookups), then you should go for it.

     If you are connecting one connection for every table lookup, it is nothing but individual connection made through the component itself. At the same time, what I would do normally is to see the performance of the connections and DB queries using DB utility tools also (like Oracle Tuning advisor, ADDM report). This will give the performance of each connection and show the corresponding performance. For example, if you are doing lot of heavy duty work using a single connection, it will not be optimal.

     In short, there is no single point solution for your query. Try different methods based on your specific use case and also do parallel monitoring using DB tools. This will give you the optimum performance from DB to Talend.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Tech8]

Thanks for your quick response. I was looking for a rules to follow but i supose it depends of each case. 

I will try using this tools to see whats the most optimized way.

Kind Regards!