topic Re: Slow SQL Server updates in Talend Studio

Slow SQL Server updates

Anonymous — Wed, 21 Mar 2018 19:03:56 GMT

Is Talend making any effort to improve Talend performance with MS SQL Server?

Having searched this community and old talendforge forums entries, it appears there are performance issues with Talend using SQL Server. I found this article (Data loading from MS SQL Server DB to other MS SQL Server DB is very low compared to SSIS) which confirms my own experience in comparing Talend to Datastage (2005 Server Edition -- an old, no longer supported version).

I built a Talend job to mirror what the old Datastage job is doing -- syncing content between two tables: a source table on one SQL Server and a target table on another SQL Server. The data is about 220k rows. The Datastage jobs does this (with MSOLEDB stage) in < 2 minutes with 2238 rows/sec. Talend is poking along (with tMSSqlOutput component) and barely able to make less than 30 rows/sec! Really?? Almost 100x slower? What the heck is it doing?

I tried changing the batch size, changing to use a component connection and changing the Commit Every setting; tried using select top to limit rows (just to see the impact). None of these made significant differences in performance. Still achieves < 30 rows/sec.

Re: Slow SQL Server updates

Anonymous — Wed, 21 Mar 2018 19:29:37 GMT

All I can say is that your job or database are poorly configured if those numbers are true. I'm currently working with Ms SQL Server and am able to get around 2500 rows per second without even looking into optimisation of my job.

Re: Slow SQL Server updates

Anonymous — Wed, 21 Mar 2018 19:42:30 GMT

I appreciate you confirming you are able to see decent results. That gives me hope that it is something misconfigured in the job. It is clearly not the database as Datastage can achieve respectable rates similar to what you stated.

The input side of the job all performs very well. The job uses tMSSQLInput to select all rows in the source table, another tMSSQLInput to select all rows from the target table into a tHashOutput, and then compares the two streams in a tMap, sending the output to either an insert path or an update path depending upon whether the record exists in the target with the same CRC (calculated in a tAddCRCRow to evaluate changes to the columns).

If I direct the insert and update paths to simple delimited files, the output is also performant reaching 51k rows/sec. (Of course, I realize a flat file is going to be extremely faster than a DB update. Just using it to isolate where the issue lies.)

When I enable the tMSSqlOutput components on the insert and update paths, the performance drops to < 30 rows/sec. Thus, the reason I've been playing around with the various settings for that component.

Re: Slow SQL Server updates

Anonymous — Wed, 21 Mar 2018 20:33:39 GMT

I built a new job that does nothing but select rows from one table and insert them into a new table in the same DB on the same server. I see the same results with this new, simpler job even though it is on a different DB and different server than the previous job was running against.

Based upon the displayed statistics, it cranks along fine at about 1k-2k rows/sec until it hits 10k rows and then just apparently hangs. If I wait, it will eventually finish and I'll then see the 30 rows/sec rate.

Given the 10k limit, I tried changing the "Batch Size" and "Commit Every" values (which appear to be the same as the "Array Size" and "Transaction Size" in Datastage) to other than 10k. As expected when I do this, it then hangs at whatever the larger of those two values are.

Re: Slow SQL Server updates

Anonymous — Wed, 21 Mar 2018 21:08:53 GMT

Right, I have just set up a job based on a table I have with 37668 rows. First, I essentially used Talend to read everything into a tHash component and write it to a new version of the table (but with no indexes or primary key). I used Talend to "create table if does not exist" and it built the table and loaded all 37668 rows in 1.74 seconds. This was with standard "out of the palette' settings.

However, inserting into an empty table and updating are two different beasts, so I then changed the job to update every single one of the 37668 rows. The only change I made was to the "Action on data" option and I set the update key using the Advanced settings' "Use field options". I then ran the job again....and I managed to get 47.66 rows per second....

Pretty terrible, but I barely changed anything and the database table has no keys or indexes.

