title: How to PLINQ an existing LINQ query with Joins?
body: <p>I'm using LINQ to compare two DataSets with each other to create new rows and update existing. I've noticed that the complete comparison lasts ~1,5 hours and only one of the two cores is busy(Task-Manager is 50-52% CPU Usage). I must admit that I'm completely new to parallel LINQ, but I assume that it could increase performance significantly.</p>
  
  <p>So my question is, how and what should I parallelize? </p>
  
  <p>These are the original queries(reduced to the essentials):</p>
  
  <pre><code>'check for new data
  Dim srcUnique = From row In src.Email_Total
                  Select Ticket_ID = row.ticket_id, Interaction = row.interaction, ModifiedAt = row.modified_time
  
  Dim destUnique = From row In dest.ContactDetail
                   Where row.ContactRow.fiContactType = emailContactType.idContactType
                   Select row.ContactRow.Ticket_ID, row.Interaction, row.ModifiedAt
  
  'get all emails(contactdetails) that are in source but not in destination
  Dim diffRows = srcUnique.Except(destUnique).ToList
  
  'get all new emails(according to ticket_id) for calculating contact columns
  Dim newRowsTickets = (From row In src.Email_Total
                       Join d In diffRows
                       On row.ticket_id Equals d.Ticket_ID _
                       And row.interaction Equals d.Interaction _
                       And row.modified_time Equals d.ModifiedAt
                       Group row By Ticket_ID = row.ticket_id Into NewTicketRows = Group).ToList
  
  For Each ticket In newRowsTickets
       Dim contact = dest.Contact.FindByTicket_IDfiContactType(ticket.Ticket_ID, emailContactType.idContactType)
       If contact Is Nothing Then
            ' Create new Contact with many sub-queries on this ticket(omitted) ****'
            Dim newContact = Me.dest.Contact.NewContactRow
            dest.Contact.AddContactRow(newContact)
            contact = newContact
       Else
            ' Update Contact with many sub-queries on this ticket(omitted) '
       End If
       daContact.Update(dest.Contact)
  
       ' Add new ContactDetail-Rows from this Ticket(this is the counterpart of the src.Email_Total-Rows, details omitted) '
       For Each newRow In ticket.NewTicketRows
           Dim newContactDetail = dest.ContactDetail.NewContactDetailRow
           newContactDetail.ContactRow = contact
           dest.ContactDetail.AddContactDetailRow(newContactDetail)
       Next
       daContactDetails.Update(dest.ContactDetail)
  Next
  </code></pre>
  
  <p><strong>Note</strong>: <code>daContact</code> and <code>daContactDetails</code> are <code>SqlDataAdapters</code>, <code>source</code> and <code>dest</code> are <code>DataSets</code> and <code>Contact</code> and <code>ContactDetail</code> are <code>DataTables</code>, where every ContactDetail belongs to a Contact.</p>
  
  <p>Even if not both cores would use 100% CPU, I assume that it would increase performance significantly if I would parallelize the queries, because the second core is nearly idle. The <code>for each</code> might also be a good place to optimize since the tickets are not related to each other. So I assume that I could loop with multiple threads and create/update records parallel. But how to do it with PLINQ? </p>
  
  <p><sup><strong>Side Note</strong>: As I've mentioned in the comments, performance is not a key factor for me so far, since the server's only purpose is to synchronize the MySQL Database(on another server) with a MS SQL-Server(on the same server as this Windows-Service). It acts as a source for reports that are generated by another service. But these reports are only generated once a day. But apart from that I was interested in learning PLINQ because I thought that this could be an excellent exercise.
  It takes the mentioned 1,5h only if destination DB is empty and all records must be created. If both databases are nearly in sync, this method takes only ~1 minute yet. In future performance will become more important since <em>email</em> is only one of several contact-types(chat+calls will exceed 1mil.records). I think that I'll anyway need some kind of (LINQ) Data-Paging then.
  </sup></p>
  
  <p>If something is unclear I'll update my answer accordingly. Thanks in advance.</p>
  
  <hr>
  
  <p><strong>Edit</strong>: Here is the result of my investigations and attempts:</p>
  
  <p>Question: How to "PLINQ" an existing LINQ query with joins?</p>
  
  <p><a href="http://msdn.microsoft.com/en-us/magazine/cc163329.aspx#S2" rel="noreferrer">Answer</a>: Note that some LINQ operators are binary—they take two IEnumerables as input. Join is a perfect example of such an operator. In these cases, the type of the left-most data source determines whether LINQ or PLINQ is used. Thus you need only call AsParallel on the first data source for your query to run in parallel:</p>
  
  <pre><code>IEnumerable&lt;T&gt; leftData = ..., rightData = ...;
  var q = from x in leftData.AsParallel()
          join y in rightData on x.a == y.b
          select f(x, y);
  </code></pre>
  
  <p>But if I change my query in the following way(note the <code>AsParallel</code>):</p>
  
  <pre><code>Dim newRowsTickets = (From row In src.Email_Total.AsParallel()
                                          Join d In diffRows
                                          On row.ticket_id Equals d.Ticket_ID _
                                          And row.interaction Equals d.Interaction _
                                          And row.modified_time Equals d.ModifiedAt
                                      Group row By Ticket_ID = row.ticket_id Into NewTicketRows = Group).ToList
  </code></pre>
  
  <p>The compiler will complain that I need to add <code>AsParallel</code> to the right datasource as well. So this seem to be a VB.NET issue or a lack of documentation(article is from 2007). I assume the latter because the(apart from that recommendable) article also says that you need to add <code>System.Concurrency.dll</code> manually but actually it is part of .NET 4.0 Framework and in Namespace <code>Sytem.Threading.Tasks</code>.</p>
  
  <p>I realized that I won't profit from a parallelized <code>Except</code> since the query is fast enough in sequential mode(even with nearly the same number of rows in both collection which results in the maximum number of comparisons, I got the result in less than 30 seconds). But I will add it for the sake of completeness later.</p>
  
  <p>So I decided to parallelize the <code>for-each</code> what is as easy as with LINQ-Queries, you simply need to add <code>AsParallel()</code> at the end.
  But I realized that I need to force the parallelism with <code>WithExecutionMode(ParallelExecutionMode.ForceParallelism)</code>, otherwise .NET decides to use only one core for this loop. I also wanted to tell .NET that I wish to use as many Threads as possible but not more than 8: <code>WithDegreeOfParallelism(8).</code></p>
  
  <p>Now both cores are working at the same time, but the CPU usage stays on 54%.</p>
  
  <p>So this is the PLINQ version so far:</p>
  
  <pre><code>Dim diffRows = srcUnique.AsParallel.Except(destUnique.AsParallel).ToList
  
  Dim newRowsTickets = (From row In src.Email_Total.AsParallel()
                          Join d In diffRows.AsParallel()
                          On row.ticket_id Equals d.Ticket_ID _
                          And row.interaction Equals d.Interaction _
                          And row.modified_time Equals d.ModifiedAt
                      Group row By Ticket_ID = row.ticket_id Into NewTicketRows = Group).ToList
  
  For Each ticket In newRowsTickets.
                      AsParallel().
                        WithDegreeOfParallelism(8).
                         WithExecutionMode(ParallelExecutionMode.ForceParallelism)
      '  blah,blah ...  '
  
      'add new ContactDetails for this Ticket(only new rows)
      For Each newRow In ticket.NewTicketRows.
                                  AsParallel().
                                      WithExecutionMode(ParallelExecutionMode.Default)
          ' blah,blah ... '
      Next
      daContactDetails.Update(dest.ContactDetail)
  Next
  </code></pre>
  
  <p>Unfortunately I don't see any performance benefits from using <code>AsParallel</code> in comparison with sequential mode:</p>
  
  <p>The <code>for each</code> with <code>AsParallel</code>(hh:mm:ss.mm):</p>
  
  <pre><code>09/29/2011 18:54:36: Contacts/ContactDetails created or modified. Duration: 01:21:34.40
  </code></pre>
  
  <p>And without:</p>
  
  <pre><code>09/29/2011 16:02:55: Contacts/ContactDetails created or modified. Duration: 01:21:24.50
  </code></pre>
  
  <p>Can somebody explain me this result? Is the database' write access in the <code>for each</code> responsible for the similar time? </p>
  
  <hr>
  
  <p>Following are recommendable readings:</p>
  
  <ul>
  <li><a href="http://msdn.microsoft.com/en-us/magazine/cc163329.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/magazine/cc163329.aspx</a> (not up-to-date)
  
  <ul>
  <li><a href="http://blogs.msdn.com/b/pfxteam/archive/2007/11/29/6558508.aspx" rel="noreferrer">List of changes since above article</a></li>
  </ul></li>
  <li><a href="http://msdn.microsoft.com/en-us/concurrency/ee851578.aspx" rel="noreferrer">Overview of videos/articles according this topic</a></li>
  <li><a href="http://download.microsoft.com/download/B/C/F/BCFD4868-1354-45E3-B71B-B851CD78733D/WhenToUseParallelForEachOrPLINQ.pdf" rel="noreferrer">PDF: When To Use ParallelForEach Or PLINQ</a></li>
  <li><a href="http://msdn.microsoft.com/en-us/library/dd997399.aspx" rel="noreferrer">When PLINQ will speedup performance</a></li>
  </ul>