LinqPad getting stuck after updating a few thousand records.

ravp

Yesterday, I ran this query:

var serverQ = from a in Import2s
	          select a;
				  
foreach (var a in serverQ)
{
	var col = a.TempCol;
	var split = col.Split('\\');
	foreach (var s in split)
	{
		if (s.Contains(','))
		{
			a.TempCol = s;
			 SubmitChanges();
		}
	}
}

It ran up until row ~4700 then stopped generating code. I ran it again and left it overnight at work, and that time it ran until record ~37000. It is stuck on declaring the SQL variables for some reason, but I can see the query has been running for 16 hours without any change.

Here is a screenshot:



Any help on why my query is randomly stopping yet still saying "Executing" after only updating a few thousand records would be greatly appreciated.

PS: One more thing I tried was moving the "SubmitChanges" method outside the outer foreach loop but to no success.

JoeAlbahari

For performance reasons, there's a limit to how much text can go into the SQL window. It's possible that you've hit the limit, but the query will continue running. Also, you're gradually putting the entire table into memory, because the objects are change-tracked. This is probably not the problem, though, because you're not getting an OutOfMemoryException.

If it possible to do this as a single SQL update, you'd be better off doing so because you'd avoid the round-tripping and memory consumption.

ravp

If it possible to do this as a single SQL update, you'd be better off doing so because you'd avoid the round-tripping and memory consumption.

I tried moving the "SubmitChanges" method outside the outer foreach loop but to no success.

The query just terminated with the error "InvalidOperationException: This SqlTransaction has completed; it is no longer usable."

I also see that the query ran for 20 hours but only performed ~30k updates as per the screenshot in my OP.

JoeAlbahari

What I mean by a single SQL update is to write the query entirely in SQL, if it can be done. This will mean a single round-trip to the server.

ravp

Not sure if it can be done but I'd like to be able to do this in LinqPad.

ravp

bump

sgmoore

I suspect you are hitting the problem where SubmitChanges gets progressively slower each time you call it. You might be better to avoid it altogether and replace it with something like

foreach(var a in serverQ) 
	{
		var newColumnValue = a.TempValue;

		var col = a.TempCol;
		var split = col.Split('\\');
		foreach (var s in split)
		{
			if (s.Contains(',')
				newColumnValue = s;
		}
		if (a.TempCol != newColumnValue)
		{
			a.TempCol = newColumnValue;
			this.ExecuteCommand("Update Import2 Set TempCol={0} where DocID={1}", newColumnValue, a.DocID);
	 	}
	}

I think this is functionally the same, but obviously you need to check/test it.


An alternative would be to retrieve your data in bit-size chunks and create a new Dataconnection for each chunk. This would be mean that the changeset would not keep growing and growing, eg

int lastID = 0
int count = 0;
do
{
	var dc = new UserQuery();

	var serverQ = (from a in dc.Import2s where a.DocID < lastID orderby a.DocID select a).Take(1000).ToList();
	
	count = serverQ.Count() ;
	
	foreach (var a in serverQ)
	{
		var col = a.TempCol;
		var split = col.Split('\\');
		foreach (var s in split)
		{
			if (s.Contains(','))
			{
				a.TempCol = s;
			}
		}
		lastID = a.DocID;
	}
	dc.SubmitChanges();
	lastID.Dump();
}
while (count != 0)

ravp

Thank you sgmoore. Your first suggestion fixed my problem brilliantly!