为什么这个LINQ比它的SQL同行慢得多? [英] Why is this LINQ so much slower than its SQL counterpart?
问题描述
我有下面的 LINQ to SQL 方法，该方法花费大量时间才能执行，但它的SQL对应方法非常简单且快速.我在LINQ部分做错了吗?我只是想返回一些数据以只读方式显示在数据网格中.
I have the below LINQ to SQL method that takes an inordinate amount of time to execute yet its SQL counterpart is quite simple and fast. Am I doing something wrong in the LINQ part? I am just trying to return some data to display, read-only, in a Data Grid.
我知道，如果该工具不合适，则不要使用它，因此我可以在此处进行SQL调用，但我想了解为什么会有这种区别.
I understand that if the tool doesn't fit don't use it and as such I could just do a SQL call here but I'd like to understand why there is such a difference.
下面是LINQ，然后是它转储的SQL.
Below is the LINQ and then the SQL that it dumps.
public static DataTable GetEnrolledMembers(Guid workerID)
{
using (var DB = CmoDataContext.Create())
{
var AllEnrollees = from enrollment in DB.tblCMOEnrollments
where enrollment.CMOSocialWorkerID == workerID || enrollment.CMONurseID == workerID
join supportWorker in DB.tblSupportWorkers on enrollment.EconomicSupportWorkerID
equals supportWorker.SupportWorkerID into workerGroup
from worker in workerGroup.DefaultIfEmpty()
select
new
{
enrollment.ClientID,
enrollment.CMONurseID,
enrollment.CMOSocialWorkerID,
enrollment.EnrollmentDate,
enrollment.DisenrollmentDate,
ESFirstName = worker.FirstName,
ESLastName = worker.LastName,
ESPhone = worker.Phone
};
var result = from enrollee in AllEnrollees.AsEnumerable()
where (enrollee.DisenrollmentDate == null || enrollee.DisenrollmentDate > DateTime.Now)
let lastName = BLLConnect.MemberLastName(enrollee.ClientID)
let firstName = BLLConnect.MemberFirstName(enrollee.ClientID)
orderby enrollee.DisenrollmentDate ascending , lastName ascending
select new
{
enrollee.ClientID,
LastName = lastName,
FirstName = firstName,
NurseName = BLLAspnetdb.NurseName(enrollee.CMONurseID),
SocialWorkerName = BLLAspnetdb.SocialWorkerName(enrollee.CMOSocialWorkerID),
enrollee.EnrollmentDate,
enrollee.DisenrollmentDate,
ESWorkerName = enrollee.ESFirstName + " " + enrollee.ESLastName,
enrollee.ESPhone
};
DB.Log = Console.Out;
return result.CopyLinqToDataTable();
}
}
和SQL:
SELECT [t0].[ClientID], [t0].[CMONurseID], [t0].[CMOSocialWorkerID], [t0].[EnrollmentDate], [t0].[DisenrollmentDate], [t1].[FirstName] AS [ESFirstName], [t1].[LastName] AS [ESLastName], [t1].[Phone] AS [ESPhone]
FROM [dbo].[tblCMOEnrollment] AS [t0]
LEFT OUTER JOIN [dbo].[tblSupportWorker] AS [t1] ON [t0].[EconomicSupportWorkerID] = ([t1].[SupportWorkerID])
WHERE ([t0].[CMOSocialWorkerID] = @p0) OR ([t0].[CMONurseID] = @p1)
-- @p0: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [060632ee-be09-4057-b17b-2d0190d0ff74]
-- @p1: Input UniqueIdentifier (Size = 0; Prec = 0; Scale = 0) [060632ee-be09-4057-b17b-2d0190d0ff74]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.4926
推荐答案
首先，我不认为您正在将苹果与苹果进行比较，因为您拥有大量的BllConnect.在第二个linq查询的选择周围进行了一些调用.此外，您还必须按照其他答案中所述取出AsEnumerable.
First, I don't think you are comparing apples to apples, you have plenty of BllConnect.Something calls around the select of the second linq query. Additionally you have to take out the AsEnumerable as mentioned in the other answer.
请考虑以下内容(假设您在db和/或linq2sql设计器中添加了相应的关系):
Consider the below (given you add the corresponding relations in the db and/or linq2sql designer):
public static DataTable GetEnrolledMembers(Guid workerID)
{
using (var DB = CmoDataContext.Create())
{
var AllEnrollees = from enrollment in DB.tblCMOEnrollments
where enrollment.CMOSocialWorkerID == workerID
|| enrollment.CMONurseID == workerID
let w = enrollment.EconomicSupporterWorker
select new
{
enrollment.ClientID,
enrollment.CMONurseID,
enrollment.CMOSocialWorkerID,
enrollment.EnrollmentDate,
enrollment.DisenrollmentDate,
ESFirstName = w != null ? w.FirstName : null,
ESLastName = w != null ? w.LastName : null,
ESPhone = w != null ? w.Phone : null
};
var filteredEnrollees = AllEnrollees
.Where(e=> e.DisenrollmentDate == null || e.DisenrollmentDate > DateTime.Now);
//benchmark how much it delays if you do a .ToList until here
// ... when comparing the sql, run it on the same remote computer you are running this,
// so you take into account the time to transfer the data.
filteredEnrollees = filteredEnrollees
.OrderBy(e=> e.DisenrollmentData) // benchmark here again
.ThenBy(e=> BLLConnect.MemberLastName(enrollee.ClientID)); // prob. causing issues
var result = // do what you already had, but against filteredEnrollees and benchmark
// prob. issues with BllConnect.* and BllAspnetdb.* being called for each record /
// ... doesn't happen in sql side
DB.Log = Console.Out;
return result.CopyLinqToDataTable();
}
}
这篇关于为什么这个LINQ比它的SQL同行慢得多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
