Consider this pseudocode:
/*
TData tabele of data to elaborate
*/
ConcurrentQueue<Exception> exceptions = new ConcurrentQueue<Exception>();
Parallel.ForEach(TData.AsEnumerable (),row =>
{
try
{
using (WorItem p = new WorkItem(row,connectionstring))
{
p.DoWork();
}
}
catch (Exception ex)
{
exceptions.Enqueue(ex);
}
});
Inside class WorkItem.DoWork, there are some database read with custom tableadapter and one finally write of results to db. The code inside DoWork code unexpectedly fail with error “Timeout expired” or “Failed to activate rows constraints…”. The failure is random. TData table contains about 50k rows. The code is executed on machin with 48 processors, connection pool contains between 20-30 concurrent pooled connection. The failure do not depends on the row, it’s totally random. If for some row i get some strange error, i the program run for that single row, no error is generated. So what to do to correct this error?
Solution 1: disable connection pool. The problem is solved, but performance are very poor. Run time goes from 2 minutes and 30 seconds, to 16 minutes. Not good.
Solution 2: connection pool for every WorkItem object. In Ado.Net connection pool depends on connection string, so if you modify every connection for every object, connection pool is for object scope. But how modify the connection string on object basis. In connectionstring options there is App option. This option have no pratical effect, the only purpose of this option is that if you enlist the connection on the db server with sp_who, this name is shown as property of the connection. So if you modify the connection with some connectionstring=connectionstring+”App=”+hash(row), the connection pool occurs on per object basis. The drawback of this solution, is that in the destructor of the WorkItem objec (either with Finalize or Dispose) the connection pool must be cleared, with the simple call: SqlConnection.ClearPool(conn);. With this solution the excution time is more or less the same with unique connection pool. Connection pool size grows to 60-70 concurrent pooled connection.
Udate 3-5-2017
Solution 2 is not sufficient, in some heavvy load hour, the connection problem still is there. Be best solution is to open a single connection for every object, open the connection at the first use, en close the connection at Dispose of the WorkItem instance.