Performance improve to load the data into RDBMS.

[jilanisyed]

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

[Anonymous]

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

[jilanisyed]

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

[Anonymous]

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

[Anonymous]

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

[jilanisyed]

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

[Anonymous]

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