I next played around with the "Use batch size" and "Commit every" settings and got marginal gains, but nothing substantial.

Now remember I said I have no keys or indexes on the table. Well I decided to add a clustered index on the update key field. This is an entirely reasonable thing to do, in fact it could be argued that you should index your tables appropriately to how they are used. If you update by a combination of columns as a composite key, you should create an index based on that combination of columns. Anyway, I added the index and ran exactly the same bog standard update with default settings (other than the "field options" settings). This was the result....

19598.34 rows per second from 47.66 just by adding an index on the update key.

Now I understand you may not be able to add any new indexes on your table, but I assume you have some already. Are you able to set the field options of the db component to match any existing indexes? Can you rebuild the index before you run? Essentially you have to performance tune the Talend job like you would performance tune a SQL query (going back to ETL with just database links 🙂 ).

Re: Slow SQL Server updates

Anonymous — Wed, 21 Mar 2018 21:49:28 GMT

UPDATE: I just realized that this simple job is doing a straight insert so the update key won't have any affect. DOH! I built the same simple job in Datastage for comparison and will do further testing. I'll try the update key in the job doing the updates and see if that is an improvement.

Well, that is interesting. (Note that this simple job is just doing an insert, not update.) Refer to images, below:

The key in my target table is already a primary clustered key.
I have that column marked as the key in my Talend schemas.
I enabled "Field Options" and checked the box under "Update Key" beside that column.
I set "Commit Every" and "Batch Size" back to the default 10k.
Performance hasn't changed. It reaches 10k fine, and then hangs.

Re: Slow SQL Server updates

Anonymous — Wed, 21 Mar 2018 22:39:31 GMT

Figured out one thing: I had been using the Microsoft provided JDBC provider. Apparently, that was a bad idea. In the simple insert job, I switched to the Open Source JTDS provider and it now is providing very respectable numbers. However, making that switch in my other job (the one doing the update) has not had that same affect. The performance drops dramatically once it hits the first commit/batch limit. Dang...

Re: Slow SQL Server updates

Anonymous — Wed, 21 Mar 2018 22:40:09 GMT

One more thing to check (I do this automatically and forgot to mention), did you untick the "updatable" field for the update key column in the field options? This can make a big difference since updating a key field (even with the same value) will incur an update performance hit since the index is technically having to be recalculated. Since it is a key value, it won't change....so you may as well untick the updatable field.

If that doesn't make a difference then I can only assume there is something funky going on with your database config. My database is not on the same machine as my Studio. In fact, it is in a different country. My Studio machine is a virtual machine with 8GB RAM (not ideal). However I am processing more data in a fraction of the time with what appear to be the same (or very similar) Talend settings.

In your last post you say "Note that this simple job is just doing an insert, not update" and show the status of the job hung. But you also show the field options with an update key specified. You only want to use the update key option if you are updating....not that it will make much of difference in an insert.

By the way, I don't believe your job is hanging at 10000 records. That is just a user interface "feature" it is displaying the results pre-emptively according to the "Batch Size" settings. Switch that off and you will see the result increment in smaller more frequent increments. Alternatively if you left your job running it would eventually get through the data (probably in a similar sort of time to if you left the job running with the "Batch Size" swicthed off).

Another slight difference between yours and mine is that you are using a primary key. When inserting a value into a primary key field the database essentially has to ensure that the value being inserted is unique, otherwise the constraint is broken. This will cause a little latency when inserting a large amount of data with existing PK data. Having said that, it shouldn't cause the issues you are seeing.

I really do not believe that Talend is doing anything weird here. Firstly because I have never seen any issues on countless customer sites. Secondly because Talend are simply using third party Jars to provide the database functionality. These are the same Jars used by many other Java applications with database interactions. All Talend does is prepare insert/update/select statements which are sent to the DB using these Jars. Even if the statements were inefficient, most RDBMS' ultimately rewrite the statements internally to make them more efficient. Any latency caused by Talend is more likely to be caused by complex in-memory transformations reuquiring a lot of memory. This is the main cause of any perfomance tuning I need to carry out, no matter what database I am using.

