存储过程还是LinQ？百万条记录（使用EF core 2.0）

本文介绍了存储过程还是LinQ？百万条记录（使用EF core 2.0）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我们正在为大型数据集工作，目前在一个SQL Server表中有200万条记录。我们需要运行一些查询以使用Entity Framework Core 2.0通过Web API生成报告。

We are working for large data sets which is currently having 2 million records in a single SQL Server table. We need to run some queries to have our reports generated through Web API using Entity Framework Core 2.0.

由于我们正在转向微服务模式，因此我们需要摆脱存储程序或代码中的Raw SQL，以便部署变得更容易。但我们面临的问题是，当我们使用LINQ时，我们会遇到性能问题
，与存储过程或原始SQL相比，这需要更多的时间。

Since we are moving to Microservices pattern, hence we need to get rid of the stored procedures or the Raw SQL in the code, so that deployment could get easier. But the problem we are facing is that when we are using LINQ, we are getting performance issue which is taking far more time as compared to stored procedure or raw SQL.

以下LINQ需要31秒（~31,828毫秒），相比之下，在过滤和分组后，在SQL中需要0.25秒才能获取304结果集。我们非常需要使用LINQ来摆脱存储过程，但我们没有得到预期的结果
。数据已被更改以保持机密性。

Following LINQ is taking 31 secs (~31,828 ms) as compared to 0.25 secs in SQL to fetch 304 resultset after filtering and grouping. We badly need to use LINQ in order to get rid of the stored procedures but we are not getting the results as expected.Data has been changed to maintain confidentiality.

甚至尝试过已编译的LINQ ，但未在规定的时间内获得满意的结果。

Even tried the compiled LINQ, but not getting the satisfactory results within stipulated time.

我们使用的SQL如下：

SQL we are using is as follows:

SELECT
	F.ID AS 'A',
	F.NAME AS 'B',
	F.NUMBER AS 'C',
	F.SNAME AS 'D',
	F.ZIP AS 'E',
	F.LATITUDE AS 'F',
	F.LONGTITUDE AS 'G',
	COUNT(*) AS 'COUNT'
FROM CLIENTDATA CC JOIN FACILITY F ON CC.FACILITYID=F.ID
WHERE CC.ISACTIVE=1 AND CC.ISCASE=1 AND F.LATITUDE IS NOT NULL
AND F.LONGTITUDE IS NOT NULL
GROUP BY  F.ID,F.NAME,F.NUMBER,F.SNAME,F.ZIP,F.LATITUDE,F.LONGTITUDE
ORDER BY A

我们使用的对应LINQ：

Corresponding LINQ we are using:

(from CC in this.dbContext.ClientData join F in this.dbContext.Facility
on CC.FacilityId equals F.Id  where CC.IsActive && CC.IsCase &&
&& F.Latitude != null && F.Longtitude != null
group F by new {
	F.Id, F.Name, F.Number, F.SName, F.Zip, F.Latitude, F.Longtitude
} into grp
select new DataDTO
{
A = grp.Key.Id,
B = grp.Key.Name,
C = grp.Key.Number,
D = grp.Key.SName,
E = grp.Key.Zip,
F = grp.Key.Latitude,
G = grp.Key.Longtitude,
COUNT = grp.Count()
}).OrderBy(x => x.A);

我们非常感谢任何帮助。非常感谢提前。

Any help would be much appreciated. Many thanks in advance.

使用EF

存储过程还是LinQ？百万条记录（使用EF core 2.0）

问题描述

推荐答案