title: Why is LINQ JOIN so much faster than linking with WHERE?
body: <p>I've recently upgraded to VS 2010 and am playing around with LINQ to Dataset. I have a strong typed dataset for Authorization that is in HttpCache of an ASP.NET WebApplication.</p>
  
  <p>So i wanted to know what actually is the fastest way to check if a user is authorized to do something. <a href="https://stackoverflow.com/questions/5518746/how-to-join-with-linq-to-typed-dataset-composite-key-issue">Here</a> is my datamodel and some other informations if somebody is interested.</p>
  
  <p>I have checked 3 ways:</p>
  
  <ol>
  <li>direct <strong>database</strong></li>
  <li>LINQ query with <strong>Where</strong> conditions as "Join" - Syntax</li>
  <li>LINQ query with <strong>Join</strong> - Syntax</li>
  </ol>
  
  <p>These are the results with 1000 calls on each function:</p>
  
  <p><strong>1.Iteration:</strong></p>
  
  <ol>
  <li>4,2841519 sec.</li>
  <li>115,7796925 sec.</li>
  <li>2,024749 sec.</li>
  </ol>
  
  <p><strong>2.Iteration:</strong></p>
  
  <ol>
  <li>3,1954857 sec.</li>
  <li>84,97047 sec.</li>
  <li>1,5783397 sec.</li>
  </ol>
  
  <p><strong>3.Iteration:</strong></p>
  
  <ol>
  <li>2,7922143 sec.</li>
  <li>97,8713267 sec.</li>
  <li>1,8432163 sec.</li>
  </ol>
  
  <p><strong>Average:</strong></p>
  
  <ol>
  <li>Database: 3,4239506333 sec.</li>
  <li>Where: 99,5404964 sec.</li>
  <li>Join: 1,815435 sec.</li>
  </ol>
  
  <p>Why is the Join-version so much faster than the where-syntax which makes it useless although as a LINQ newbie it seems to be the most legible. Or have i missed something in my queries?</p>
  
  <p>Here are the LINQ queries, i skip the database:</p>
  
  <p><strong>Where</strong>:   </p>
  
  <pre><code>Public Function hasAccessDS_Where(ByVal accessRule As String) As Boolean
      Dim userID As Guid = DirectCast(Membership.GetUser.ProviderUserKey, Guid)
      Dim query = From accRule In Authorization.dsAuth.aspnet_AccessRule, _
                  roleAccRule In Authorization.dsAuth.aspnet_RoleAccessRule, _
                  role In Authorization.dsAuth.aspnet_Roles, _
                  userRole In Authorization.dsAuth.aspnet_UsersInRoles _
                  Where accRule.idAccessRule = roleAccRule.fiAccessRule _
                  And roleAccRule.fiRole = role.RoleId _
                  And userRole.RoleId = role.RoleId _
                  And userRole.UserId = userID And accRule.RuleName.Contains(accessRule)
                  Select accRule.idAccessRule
      Return query.Any
  End Function
  </code></pre>
  
  <p><strong>Join:</strong></p>
  
  <pre><code>Public Function hasAccessDS_Join(ByVal accessRule As String) As Boolean
      Dim userID As Guid = DirectCast(Membership.GetUser.ProviderUserKey, Guid)
      Dim query = From accRule In Authorization.dsAuth.aspnet_AccessRule _
                  Join roleAccRule In Authorization.dsAuth.aspnet_RoleAccessRule _
                  On accRule.idAccessRule Equals roleAccRule.fiAccessRule _
                  Join role In Authorization.dsAuth.aspnet_Roles _
                  On role.RoleId Equals roleAccRule.fiRole _
                  Join userRole In Authorization.dsAuth.aspnet_UsersInRoles _
                  On userRole.RoleId Equals role.RoleId _
                  Where userRole.UserId = userID And accRule.RuleName.Contains(accessRule)
                  Select accRule.idAccessRule
      Return query.Any
  End Function
  </code></pre>
  
  <p>Thank you in advance.</p>
  
  <hr>
  
  <p><strong>Edit</strong>: after some improvements on both queries to get more meaningful perfomance-values, the advantage of the JOIN is even many times greater than before:</p>
  
  <p><strong>Join</strong>:</p>
  
  <pre><code>Public Overloads Shared Function hasAccessDS_Join(ByVal userID As Guid, ByVal idAccessRule As Int32) As Boolean
      Dim query = From accRule In Authorization.dsAuth.aspnet_AccessRule _
                     Join roleAccRule In Authorization.dsAuth.aspnet_RoleAccessRule _
                     On accRule.idAccessRule Equals roleAccRule.fiAccessRule _
                     Join role In Authorization.dsAuth.aspnet_Roles _
                     On role.RoleId Equals roleAccRule.fiRole _
                     Join userRole In Authorization.dsAuth.aspnet_UsersInRoles _
                     On userRole.RoleId Equals role.RoleId _
                     Where accRule.idAccessRule = idAccessRule And userRole.UserId = userID
               Select role.RoleId
      Return query.Any
  End Function
  </code></pre>
  
  <p><strong>Where</strong>:</p>
  
  <pre><code>Public Overloads Shared Function hasAccessDS_Where(ByVal userID As Guid, ByVal idAccessRule As Int32) As Boolean
      Dim query = From accRule In Authorization.dsAuth.aspnet_AccessRule, _
             roleAccRule In Authorization.dsAuth.aspnet_RoleAccessRule, _
             role In Authorization.dsAuth.aspnet_Roles, _
             userRole In Authorization.dsAuth.aspnet_UsersInRoles _
             Where accRule.idAccessRule = roleAccRule.fiAccessRule _
             And roleAccRule.fiRole = role.RoleId _
             And userRole.RoleId = role.RoleId _
             And accRule.idAccessRule = idAccessRule And userRole.UserId = userID
             Select role.RoleId
      Return query.Any
  End Function
  </code></pre>
  
  <h2>Result for 1000 calls (on a faster computer)</h2>
  
  <ol>
  <li>Join | 2. Where</li>
  </ol>
  
  <p><strong>1.Iteration:</strong></p>
  
  <ol>
  <li>0,0713669 sec.</li>
  <li>12,7395299 sec.</li>
  </ol>
  
  <p><strong>2.Iteration:</strong></p>
  
  <ol>
  <li>0,0492458 sec.</li>
  <li>12,3885925 sec.</li>
  </ol>
  
  <p><strong>3.Iteration:</strong></p>
  
  <ol>
  <li>0,0501982 sec.</li>
  <li>13,3474216 sec.</li>
  </ol>
  
  <p><strong>Average:</strong></p>
  
  <ol>
  <li>Join:  0,0569367 sec.</li>
  <li>Where: 12,8251813 sec.</li>
  </ol>
  
  <hr>
  
  <p>Join is 225 times faster </p>
  
  <p><strong>Conclusion:</strong> avoid WHERE to specify relations and use JOIN whenever possible(definitely in <a href="http://msdn.microsoft.com/en-us/library/bb386977.aspx" rel="noreferrer">LINQ to DataSet</a> and <code>Linq-To-Objects</code> in general).</p>