Re: Slow SQL Server updates

Anonymous — Thu, 22 Mar 2018 18:35:04 GMT

NOTE: I added the topic Performance impact of tMSSqlOutput Commit Every and Batch Size which shows the performance of inserts and how Commit and Batch affect the outcome. I did this testing with regard to the experience I was describing in this thread.

First, thank you very much for all the info you have shared and provided. It is really appreciated. I've got years of Datastage experience and now we're moving to Talend. Much of what I know still applies but the details are often different.

@rhallwrote:

One more thing to check (I do this automatically and forgot to mention), did you untick the "updatable" field for the update key column in the field options?

Yes, saw that list and made that same change at the time. I unchecked everything except the primary key in the "Update key" column and checked everything except the primary key in the "Updatable" column. I even unchecked everything in the "Insertable" column, just in case.

@rhallwrote:
If that doesn't make a difference then I can only assume there is something funky going on with your database config. My database is not on the same machine as my Studio. In fact, it is in a different country. My Studio machine is a virtual machine with 8GB RAM (not ideal). However I am processing more data in a fraction of the time with what appear to be the same (or very similar) Talend settings.

For this "update" job, the source and target databases are remote from me, both in Chicago. My Studio machine is a Macbook Pro 16G Ram, 2.8 GHz i7. As noted in the article from the link at top, my inserts appear to be performing fine.

@rhallwrote:
In your last post you say "Note that this simple job is just doing an insert, not update" and show the status of the job hung. But you also show the field options with an update key specified. You only want to use the update key option if you are updating....not that it will make much of difference in an insert.

Yeah, I caught that just after I posted and then made an update to that post. Oversight on my part, getting confused between the two jobs.

@rhallwrote:
By the way, I don't believe your job is hanging at 10000 records. That is just a user interface "feature" it is displaying the results pre-emptively according to the "Batch Size" settings. Switch that off and you will see the result increment in smaller more frequent increments. Alternatively if you left your job running it would eventually get through the data (probably in a similar sort of time to if you left the job running with the "Batch Size" swicthed off).

I only meant "hanging" in the sense of the visual behavior I saw. Of course, it is that the first "Batch" rows get processed very quickly (so the count goes quickly to that number), and then it hangs as it waits for that batch to be committed. As noted in the other article, "Batch" clearly has an impact on performance. Disabling it resulted in a huge drop in performance for the inserts.

@rhallwrote:
Another slight difference between yours and mine is that you are using a primary key. When inserting a value into a primary key field the database essentially has to ensure that the value being inserted is unique, otherwise the constraint is broken. This will cause a little latency when inserting a large amount of data with existing PK data. Having said that, it shouldn't cause the issues you are seeing.

I think you are referring to the fact that one of the columns in the stream has the "Key" checked? Assuming I understand that correctly, I had wondered why setting that column as the key still required me to do something else to tell the update to use it. Seems odd. In DS, setting a column as the key automatically makes it use in the update statement.

@rhallwrote:
I really do not believe that Talend is doing anything weird here. Firstly because I have never seen any issues on countless customer sites. Secondly because Talend are simply using third party Jars to provide the database functionality. These are the same Jars used by many other Java applications with database interactions. All Talend does is prepare insert/update/select statements which are sent to the DB using these Jars. Even if the statements were inefficient, most RDBMS' ultimately rewrite the statements internally to make them more efficient. Any latency caused by Talend is more likely to be caused by complex in-memory transformations reuquiring a lot of memory. This is the main cause of any perfomance tuning I need to carry out, no matter what database I am using.

Your point about using 3rd party components is an important one and certainly validates the thought that Talend isn't really at fault here, wherever else the fault might lie.

