Performance improve to load the data into RDBMS.

Six Stars

Performance improve to load the data into RDBMS.

Hi,

i need help on improve the performance in Talend job

1. I have build a sample job to load the form Impala to SQL Server.

2. There is no transformation in this mapping the source has around 4 million records.

3. The data loading the data into SQL server is very slow. the rows commit per second less than 2000 records.

4. I have used batch size and Commit every option DBoutput component.

5. And i tried with enable parallel option.

Still i have bad performance in the job.

please help me  if we have any other options to tune the jobs or what stats i need to check in to Database side.

 

Thanks

Jilani Syed

Moderator

Re: Performance improve to load the data into RDBMS.

Hello,

Are you using any DB connection component and checking the "use connection option"? 

Performance issue is usually caused by the DB connection or the job design, could you upload some screenshots of your job?

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: Performance improve to load the data into RDBMS.

Hello Sabrina,

 

I am not using use connection option . and i am directly giving connection details in output component. and also i tried with enable parallel option.

as you requested please see the below screen for your reference

Impala_SQLSEVR.png

Moderator

Re: Performance improve to load the data into RDBMS.

Hello,

We need more information about your environment to address your performance issue.

Could you please create a support case on talend support portal so that we could give you a remote assistance through support cycle with priority?

https://login.talend.com/support-login.php 

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.
Employee

Re: Performance improve to load the data into RDBMS.

Hi,

 

    I will do two quick changes to test the performance difference.

 

a) Drop tDBOutput component and replace it with MSSQL server Bulk components (say tDBOutputBulkExec)

https://help.talend.com/reader/lZLCVAvlC2eua7~d4oeP4w/MrNjYmCLUMTatTpxne8GNQ

 

b) Increase the JVM Xms and Xmx parameters to a higher value to allocate more system memory in your Talend job (Please do not give exorbitant values as it also depends on underlying system memory)

 

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

Six Stars

Re: Performance improve to load the data into RDBMS.

Hello Nikhil,

 

Thanks for your response. but we have some concern to use bulkExec components.

we need install jobserver on Database server machine or at least we should have shared location on these machine.

these two cases not possible with customer.

any work around do you have to work on bulkExec components with out installing jobserver on database server machine?

 

Thanks

Jilani Syed

 

Employee

Re: Performance improve to load the data into RDBMS.

Hi,

 

    The file created by job server needs to be accessed by loader script generated by the Talend job. This means the file has to be accessed by the loader scripts. If the loader script is working from any machine, the same task can be executed by Talend Bulk components too. You may have to discuss with your DBAs for identifying the right location for this task.

 

    At the same time, did you increase the memory parameters and what was its effect? Definitely the tDBOutput component will take more time as it is not intended to be used for heavy data movement. This component is used for small or medium delta loads.

 

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

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog