使用 count() 优化 Linq 查询

Question

使用 count() 优化 Linq 查询

3

我有以下查询语句，它可以正常工作

var myList = (from p in db.full


                      group p by p.object into g
                      orderby g.Count() descending
                      select new StringIntType
                      {
                          str = g.Key,

                          nbr = g.Count()
                      }).Take(50).ToList();

问题在于我正在使用count()，它会被翻译为count(*)，导致速度有些慢。
我需要知道是否有一种方法可以使用count(object)。以下是我在SQL Server Profiler中得到的内容。

  exec sp_executesql N'SELECT TOP (50) 
  [Project1].[C2] AS [C1], 
  [Project1].[object] AS [object], 
  [Project1].[C1] AS [C2]
  FROM ( SELECT 
      [GroupBy1].[A1] AS [C1], 
    [GroupBy1].[K1] AS [object], 
    1 AS [C2]
    FROM ( SELECT 
        [Extent1].[object], AS [K1], 
        COUNT(1) AS [A1]
        FROM (SELECT 
[full].[mc_host_class] AS [mc_host_class], 
[full].[event_handle] AS [event_handle], 
[full].[mc_host_address] AS [mc_host_address], 
[full].[mc_object_class] AS [mc_object_class], 
[full].[mc_object] AS [mc_object], 
[full].[mc_incident_time] AS [mc_incident_time], 
[full].[date_reception] AS [date_reception], 
[full].[status] AS [status], 
[full].[mc_owner] AS [mc_owner], 
[full].[msg] AS [msg], 
[full].[duration] AS [duration], 
[full].[repeat_count] AS [repeat_count], 
[full].[mc_date_modification] AS [mc_date_modification], 
[full].[event_class] AS [event_class], 
[full].[bycn_ticket_remedy] AS [bycn_ticket_remedy], 
[full].[mc_host] AS [mc_host], 
[full].[acknowledge_by] AS [acknowledge_by], 
[full].[acknowledge_by_time] AS [acknowledge_by_time], 
[full].[assigned_by] AS [assigned_by], 
[full].[assigned_to] AS [assigned_to], 
[full].[assigned_by_time] AS [assigned_by_time], 
[full].[closed_b            y] AS [closed_by], 
[full].[closed_by_time] AS [closed_by_time], 
[full].[blacked_out] AS [blacked_out], 
[full].[bycn_liaison_type] AS [bycn_liaison_type], 
[full].[bycn_liaison_debit] AS [bycn_liaison_debit], 
[full].[cause] AS [cause], 
[full].[mc_location] AS [mc_location], 
[full].[mc_parameter] AS [mc_parameter]
FROM [dbo].[full] AS [full]) AS [Extent1]
        GROUP BY [Extent1].[object], 
        )  AS [GroupBy1]
     )  AS [Project1]
ORDER BY [Project1].[C1] DESC',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',@p__linq__0='2015-03-14 00:00:00',@p__linq__1='2015-04-15 00:00:00'

- drex drex

在你的情况下，我认为 SqlProfiler 可以帮上忙。看起来生成的查询语句相当简单，所以我不确定是否有优化的机会，例如在 SQL 中添加索引或其他性能技巧。 - Eugeniu Torica

哦，nbr = g.Select(a => 1).Count()？ - user2160375

@Jenea 在 SQL Profiler 中，它会计算表的所有列，这需要太多时间。 - drex drex

翻译：翻译后的查询中最内层的SELECT似乎是不必要的。有没有办法告诉LINQ不要生成它？ - Serpiton

在我的代码中还是在 SQL Server Profiler 生成的代码中？ - drex drex

显示剩余3条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- oleksii · Accepted Answer

也许只需要几个优化就可以解决问题：

在选择之前先执行take操作
使用let关键字只计算一次分组

所以这是一个伪代码（这段代码写在记事本中，无法编译！）

var topFifty = (
    from p in db.full   
    group p by p.object into g
    let groupedCount = g.Count()
    orderby groupedCount descending 
    select p.key, groupedCount
    )
    .Take(50).ToList();

var topFifty.Select(x => new StringIntType
    {
        str = x.Key,
        nbr = x.Count
    }).ToList();