Data dump from one database table to other table is very slow in Azure SQL Server

Six Stars

Data dump from one database table to other table is very slow in Azure SQL Server

Hi,

 

Why it takes around 11 minutes to dump 2.8 million rows from one database table to other table on Azure Sql server?

Do anyone have solution to speed up the performance on Azure Server?

 

Thanks,

Dhara

 

Seven Stars

Re: Data dump from one database table to other table is very slow in Azure SQL Server

Hi Dhara,

 

the MySQL input component I work with has an advanced setting "enable stream". I do not know about Azure SQL server, maybe this setting is there, too?

Are you using a bulk load component? If you are using an output component with inserts, did you consider to use and ajust the 'batch size' and 'commit every' settings? Have you thought about 'enable parallel execution'?

If you are sending the rows through a tMap, you might want to ajust the 'max buffer size' advanced setting.

 

Best regards,

 

Thomas

 

Six Stars

Re: Data dump from one database table to other table is very slow in Azure SQL Server

Hi Thomas,

 

I am only using tMSSqlInput and tMSSqlOutput component. I have tried 'batch size' and 'commit every' settings but still performance is low. I am only executing single job.

 

Thanks,

Dhara

 

Six Stars

Re: Data dump from one database table to other table is very slow in Azure SQL Server

Check increasing JVM size. Also remove if you have used any tLogRow component within any subjob. 

Employee

Re: Data dump from one database table to other table is very slow in Azure SQL Server

Hi,

 

    I believe you are using tDBInput and tDBOutput components to load the data. You can do two changes to make it faster.

 

a) Currently you must be using a single tDBInput Component to extract the data. You can use a tparallelize option to run parallel operations where each tDBInput will fetch data from certain partitions. Please make sure that you are providing enough memory also for this operation. This way, we can increase the throughput at reading stage.

b) In writing stage, could you please use tDBBulk components instead of normal output component? This will increase the processing at output stages.

 

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: Data dump from one database table to other table is very slow in Azure SQL Server

Hi,

 

I am getting the following error:

Capture.JPG

 

Thanks,

Dhara

Employee

Re: Data dump from one database table to other table is very slow in Azure SQL Server

Hi Dhara,

 

     Could you please share your job details screenshots so that Talend Community members will get more clarity?

 

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: Data dump from one database table to other table is very slow in Azure SQL Server

Hi,

I have set  Additional JDBC parameter = "encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;" as I am dumping data on Azure Server. Following is my Talend Job:

Capture1.JPG

 

Thanks,

Dhara

Seven Stars

Re: Data dump from one database table to other table is very slow in Azure SQL Server

Hi Dhara,

 

relating to the error "file ... could not be opened": Did this job run in Talend Studio on your local PC or on a remote jobserver? Does the file "mssql_data.txt" exist in the directory D:\<something> (on your PC or the host that runs Talend jobserver)?

 

Best regards,

 

Thomas

Six Stars

Re: Data dump from one database table to other table is very slow in Azure SQL Server

Yes I run this file in Talend Studio on my local PC and file is located in local PC on the same path.
Seven Stars

Re: Data dump from one database table to other table is very slow in Azure SQL Server

Hello Dhara,

 

can you please check if the bulk load component runs without error when you place the "mssql_data.txt" file on the SQL server in a corresponding directory?

 

Best regards,

 

Thomas

Six Stars

Re: Data dump from one database table to other table is very slow in Azure SQL Server

Hi,

 

Bulk load runs with error file accessing the file.

 

Thanks,

Dhara

Seven Stars

Re: Data dump from one database table to other table is very slow in Azure SQL Server

Hi Dhara,

 

I know from our MySQL that there is a setting to allow bulk loads only from a certain directory. Maybe your SQL server has a similar setting in place?

I am not familiar with Azure SQL server, so I have to leave it to other experts.

 

Best regards,

 

Thomas

 

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch