Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I selected the component and then went to the "Code Viewer" window. I didn't see any way to search there so I selected all the content and pasted it into notepad++. I see references for "whetherReject_tMSSqlOutput_2", "pstmt_tMSSqlOutput_2", "updatedCount_tMSSqlOutput_2", and several others but no "update_tMSSqlOutput_2". So I checked my "insert" path (tMSSqlOutput_1) and had the same result -- lots of references but no "insert_tMSSqlOutput_1". (Yes, I was searching case-insensitive.) I am clearly missing something...
I can see where it uses "pstmt_tMSSqlOutput_2" as an arg of type java.sql.PreparedStatement, and can see where it builds that variable, but it only shows the column values being added -- I assume as parameters to the statement.
/**
* [tMSSqlOutput_2 main ] start
*/
currentComponent="tMSSqlOutput_2";
if(log.isTraceEnabled()){
log.trace("row17 - " + (row17==null? "": row17.toLogString()));
}
whetherReject_tMSSqlOutput_2 = false;
if(row17.customer_name == null) {
pstmt_tMSSqlOutput_2.setNull(1, java.sql.Types.VARCHAR);
} else {pstmt_tMSSqlOutput_2.setString(1, row17.customer_name);
}
if(row17.ship_address == null) {
pstmt_tMSSqlOutput_2.setNull(2, java.sql.Types.VARCHAR);
} else {pstmt_tMSSqlOutput_2.setString(2, row17.ship_address);
}
if(row17.ship_postalcode == null) {
pstmt_tMSSqlOutput_2.setNull(3, java.sql.Types.VARCHAR);
} else {pstmt_tMSSqlOutput_2.setString(3, row17.ship_postalcode);
}
if(row17.ship_city == null) {
pstmt_tMSSqlOutput_2.setNull(4, java.sql.Types.VARCHAR);
} else {pstmt_tMSSqlOutput_2.setString(4, row17.ship_city);
}
if(row17.ship_county == null) {
pstmt_tMSSqlOutput_2.setNull(5, java.sql.Types.VARCHAR);
} else {pstmt_tMSSqlOutput_2.setString(5, row17.ship_county);
}
if(row17.ship_state == null) {
pstmt_tMSSqlOutput_2.setNull(6, java.sql.Types.VARCHAR);
} else {pstmt_tMSSqlOutput_2.setString(6, row17.ship_state);
}
if(row17.ship_country == null) {
pstmt_tMSSqlOutput_2.setNull(7, java.sql.Types.VARCHAR);
} else {pstmt_tMSSqlOutput_2.setString(7, row17.ship_country);
}
if(row17.mktsegcode == null) {
pstmt_tMSSqlOutput_2.setNull(8, java.sql.Types.VARCHAR);
} else {pstmt_tMSSqlOutput_2.setString(8, row17.mktsegcode);
}
if(row17.crc == null) {
pstmt_tMSSqlOutput_2.setNull(9, java.sql.Types.INTEGER);
} else {pstmt_tMSSqlOutput_2.setLong(9, row17.crc);
}
if(row17.cmf == null) {
pstmt_tMSSqlOutput_2.setNull(10 + count_tMSSqlOutput_2, java.sql.Types.VARCHAR);
} else {pstmt_tMSSqlOutput_2.setString(10 + count_tMSSqlOutput_2, row17.cmf);
}
pstmt_tMSSqlOutput_2.addBatch();
nb_line_tMSSqlOutput_2++;
if(log.isDebugEnabled())
log.debug("tMSSqlOutput_2 - " + ("Adding the record ") + (nb_line_tMSSqlOutput_2) + (" to the ") + ("UPDATE") + (" batch.") );
batchSizeCounter_tMSSqlOutput_2++;
//////////batch execute by batch size///////
class LimitBytesHelper_tMSSqlOutput_2{
public int limitBytePart1(int counter,java.sql.PreparedStatement pstmt_tMSSqlOutput_2) throws Exception {
try {
if(log.isDebugEnabled())
log.debug("tMSSqlOutput_2 - " + ("Executing the ") + ("UPDATE") + (" batch.") );
for(int countEach_tMSSqlOutput_2: pstmt_tMSSqlOutput_2.executeBatch()) {
if(countEach_tMSSqlOutput_2 == -2 || countEach_tMSSqlOutput_2 == -3) {
break;
}
counter += countEach_tMSSqlOutput_2;
}
if(log.isDebugEnabled())
log.debug("tMSSqlOutput_2 - " + ("The ") + ("UPDATE") + (" batch execution has succeeded.") );
}catch (java.sql.BatchUpdateException e){
int countSum_tMSSqlOutput_2 = 0;
for(int countEach_tMSSqlOutput_2: e.getUpdateCounts()) {
counter += (countEach_tMSSqlOutput_2 < 0 ? 0 : countEach_tMSSqlOutput_2);
}
log.error("tMSSqlOutput_2 - " + (e.getMessage()) );
System.err.println(e.getMessage());
}
return counter;
}
public int limitBytePart2(int counter,java.sql.PreparedStatement pstmt_tMSSqlOutput_2) throws Exception {
try {
if(log.isDebugEnabled())
log.debug("tMSSqlOutput_2 - " + ("Executing the ") + ("UPDATE") + (" batch.") );
for(int countEach_tMSSqlOutput_2: pstmt_tMSSqlOutput_2.executeBatch()) {
if(countEach_tMSSqlOutput_2 == -2 || countEach_tMSSqlOutput_2 == -3) {
break;
}
counter += countEach_tMSSqlOutput_2;
}
if(log.isDebugEnabled())
log.debug("tMSSqlOutput_2 - " + ("The ") + ("UPDATE") + (" batch execution has succeeded.") );
}catch (java.sql.BatchUpdateException e){
for(int countEach_tMSSqlOutput_2: e.getUpdateCounts()) {
counter += (countEach_tMSSqlOutput_2 < 0 ? 0 : countEach_tMSSqlOutput_2);
}
log.error("tMSSqlOutput_2 - " + (e.getMessage()) );
System.err.println(e.getMessage());
}
return counter;
}
}
if ((batchSize_tMSSqlOutput_2 > 0) && (batchSize_tMSSqlOutput_2 <= batchSizeCounter_tMSSqlOutput_2)) {
updatedCount_tMSSqlOutput_2 = new LimitBytesHelper_tMSSqlOutput_2().limitBytePart1(updatedCount_tMSSqlOutput_2,pstmt_tMSSqlOutput_2);
batchSizeCounter_tMSSqlOutput_2 = 0;
}
////////////commit every////////////
commitCounter_tMSSqlOutput_2++;
if(commitEvery_tMSSqlOutput_2 <= commitCounter_tMSSqlOutput_2) {
if ((batchSize_tMSSqlOutput_2 > 0) && (batchSizeCounter_tMSSqlOutput_2 > 0)) {
updatedCount_tMSSqlOutput_2 = new LimitBytesHelper_tMSSqlOutput_2().limitBytePart1(updatedCount_tMSSqlOutput_2,pstmt_tMSSqlOutput_2);
batchSizeCounter_tMSSqlOutput_2 = 0;
}
if(log.isDebugEnabled())
log.debug("tMSSqlOutput_2 - " + ("Connection starting to commit ") + (commitCounter_tMSSqlOutput_2) + (" record(s).") );
conn_tMSSqlOutput_2.commit();
if(log.isDebugEnabled())
log.debug("tMSSqlOutput_2 - " + ("Connection commit has succeeded.") );
commitCounter_tMSSqlOutput_2=0;
}
tos_count_tMSSqlOutput_2++;
/**
* [tMSSqlOutput_2 main ] stop
*/
I was using a Mac at the time but was assuming you were using Windows when I suggested that.
I've just realised what you are doing wrong. I was not talking about the Code Viewer (its useless in my eyes). I was talking about about the "Code" tab at the bottom left of the design window....
Click that and you will see the whole code for the job. It is also useful when looking for compilation errors or trying to identify errors in error stacks. In fact, you should be spending a lot of time looking at this to quickly debug issues.
Ah, yes, that is more helpful. 
Clearly the key column is being used in the WHERE clause -- it appears in the Talend statement:
String update_tMSSqlOutput_2 = "UPDATE [" + tableName_tMSSqlOutput_2 + "] SET [customer_name] = ?,[ship_address] = ?,[ship_postalcode] = ?,[ship_city] = ?,[ship_county] = ?,[ship_state] = ?,[ship_country] = ?,[mktsegcode] = ?,[crc] = ? WHERE [cmf] = ?";
And in the resulting line shown in SQL Profiler:
exec sp_prepare @p1 output,N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 bigint,@P9 nvarchar(4000)',N'UPDATE [dbo].[customer] SET [customer_name] = @P0 ,[ship_address] = @P1 ,[ship_postalcode] = @P2 ,[ship_city] = @P3 ,[ship_county] = @P4 ,[ship_state] = @P5 ,[ship_country] = @P6 ,[mktsegcode] = @P7 ,[crc] = @P8 WHERE [cmf] = @P9 ',1
Which is followed by (for each row):
exec sp_execute 1,...
NOTE: It is interesting that the sp_prepare shows nvarchar(4000) for every string parameter despite each column being defined explicitly as varchar(n) with an appropriate length. But this is probably a generic thing done for all parameters since it requires NVARCHAR.
I did find the article Watch out those prepared SQL statements. It indicates that use of sp_prepare/sp_execute is typical of ODBC, OLEDB, and, apparently, JDBC. But it also points out a potential issue with the sp_unprepare not always being triggered. I'm not sure of the impact of that but I wouldn't suspect it is related to what I am seeing.
I'm completely stumped at this point. I have no explanation for the poor performance compared to Datastage.