Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I chased this problem down for the past day and a half. The issue is that Talend recommends downloading an old JDBC driver from Microsoft. I downloaded the latest https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view... Version 7 driver and magic! 
To uninstall the old SQL Server module (probably v6.x) follow these instructions https://community.talend.com/t5/Design-and-Development/uninstall-an-external-jar/td-p/30541 aka delete the folder labeled studio\configuration\.m2\repository\org\talend\libraries\mssql-jar
Hello everyone !
I'm digging up the topic since, the issue is not solved !
I'm facing real issues with UPSERT in MSSQL in TOS 7.x...
I read the whole topic and managed to do some improvements :
- Using NONCLUSTERED index is better than CLUSTERED in the end, and you still have a sorted index
- I read some interesting digging up there : https://github.com/microsoft/mssql-jdbc/issues/166 : basically, JTDS, in some cases, JTDS was unpreparing the statemement at each row.
- putting "not updatable" on my primary key nearly doubled my throughput (from 120 to 250 row/s still...)
And finally I analyzed fine Events in SQLServer logs :
As you can see, the statement is "instant" for it's execution, but the latency between the Talend runtime and the Database is the real problem : 3 milliseconds.
It's all it takes to go down drastically in performance. You cannot basically go faster than 300/500 row per sec
I know pretty much how Datastage works, and I now understand why Talend is so slow on MSSQL Server !!
Datastage approach :
- You declare a "batch size" (generally 2k to 10k)
- Those 2k lines are sent in a single batch (an array of prepared statements in each insert and update statements):
- Step 1 : update statements are sent
- Step 2 : all failed updates are marked and corresponding insert statements are sent
- UPDATE then Insert is prefered to INSERT then UPDATE, because UPDATE is much more faster (not changing primary key)
- It's up to you to not have a duplicate key in your batch
Talend approach :
- You can't use batch size in update/Insert operations : it's a 1 row batch
- It's send as a prepared statement atomically
- if the insert or update fails, it launch the other statement
- you don't have to worry about having duplicate key in you your input data
In terms of performance, with a pretty slow machine these are the metrics :
- Datastage SE : 2k to 5k row/s
- Datastage PX : 100k to 400k row/s (depends on how many parallel channels you're using)
- Talend : 50 row/s
You can do whatever you want, UPSERT is never going to be a valid option with the current JTDS version (which is quite old now) because of the batch size.
You have to either :
- separate insert and update
- use insert first, then a second connector just after like that :
Delimited ==> DBOutput (Insert) ==> DBOuput (Update).
I'm going to try this one because it's to **bleep** painful to do a lookup for each table you are going to update ...
And even there, I'm not sure that the update will be fast (insert are faster in jTDS)
EDIT : as stated before, it's indeed far better with INSERT => UPDATE method (it's instant). But it creates a ton of logs ...
And with Insert if not exists :
Performance drops again.
Sorry, I was a bit overcharged by all other topics I'm working on
Here it is : https://jira.talendforge.org/browse/TDI-42913
Thank you in advance.
I found the piece of code responsible for building the sp_prepare, but I'm not confident enough to modify the code directly. you may help me through it ?