title: Multi threading C# application with SQL Server database calls
body: <p>I have a SQL Server database with 500,000 records in table <code>main</code>. There are also three other tables called <code>child1</code>, <code>child2</code>, and <code>child3</code>. The many to many relationships between <code>child1</code>, <code>child2</code>, <code>child3</code>, and <code>main</code> are implemented via the three relationship tables: <code>main_child1_relationship</code>, <code>main_child2_relationship</code>, and <code>main_child3_relationship</code>.  I need to read the records in <code>main</code>, update <code>main</code>, and also insert into the relationship tables new rows as well as insert new records in the child tables. The records in the child tables have uniqueness constraints, so the pseudo-code for the actual calculation (CalculateDetails) would be something like:</p>
  
  <pre><code>for each record in main
  {
     find its child1 like qualities
     for each one of its child1 qualities
     {
        find the record in child1 that matches that quality
        if found
        {
            add a record to main_child1_relationship to connect the two records
        }
        else
        {
            create a new record in child1 for the quality mentioned
            add a record to main_child1_relationship to connect the two records
        }
     }
     ...repeat the above for child2
     ...repeat the above for child3 
  }
  </code></pre>
  
  <p>This works fine as a single threaded app. But it is too slow.  The processing in C# is pretty heavy duty and takes too long.  I want to turn this into a multi-threaded app.</p>
  
  <p>What is the best way to do this?  We are using Linq to Sql.</p>
  
  <p>So far my approach has been to create a new <code>DataContext</code> object for each batch of records from <code>main</code> and use <code>ThreadPool.QueueUserWorkItem</code> to process it.  However these batches are stepping on each other's toes because one thread adds a record and then the next thread tries to add the same one and ... I am getting all kinds of interesting SQL Server dead locks.</p>
  
  <p>Here is the code:</p>
  
  <pre><code>    int skip = 0;
      List&lt;int&gt; thisBatch;
      Queue&lt;List&lt;int&gt;&gt; allBatches = new Queue&lt;List&lt;int&gt;&gt;();
      do
      {
          thisBatch = allIds
                  .Skip(skip)
                  .Take(numberOfRecordsToPullFromDBAtATime).ToList();
          allBatches.Enqueue(thisBatch);
          skip += numberOfRecordsToPullFromDBAtATime;
  
      } while (thisBatch.Count() &gt; 0);
  
      while (allBatches.Count() &gt; 0)
      {
          RRDataContext rrdc = new RRDataContext();
  
          var currentBatch = allBatches.Dequeue();
          lock (locker)  
          {
              runningTasks++;
          }
          System.Threading.ThreadPool.QueueUserWorkItem(x =&gt;
                      ProcessBatch(currentBatch, rrdc));
  
          lock (locker) 
          {
              while (runningTasks &gt; MAX_NUMBER_OF_THREADS)
              {
                   Monitor.Wait(locker);
                   UpdateGUI();
              }
          }
      }
  </code></pre>
  
  <p>And here is ProcessBatch:</p>
  
  <pre><code>    private static void ProcessBatch( 
          List&lt;int&gt; currentBatch, RRDataContext rrdc)
      {
          var topRecords = GetTopRecords(rrdc, currentBatch);
          CalculateDetails(rrdc, topRecords);
          rrdc.Dispose();
  
          lock (locker)
          {
              runningTasks--;
              Monitor.Pulse(locker);
          };
      }
  </code></pre>
  
  <p>And </p>
  
  <pre><code>    private static List&lt;Record&gt; GetTopRecords(RecipeRelationshipsDataContext rrdc, 
                                                List&lt;int&gt; thisBatch)
      {
          List&lt;Record&gt; topRecords;
  
          topRecords = rrdc.Records
                      .Where(x =&gt; thisBatch.Contains(x.Id))
                      .OrderBy(x =&gt; x.OrderByMe).ToList();
          return topRecords;
      }
  </code></pre>
  
  <p><code>CalculateDetails</code> is best explained by the pseudo-code at the top. </p>
  
  <p>I think there must be a better way to do this.  Please help.  Many thanks!</p>