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

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

Anonymous — Wed, 23 Jan 2019 06:01:17 GMT

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

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

ThWabi — Wed, 23 Jan 2019 07:02:16 GMT

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

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

Anonymous — Wed, 23 Jan 2019 07:28:10 GMT

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

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

dipanjan93 — Wed, 23 Jan 2019 07:42:07 GMT

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

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

Anonymous — Wed, 23 Jan 2019 08:49:56 GMT

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 🙂

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

Anonymous — Wed, 23 Jan 2019 10:55:59 GMT

Hi,

I am getting the following error:

Thanks,

Dhara

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

Anonymous — Wed, 23 Jan 2019 11:05:24 GMT

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 🙂

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

Anonymous — Wed, 23 Jan 2019 11:37:19 GMT

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:

Thanks,

Dhara

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

ThWabi — Wed, 23 Jan 2019 12:01:18 GMT

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

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

Anonymous — Wed, 23 Jan 2019 12:39:15 GMT

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

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

ThWabi — Thu, 24 Jan 2019 13:43:26 GMT

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

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

Anonymous — Fri, 25 Jan 2019 07:19:43 GMT

Hi,

Bulk load runs with error file accessing the file.

Thanks,

Dhara

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

ThWabi — Fri, 25 Jan 2019 08:31:25 GMT

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