.net 为什么 LINQ JOIN 比使用 WHERE 链接快得多

我最近升级到VS 2010，正在使用LINQ to Dataset.我有一个用于授权的强类型数据集，它位于ASP的HttpCache中.NET网络应用程序.

所以我想知道判断用户是否有权做某事的最快方法是什么.Here是我的数据模型和其他一些信息，如果有人感兴趣的话.

我查了三种方式:

1. 直接database
2. 具有Where个条件的LINQ查询，如"JOIN"-语法
3. 使用Join语法的LINQ查询

以下是对每个函数进行1000次调用的结果:

1.Iteration:个个

1. 42841519秒.
2. 1157796925秒.
3. 2024749秒

2.Iteration:个个

1. 31954857秒
2. 8497047秒.
3. 1,5783397秒.

3.Iteration:

1. 27922143秒.
2. 97,8713267秒.
3. 18432163秒.

Average:个个

1. 数据库:34239506333秒.
2. 其中:99,5404964秒.
3. 加入:1815435秒.

为什么连接版本比WHERE语法快这么多，这使得它毫无用处，尽管作为LINQ新手，它似乎是最易读的.还是我在查询中遗漏了什么？

以下是LINQ查询，我跳过数据库:

Where:

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

Join:

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

提前谢谢你.

Edit:在对两个查询进行一些改进以获得更有意义的性能值之后，联接的优势甚至比以前大了很多倍:

Join:

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

Where:

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

1000次通话的结果(在速度更快的计算机上)

1. 加入|2.其中

1.Iteration:个个

1. 0,0713669秒.
2. 127395299秒.

2.Iteration:个个

1. 00492458秒.
2. 123885925秒.

3.Iteration:

1. 00501982秒.
2. 133474216秒.

Average:个个

1. 加入:00569367秒.
2. 其中:12,8251813秒.

JOIN的速度是原来的225倍

Conclusion:避免在哪里指定关系，并尽可能使用JOIN(通常在LINQ to DataSet和Linq-To-Objects中明确).