I'm just still stumped though still investigating. Doesn't make sense why this Talend job would perform so poorly compared to a Datastage job doing the same thing to the same tables in the same database.

I will report back when and if I find out anything conclusive on the update performance issue.

Again, thanks.

Re: Slow SQL Server updates

Anonymous — Thu, 22 Mar 2018 21:26:32 GMT

I'm still seeing 31 rows/sec in Talend vs. 3898 rows/sec in Datastage -- over 131 minutes vs. 1 minute.

In Datastage, I can look at the actual statement the stage is going to use:

UPDATE customer SET customer_name=?,ship_address=?,ship_postalcode=?,ship_city=?,ship_county=?,ship_state=?,ship_country=?,mktsegcode=?,crc=? WHERE cmf=?;

I'd really like to see if the SQL used by the Talend component actually includes the WHERE clause to employ the update key.

The docs indicate there is a global variable QUERY but I've not been able to successfully access. Probably have a syntax issue...

Re: Slow SQL Server updates

Anonymous — Thu, 22 Mar 2018 21:59:38 GMT

I can help you here. On your workspace you will see a a tab called "Code" in the bottom left corner. Click on that and you will see the code generated. It is a bit messy, but you can learn a lot from it. Now your update component will have a name something like ....

tMSSqlOutput_

...after the _ will be a number. You can see the name when you click on the component and then click on the component tab. Lets assume your component is numbered 1. What you want to search for when looking in the code tab is for a code like this....

String update_tMSSqlOutput_1 =

This will take you to where your update query is built as a String.

Re: Slow SQL Server updates

Anonymous — Thu, 22 Mar 2018 22:23:57 GMT

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
 */

Re: Slow SQL Server updates

Anonymous — Thu, 22 Mar 2018 22:39:38 GMT

You should be able to switch to the code screen and hit ctrl+F to bring up a find box. You can edit the code, but you can search it. The code you copied and pasted isn't all of it for the tMSSqlOutput. There is a lot more. Here is an example I quickly knocked up. This isn't configured for use, but just dumped onto a job and configured to carry out an update. You will notice there is a section of code like this....

String update_tMSSqlOutput_1 = "UPDATE ["
						+ tableName_tMSSqlOutput_1
						+ "] SET [newColumn] = ?,[newColumn1] = ?,[newColumn2] = ?,[newColumn3] = ?,[newColumn4] = ? WHERE [newColumn3] = ? AND [newColumn4] = ?";

I've highlighted in bold what you should search for in your job. I assume you are using Talend 6.something? If so this code will be there if you have your component configured for an update.

To see the whole post, download it here
OriginalPost.pdf

Re: Slow SQL Server updates

Anonymous — Fri, 23 Mar 2018 15:18:49 GMT

@rhallwrote:

You should be able to switch to the code screen and hit ctrl+F to bring up a find box.

Must be a difference between Windows and Mac. On Mac, it would be command-F to bring up search -- which works in most places but nothing happens in Talend. I tried control-F, also, but no response.

@rhallwrote:

The code you copied and pasted isn't all of it for the tMSSqlOutput.

The code I pasted is all that appears in the Code Viewer window when I have the tMSSqlOutput_2 job selected. You can see the start and stop comments from top and bottom of what displays in the viewer -- pasted again, below. Maybe another difference between Windows and Mac? Hmmm... I'm going to use SQL Server Profiler to try and look at the SQL coming in. But I'd really like to know why I don't see what you see.

/**
* [tMSSqlOutput_2 main ] start
*/

...

/**
* [tMSSqlOutput_2 main ] stop
*/

Re: Slow SQL Server updates

Anonymous — Fri, 23 Mar 2018 15:26:02 GMT

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.

Re: Slow SQL Server updates

Anonymous — Fri, 23 Mar 2018 16:07:46 GMT

