主要问题 有没有已知的限制、故障、配置问题或其他原因,可以解释当所有条件相同时,从C# linq运行的查询完成时间比在任何其他方式下运行的查询要慢一个数量级?
这是在linq中使用的简化查询。它是视图和表之间的非常直接的连接。
var query = (
from content in context.ApprovedContentView
where content.BucketId == 13098 && content.ContentTypeId == 5220
join item in context.ActiveContent
on content.ContentId equals item.ItemId
where
item.IsSuchAndSuch == true && item.SomeOtherProperty == 5000
select new
{
ItemId = item.ItemId,
Title = item.Title,
SubTitle = item.SubTitle,
DescriptionText = item.DescriptionText,
/* about 10 other scalar fields */
});
int count = query.Count();
var data = query.OrderByDescending(item => item.ItemId).Skip(5).Take(3);
下面是它生成的SQL(缩写/格式化)
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [SchemaX].[ApprovedContentView] AS [Extent1]
INNER JOIN [SchemaX].[ActiveContent] AS [Extent2] ON [Extent1].[ContentId] = [Extent2].[ItemId]
WHERE (13098 = [Extent1].[BucketId]) AND (5220 = [Extent1].[ContentTypeId ]) AND
(1 = [Extent2].[IsSuchAndSuch]) AND (5000 = [Extent2].[SomeOtherProperty ])
) AS [GroupBy1]
GO
SELECT TOP (3)
[Filter1].[BucketId] AS [BucketId],
[Filter1].[ItemId] AS [ItemId],
[Filter1].[Title] AS [Title],
[Filter1].[SubTitle] AS [SubTitle],
[Filter1].[DescriptionText] AS [DescriptionText],
/* other fields */
FROM ( SELECT
[Extent1].[BucketId] AS [BucketId],
[Extent2].[ItemId] AS [ItemId],
[Extent2].[Title] AS [Title],
[Extent2].[SubTitle] AS [SubTitle],
[Extent2].[DescriptionText] AS [DescriptionText],
/* other fields */
row_number() OVER (ORDER BY [Extent2].[DealId] DESC) AS [row_number]
FROM [SchemaX].[ApprovedContentView] AS [Extent1]
INNER JOIN [SchemaX].[ActiveContent] AS [Extent2] ON [Extent1].[ContentId] = [Extent2].[ItemId]
WHERE (13098 = [Extent1].[BucketId]) AND (5220 = [Extent1].[ContentTypeId ]) AND
(1 = [Extent2].[IsSuchAndSuch]) AND (5000 = [Extent2].[SomeOtherProperty ])
) AS [Filter1]
WHERE [Filter1].[row_number] > 5
ORDER BY [Filter1].[DealId] DESC
不同的场景
实际应用中 当这个Linq查询在我的C#应用程序的正常操作中执行时,我观察到在SQL Profiler中,选择计数需要3秒钟才能完成,并且奇怪的是,产生投影的查询仅需要200毫秒,而且时间是可重复的,这似乎排除了查询执行计划缓存问题。 (使用Entity Framework 5,SQL Server 2008 R2)
LINQPad中 当我通过LinqPad执行Linq语句,使用C#应用程序的dll数据上下文时,每个计数和投影都在不到四分之一秒的时间内完成(每个约224ms,总运行时间约为450ms)。
SSMS中 无论SQL的来源如何,当我复制SQL Profile报告的实际代码并将其粘贴到管理工具窗口中执行时,大约需要224ms。
数据库调整 在SSMS中,当我评估从Profiler复制的SQL的实际执行计划(无论是从代码还是从LinqPad),我发现SQL使用所有正确的索引,并且只报告索引查找--没有表扫描,没有RID查找。
那么,是什么原因呢?有人见过这样的情况吗?