title: Is parallelism usefull with a lock for datatable write transactions
body: <p>Will parallelism help with performance for a locked object, should it be run single threaded, or is there another technique?</p>
  
  <p>I noticed that when accessing a dataset and adding rows from multiple threads exceptions were thrown.  Therefore I created a "thread-safe" version to add rows by locking the table prior to updating the row.  This implementation works but is appears slow with many transactions.</p>
  
  <pre><code>public partial class HaMmeRffl
  {
      public partial class PlayerStatsDataTable
      {
          public void AddPlayerStatsRow(int PlayerID, int Year, int StatEnum, int Value, DateTime Timestamp)
          {
              lock (TeamMemberData.Dataset.PlayerStats)
              {
                  HaMmeRffl.PlayerStatsRow testrow = TeamMemberData.Dataset.PlayerStats.FindByPlayerIDYearStatEnum(PlayerID, Year, StatEnum);
                  if (testrow == null)
                  {
                      HaMmeRffl.PlayerStatsRow newRow = TeamMemberData.Dataset.PlayerStats.NewPlayerStatsRow();
                      newRow.PlayerID = PlayerID;
                      newRow.Year = Year;
                      newRow.StatEnum = StatEnum;
                      newRow.Value = Value;
                      newRow.Timestamp = Timestamp;
                      TeamMemberData.Dataset.PlayerStats.AddPlayerStatsRow(newRow);
                  }
                  else
                  {
                      testrow.Value = Value;
                      testrow.Timestamp = Timestamp;
                  }
  
              }
          }
      }
  }
  </code></pre>
  
  <p>Now I can call this safely from multiple threads, but does it actually buy me anything? Can I do this differently for better performance.  For instance is there any way to use <code>System.Collections.Concurrent</code> namespace to optimize performance or any other methods?</p>
  
  <p>In addition, I update the underlying database after the entire dataset is updated and that takes a very long time.  Would that be considered an I/O operation and be worth using parallel processing by updating it after each row is updated in the dataset (or some number of rows).</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I wrote some code to test concurrent vs sequential processing which shows it takes about 30% longer to do concurrent processing and I should use sequential processing here. <strong>I assume this is because the lock on the database is causing the overhead on the <code>ConcurrentQueue</code> to be more costly than the gains from parallel processing.  Is this conclusion correct and is there anything that I can do to speed up the processing</strong>, or am I stuck as for a <code>Datatable</code> "You must synchronize any write operations".</p>
  
  <p>Here is my test code which might not be scientifically correct.  Here is the timer and calls between them.</p>
  
  <pre><code>            dbTimer.Restart();
              Queue&lt;HaMmeRffl.PlayersRow.PlayerValue&gt; addPlayerRow = InsertToPlayerQ(addUpdatePlayers);
              Queue&lt;HaMmeRffl.PlayerStatsRow.PlayerStatValue&gt; addPlayerStatRow                     = InsertToPlayerStatQ(addUpdatePlayers);
              UpdatePlayerStatsInDB(addPlayerRow, addPlayerStatRow);
              dbTimer.Stop();
              System.Diagnostics.Debug.Print("Writing to the dataset took {0} seconds single threaded", dbTimer.Elapsed.TotalSeconds);
  
              dbTimer.Restart();
              ConcurrentQueue&lt;HaMmeRffl.PlayersRow.PlayerValue&gt; addPlayerRows                    = InsertToPlayerQueue(addUpdatePlayers);
              ConcurrentQueue&lt;HaMmeRffl.PlayerStatsRow.PlayerStatValue&gt; addPlayerStatRows                    = InsertToPlayerStatQueue(addUpdatePlayers);
              UpdatePlayerStatsInDB(addPlayerRows, addPlayerStatRows);
              dbTimer.Stop();
              System.Diagnostics.Debug.Print("Writing to the dataset took {0} seconds concurrently", dbTimer.Elapsed.TotalSeconds);
  </code></pre>
  
  <p>In both examples I add to the <code>Queue</code> and <code>ConcurrentQueue</code> in an identical manner single threaded.  The only difference is the insertion into the datatable.  The single-threaded approach inserts as follows:</p>
  
  <pre><code>    private static void UpdatePlayerStatsInDB(Queue&lt;HaMmeRffl.PlayersRow.PlayerValue&gt; addPlayerRows, Queue&lt;HaMmeRffl.PlayerStatsRow.PlayerStatValue&gt; addPlayerStatRows)
      {
          try
          {
              HaMmeRffl.PlayersRow.PlayerValue row;
              while (addPlayerRows.Count &gt; 0)
              {
                  row = addPlayerRows.Dequeue();
                  TeamMemberData.Dataset.Players.AddPlayersRow(
                      row.PlayerID, row.Name, row.PosEnum, row.DepthEnum,
                      row.TeamID, row.RosterTimestamp, row.DepthTimestamp,
                      row.Active, row.NewsUpdate);
              }
          }
          catch (Exception)
          {
              TeamMemberData.Dataset.Players.RejectChanges();
          }
  
          try
          {
              HaMmeRffl.PlayerStatsRow.PlayerStatValue row;
              while (addPlayerStatRows.Count &gt; 0)
              {
                  row = addPlayerStatRows.Dequeue();
                  TeamMemberData.Dataset.PlayerStats.AddUpdatePlayerStatsRow(
                      row.PlayerID, row.Year, row.StatEnum, row.Value, row.Timestamp);
              }
          }
          catch (Exception)
          {
              TeamMemberData.Dataset.PlayerStats.RejectChanges();
          }
  
          TeamMemberData.Dataset.Players.AcceptChanges();
          TeamMemberData.Dataset.PlayerStats.AcceptChanges();
  
      }
  </code></pre>
  
  <p>The concurrent adds as follows</p>
  
  <pre><code>    private static void UpdatePlayerStatsInDB(ConcurrentQueue&lt;HaMmeRffl.PlayersRow.PlayerValue&gt; addPlayerRows, ConcurrentQueue&lt;HaMmeRffl.PlayerStatsRow.PlayerStatValue&gt; addPlayerStatRows)
      {
          Action actionPlayer = () =&gt;
          {
              HaMmeRffl.PlayersRow.PlayerValue row;
              while (addPlayerRows.TryDequeue(out row))
              {
                  TeamMemberData.Dataset.Players.AddPlayersRow(
                      row.PlayerID, row.Name, row.PosEnum, row.DepthEnum,
                      row.TeamID, row.RosterTimestamp, row.DepthTimestamp,
                      row.Active, row.NewsUpdate);
              }
          };
  
          Action actionPlayerStat = () =&gt;
          {
              HaMmeRffl.PlayerStatsRow.PlayerStatValue row;
              while (addPlayerStatRows.TryDequeue(out row))
              {
                  TeamMemberData.Dataset.PlayerStats.AddUpdatePlayerStatsRow(
                      row.PlayerID, row.Year, row.StatEnum, row.Value, row.Timestamp);
              }
          };
  
          Action[] actions = new Action[Environment.ProcessorCount * 2];
          for (int i = 0; i &lt; Environment.ProcessorCount; i++)
          {
              actions[i * 2] = actionPlayer;
              actions[i * 2 + 1] = actionPlayerStat;
          }
  
          try
          {
              // Start ProcessorCount concurrent consuming actions.
              Parallel.Invoke(actions);
          }
          catch (Exception)
          {
              TeamMemberData.Dataset.Players.RejectChanges();
              TeamMemberData.Dataset.PlayerStats.RejectChanges();
          }
  
          TeamMemberData.Dataset.Players.AcceptChanges();
          TeamMemberData.Dataset.PlayerStats.AcceptChanges();
  
      }
  </code></pre>
  
  <p>The difference in time is 4.6 seconds for the single-threaded and 6.1 for the parallel.Invoke.</p>