Solved

SharePoint 2013 update external list with CSOM timeout

Posted on 2015-01-12
14
573 Views
Last Modified: 2015-02-02
I am trying to update records in an external list (In MSSQL) using the client side object model. The function times out after about 90 seconds with only 20 records. What's weird is I can insert 100 records in a few seconds without any problems but when it comes to updating it times out. Below is the code I'm using. Any help would be greatly appreciated...

function updateListItems() {
	myShowWaitScreenWithNoClose();
    
    	var itemArray = [];
    	var clientContext = SP.ClientContext.get_current();
    	var oList = clientContext.get_web().get_lists().getByTitle('wst_Inventory');
        
        var mDate = new Date();
		var user = sp_GetUser();
    	var ID;
    	var oListItem;
    	
    	$(".c_ShortNo").each(function(i) {
    	    ID = parseInt($(this).val());
    		//alert(ID);
    	    oListItem = oList.getItemById(ID);  
    	    oListItem.set_item('Active', 'FALSE');
            oListItem.set_item('Modified', mDate);
            oListItem.set_item('ModifiedBy', user);  
    	    oListItem.update();
    	    itemArray[i] = oListItem;
    	    clientContext.load(itemArray[i]);
    	});
    	    
    	clientContext.executeQueryAsync(onQuerySucceeded, onQueryFailed);
	}

Open in new window

0
Comment
Question by:snieves
  • 6
  • 5
14 Comments
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40548580
How many records do you have in that table?
And the table has a primary key?
0
 
LVL 1

Author Comment

by:snieves
ID: 40548810
There are currently 13747 rows in the table and yes it does have a primary key (ID). I can push and pull from the table just fine using SPServices and CSOM but updating times out when updating more than 5 records.
0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40548845
I can only help you from the database side but will need to know the UPDATE statement that is being executed.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:snieves
ID: 40550109
Not sure really. The table is in SQL and is imported as an external content type into a SharePoint 2013 Foundation site. Where can I find the update statement?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40550831
I'm not a SharePoint expert. I wouldn't be surprised if the update statement is building in the background by SharePoint. If so, the only way to know the update command is to run a SQL Profiler in the database side to capture the command sent by SharePoint.
0
 
LVL 1

Author Comment

by:snieves
ID: 40551143
Vitor, is it possible that my MSSQL table is locking? I apologize I am not as well versed with MSSQL as I should be but is it possible it's timing out because of table locking?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40551189
It must possible that locking are occurring in the database side. That's why I asked for the Update command.
If you can run a SQL Profiler would help.
0
 
LVL 1

Author Comment

by:snieves
ID: 40551425
Again please excuse my ignorance. How should I configure the profiler to make sure I'm looking at the update commands?
0
 
LVL 1

Author Comment

by:snieves
ID: 40557796
Here is the SQL command that is running for each update call:

exec sp_executesql N'UPDATE [dbo].[wst_Inventory] SET [WID] = @WID , [Descriptor] = @Descriptor , [Weight] = @Weight , [Tare] = @Tare , [Net] = @Net , [ContainerType] = @ContainerType , [ContainerSize] = @ContainerSize , [FID] = @FID , [LID] = @LID , [OneYear] = @OneYear , [Comments] = @Comments , [Active] = @Active , [Created] = @Created , [CreatedBy] = @CreatedBy , [Modified] = @Modified , [ModifiedBy] = @ModifiedBy , [FormType] = @FormType , [FormID] = @FormID , [OldInv] = @OldInv WHERE [ID] = @ID',N'@ID int,@WID int,@Descriptor nvarchar(13),@Weight int,@Tare int,@Net int,@ContainerType nvarchar(2),@ContainerSize nvarchar(2),@FID int,@LID int,@OneYear datetime,@Comments nvarchar(5),@Active bit,@Created datetime,@CreatedBy nvarchar(12),@Modified datetime,@ModifiedBy nvarchar(12),@FormType nvarchar(9),@FormID int,@OldInv int',@ID=5001,@WID=8,@Descriptor=N'For Recycling',@Weight=544,@Tare=20,@Net=524,@ContainerType=N'DM',@ContainerSize=N'55',@FID=2,@LID=7,@OneYear='2015-09-16 00:00:00',@Comments=N'Test1',@Active=1,@Created='2015-01-05 00:00:00',@CreatedBy=N'Shaun Nieves',@Modified='2015-01-05 00:00:00',@ModifiedBy=N'Shaun Nieves',@FormType=N'Container',@FormID=0,@OldInv=357875

Open in new window

0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40559264
Hi snieves.

Here is a link to show you how to create and run a SQL Profiler: http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step.

You can also find tutorials in the internet by searching for "SQL Profiler tutorial" or "SQL Profiler step by step".

Try to capture queries and locks and then return here with your findings.
Good luck.
0
 
LVL 1

Author Closing Comment

by:snieves
ID: 40584415
I ended up using a stored procedure in the external content type and that got around the timeout issue. Thanks for pointing me in the right direction!!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question