How to increase performance?

[rp2018]

Performance loading from source db to target db is very slow. Our source db is on premise and target db on Azure.  Loading three tables and all of them total around 25,000 records and it takes 5 minutes to load. No transformation is done, only straight load.  My job uses tMSSqlConnection to connect and auto commit option is selected.  Is there any options to set to increase the speed?

[Anonymous]

Hi,

You can execute them in parallelize (If you are using enterprize edition) else use the Multi Thread option in Job tab.

Along with that set the Batch size to 10k and commit size as well to 10k. 

Also the number of processes you can put in tParallelize should be less than the number of cores you are using on your server.

Let me know if that helps.

Best Regards,

Abhishek

[Anonymous]

@abhishek mentions some good things to check/change, but there is also a known issue with the MSSqlServer components (depending on the version you are using). There is an easy workaround though. All you need to do is to add....

 "sendStringParametersAsUnicode=false"

....to the "Additional JDBC Parameters" in the "Advanced" tab of your Insert or Update component. You should see an improvement from that. 

You should also keep in mind that your performance will suffer a bit by the fact that you data is moving from on-premise to the cloud.....although nowhere near to the point where you are getting only 250 rows a second, unless you have a VERY slow internet connection. 

[rp2018]

I don't see any option under Advanced settings in the tmssqlinput or tmssqloutput to set

"sendStringParametersAsUnicode=false"

.  Also,  where do you set the # of how many rows to commit?

[Anonymous]

Sorry, I didn't take in that you are using a connection component. The advanced settings will be there when you use a connection component.

[Jesperrekuh]

Do you have a (unique/primary) key and have UPDATE or INSERT or the other way around?
Update/insert is the performance killer.

Suggestion, load from premises into tmp_tbl and run a t-sql script to load this tmp table into target tbl. Destroy the tmp table on success.

[Anonymous]

Hello,

Would you mind posting your current work flow screenshots on forum which will get more visibility and more help?

Best regards

Sabrina

[rp2018]

I don't see any option in tMssqlconnection under advanced settings to add sendStringParametersAsUnicode=false.

[Anonymous]

Look in the Basic Settings. For some reason the "Additional JDBC Parameters" are on the Advanced tab for the Input and Output components, but on the Basic tab for the Connection components.

[rp2018]

I've added this string and still my performance didn't improve.  I'm only loading around 6,000 records and no transformation at all.  Use Batch size is set to 10000