使用Postgres的EF Core性能比原始SQL查询差

Question

我正在尝试诊断我在C#中针对Postgres DB编写的查询的确切问题，我已经在.NET Core WebAPI项目中使用Scaffold-DbContext生成了上下文。

我期望两个查询的速度相似，但当我使用Postgres ODBC驱动程序或PgAdmin运行一个给我相同结果集的查询时，我想知道：为什么“纯SQL”版本的性能要好得多？

我的SQL查询如下：

SELECT oolk.salesrep, SUM(oool.openqty) 
FROM public.oolookup oolk 
INNER JOIN public.ooorderlines oool ON oool.orderlinekey = oolk.orderlinekey 
WHERE oolk.salesteam = 'Team1' AND oolk.categorycode = 'Category 8'
GROUP BY oolk.salesrep

通过ODBC运行此查询并通过WebAPI（本地主机）将结果返回为JSON需要：2216毫秒。

在C#中的“相同”查询：

(from oolk in db.Oolookup
 join oool in db.Ooorderlines on oolk.Orderlinekey equals oool.Orderlinekey
 where oolk.Salesteam == "Team1"
 where oolk.Categorycode == "Category 8"
 group new { oolk, oool } by oolk.Salesrep into g
 select new
 {
     SalesRep = g.Key,
     OpenQty = g.Sum(gr => gr.oool.Openqty)
 }).ToList()

运行此查询并通过 WebAPI（本地主机）以 JSON 格式返回结果需要 8353 毫秒。

当我在 C# 代码中使用数据库日志记录时，这是似乎被查询表达式发送到我的 PG 数据库的查询。

SELECT "oolk0"."pk_oolookup", "oolk0"."categorycode", "oolk0"."customercode", "oolk0"."customerdiv", "oolk0"."customergroup", "oolk0"."forecastgroup", "oolk0"."orderclass", "oolk0"."orderkey", "oolk0"."orderlinekey", "oolk0"."productcode", "oolk0"."saleslocation", "oolk0"."salesrep", "oolk0"."salesteam", "oolk0"."shippinglocation", "oool0"."orderlinekey", "oool0"."openamount", "oool0"."openappliedheadercharges", "oool0"."openitemamount", "oool0"."openlinechargeamount", "oool0"."openqty", "oool0"."openvolume", "oool0"."openweight", "oool0"."totalamount", "oool0"."totalheaderchargeamount", "oool0"."totalitemamount", "oool0"."totalqty", "oool0"."totalvolume", "oool0"."totalweight"
FROM "oolookup" AS "oolk0"
INNER JOIN "ooorderlines" AS "oool0" ON "oolk0"."orderlinekey" = "oool0"."orderlinekey"
WHERE ("oolk0"."salesteam" = 'Team1') AND ("oolk0"."categorycode" = 'Category 8')
ORDER BY "oolk0"."salesrep"

我对此有几点感到奇怪。首先，我从未指定要从数据库中选择这么多列，就像我必须使用“普通SQL”一样。然而，它们在这里。其次，这看起来只是“真实查询”的一半。我看不到任何进行汇总的“SQL”，因此我认为这是在.NET对象内部而不是在数据库上发生的。

对于索引，我正在使用星型模式构建数据仓库。因此，我的ooorderlines表上的连接字段/主键已经建立了索引（单个列），我 oolookup表上的每个列都有一个索引（单个列）。因此，我不认为这是索引问题。我把这归咎于我对.NET中查询表达式的经验不足。

那么六秒钟的差异来自哪里呢？

- Justin Capalbo

---

谢谢回复。很好奇你是如何通过阅读这篇帖子就知道"TrackingOn"已启用的。能否再具体一点？我在寻找关于"TrackingOn"的参考资料时遇到了困难。接下来我会尝试使用gcServer=true。 - Justin Capalbo

你是从数据库还是从C#运行SQL？任何对数据库的查询都需要将结果封装在数据库接口中，然后传输到VS，最后在C#中解封。这将始终增加执行时间。使用Linq比使用SQLClient等数据库库要快。Linq比旧式的数据库库更高效。 - jdweng

@jdweng 我已经测试了两者，它们大致相同。所以看起来我的C#开销并不高。我在pgAdmin中测试了查询，然后在我的EF Core项目中再次测试，最后又在C#中使用ODBCConnection/command进行了一次测试。ODBCConnection/command和PgAdmin中的查询几乎以相同的速度运行。 - Justin Capalbo

维基百科上有一个接口列表（https://en.wikipedia.org/wiki/PostgreSQL）。也许一个C++封装器可以加快结果速度。 - jdweng

根据我的经验，EF Core在目前阶段只适用于涉及“实体”的简单CRUD和查询。个人意见。 - Ivan Stoev

Answer 1

关于添加ORDER BY - 在EF中存在问题，看起来无法解决https://dev59.com/aV8e5IYBdhLWcg3wqr2t#26821542

关于不需要的属性选择 - 您可以尝试下面的代码

var neededDataQ =
    from oolk in db.Oolookup
    join oool in db.Ooorderlines
        on oolk.Orderlinekey equals oool.Orderlinekey
    where
        oolk.Salesteam == "Team1"
        &&
        oolk.Categorycode == "Category 8"
    select new {
        oolk.Salesrep,
        oool.Openqty,
    };

var groupedDataQ =
    from neededData in neededDataQ
    group neededData by neededData.Salesrep into g
    select new
    {
        SalesRep = g.Key,
        OpenQty = g.Sum(gr => gr.Openqty)
    };

var groupedDataList = await groupedDataQ.ToListAsync(cancellationToken);

- Ivan Ivanyuk