Ah, yes, that is more helpful. I agree about the "Code Viewer" window -- seems pointless given the ability to view the "Code" tab of the designer.

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.

Re: Slow SQL Server updates

Anonymous — Fri, 23 Mar 2018 16:33:12 GMT

I wish I could offer more help, but without actually seeing the issue and being able to hack around, it is very hard. As my screenshots showed you, Talend is capable of much better performance than you are seeing for both inserts and updates. I'd actually be interested to try out Datastage to get some comparisons of my own (I came from a pure SQL with DB links ETL to Informatica background). If you have a Talend license, I would advise contacting Talend support and asking them for a WebEx session so that they can see exactly what you have and maybe have a bit of a look around.

Re: Slow SQL Server updates

Anonymous — Fri, 23 Mar 2018 22:42:35 GMT

I appreciate all you have contributed to this conversation. I'll end this thread for now until I (hopefully) come up with a resolution. Meanwhile, I'll close with this information:

Using SQL Profiler I traced each of the Talend and Datastage jobs and saved the trace which I include below. (I have included only a single sp_execute as they are all the same for each row except for the content being passed in.)

The only differences in their output was this:

Talend (using jDTS/JDBC):
- Forces everything to unicode in the sp_prepare and defaults parameters to maximum size.
  - From researching this on the 'net, this seems to be a standard practice with JDBC due to Java being unicode by default.
  - I don't understand why it doesn't respect the column definitions and just maxes the parameter size. Not sure that has an impact on the issue covered in this thread but it seems sloppy.
  - Note that Datastage (using OLEDB) does use the column definitions.
- Does not issue a closing sp_unprepare or reset IMPLICIT_TRANSACTIONS.
  - Personally, I think this is not a good thing though I don't know that it has any impact on the issue I am covering in this thread. Again, it just seems sloppy.
  - Note that Datastage does reset the IMPLICIT_TRANSACTIONS to off and closes with sp_unprepare

Talend

-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

SELECT @@MAX_PRECISION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET IMPLICIT_TRANSACTIONS OFF
SET QUOTED_IDENTIFIER ON
SET TEXTSIZE 2147483647

SET IMPLICIT_TRANSACTIONS ON

declare @p1 int
set @p1=1
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
select @p1

exec sp_execute 1,N'...

IF @@TRANCOUNT > 0 COMMIT TRAN

Datastage

-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

set implicit_transactions on 

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 varchar(50),@P2 varchar(240),@P3 varchar(10),@P4 varchar(60),@P5 varchar(60),@P6 char(2),@P7 char(4),@P8 varchar(2),@P9 bigint,@P10 char(8)',N'UPDATE customer SET customer_name=@P1,ship_address=@P2,ship_postalcode=@P3,ship_city=@P4,ship_county=@P5,ship_state=@P6,ship_country=@P7,mktsegcode=@P8,crc=@P9 WHERE cmf=@P10',1
select @p1

exec sp_execute 1,'...

IF @@TRANCOUNT > 0 COMMIT TRAN

set implicit_transactions off 

exec sp_unprepare 1

Re: Slow SQL Server updates

Anonymous — Sat, 24 Mar 2018 01:16:13 GMT

Not wishing to steal the last word, but I just had a thought which might help with your investigation. At the moment you are using the components in a way which lets them handle commits for you. You can take control of this and manually create a connection, perform your updates/inserts/deletes and then commit or rollback at the end. I mainly work in this way if I have to ensure a successful atomic transaction or rollback. But it *might* help with your problem. Take a look at this page which shows how to do this.....

https://help.talend.com/reader/iYcvdknuprDzYycT3WRU8w/tR5sAkr~80fPFhrYZNlNWQ

Re: Slow SQL Server updates

Anonymous — Sat, 24 Mar 2018 03:12:55 GMT

I originally had this job using a shared connection but had moved back from that to connections in the components in an effort to simplify the job and determine why it was slow. I'll keep in mind the option you have suggested here.