c# - LinqToSql查询返回时间太长性能太长

Question

我正在做一个相当沉重的LinqToSql语句，该语句返回一个新对象。由于SQL方法的数量（主要是求和和转换），SQL需要花费很长时间才能运行，因此加载网页需要很长时间（10-15秒）。虽然我可以将AJAX或类似的东西与CSS加载器一起使用。我首先想知道是否有一种简单的方法来实现我试图从SQL数据库获得的内容。

我在尝试着：


返回给定字段不为空的所有用户
在机会表中获取状态为“打开”且外键匹配的所有当前项目。 （手动加入后）
在这些机会中，将几个字段的所有货币值的总和存储到我的课程中
获取这些货币价值的计数。


Linq语句本身耗时很长，但是当转换为SQL时，它充满了COALESCE和其他大量SQL方法。

这是我的LINQ语句：

 decimal _default = (decimal)0.0000;
            var users = from bio in ctx.tbl_Bios.Where(bio => bio.SLXUID != null)
                      join opp in ctx.slx_Opportunities.Where(opp => opp.STATUS == "open") on bio.SLXUID equals opp.ACCOUNTMANAGERID  into opps
                      select new UserStats{
                          Name = bio.FirstName + " " + bio.SurName,
                          EnquiryMoney = opps.Where(opp => opp.SALESCYCLE == "Enquiry").Sum(opp => (opp.ACTUALAMOUNT.HasValue && opp.ACTUALAMOUNT.Value != _default ? opp.ACTUALAMOUNT : opp.SALESPOTENTIAL.HasValue ? (decimal)opp.SALESPOTENTIAL.Value : _default)).GetValueOrDefault(_default),
                          EnquiryNum = opps.Where(opp =>  opp.SALESCYCLE == "Enquiry").Count(),
                          GoingAheadMoney = opps.Where(opp => opp.SALESCYCLE == "Going Ahead").Sum(opp => (opp.ACTUALAMOUNT.HasValue && opp.ACTUALAMOUNT.Value != _default ? opp.ACTUALAMOUNT : opp.SALESPOTENTIAL.HasValue ? (decimal)opp.SALESPOTENTIAL.Value : _default)).GetValueOrDefault(_default),
                          GoingAheadNum = opps.Where(opp =>  opp.SALESCYCLE == "Going Ahead").Count(),
                          GoodPotentialMoney = opps.Where(opp => opp.SALESCYCLE == "Good Potential").Sum(opp => (opp.ACTUALAMOUNT.HasValue && opp.ACTUALAMOUNT.Value != _default ? opp.ACTUALAMOUNT : opp.SALESPOTENTIAL.HasValue ? (decimal)opp.SALESPOTENTIAL.Value : _default)).GetValueOrDefault(_default),
                          GoodPotentialNum = opps.Where(opp =>  opp.SALESCYCLE == "Good Potential").Count(),
                          LeadMoney = opps.Where(opp => opp.SALESCYCLE == "Lead").Sum(opp => (opp.ACTUALAMOUNT.HasValue && opp.ACTUALAMOUNT.Value != _default ? opp.ACTUALAMOUNT : opp.SALESPOTENTIAL.HasValue ? (decimal)opp.SALESPOTENTIAL.Value : _default)).GetValueOrDefault(_default),
                          LeadNum = opps.Where(opp =>  opp.SALESCYCLE == "Lead").Count(),
                          PriceOnlyMoney = opps.Where(opp => opp.SALESCYCLE == "Price Only").Sum(opp => (opp.ACTUALAMOUNT.HasValue && opp.ACTUALAMOUNT.Value != _default ? opp.ACTUALAMOUNT : opp.SALESPOTENTIAL.HasValue ? (decimal)opp.SALESPOTENTIAL.Value : _default)).GetValueOrDefault(_default),
                          PriceOnlyNum = opps.Where(opp =>  opp.SALESCYCLE == "Price Only").Count(),
                          ProvisionalMoney = opps.Where(opp => opp.SALESCYCLE == "Provisional").Sum(opp => (opp.ACTUALAMOUNT.HasValue && opp.ACTUALAMOUNT.Value != _default ? opp.ACTUALAMOUNT : opp.SALESPOTENTIAL.HasValue ? (decimal)opp.SALESPOTENTIAL.Value : _default)).GetValueOrDefault(_default),
                          ProvisionalNum = opps.Where(opp =>  opp.SALESCYCLE == "Provisional").Count()
                      };

Answer 1

您可以执行多项操作：


筛选索引：根据“机会”表中围绕“打开”值的记录细分，您可以在“打开”上创建筛选索引。如果“打开”和“关闭”（或其他任何值）的数量大致相等，则过滤索引将使您的TSQL仅查看具有“打开”的记录。筛选索引仅存储符合谓词的数据；在这种情况下，您要加入的任何东西的值都为“开放”。这样，它就不必扫描其他索引来查找其中可能具有“打开”状态的记录。
摘要/汇总表：创建具有所需值的汇总表；在这种情况下，您要查找求和和计数-为什么不创建只包含一行具有这些计数的表？您可以使用存储过程/代理作业来使其保持最新状态。如果查询允许，您也可以尝试创建索引视图；我将在下面进行介绍。对于汇总表；实际上，您将运行一个存储过程，该存储过程将计算这些字段并定期更新（例如，每隔几分钟或每分钟一次，具体取决于负载），然后将这些结果写入新表中；这将是您的汇总表。然后，您的结果就像选择语句一样简单。这将非常快，但要花费每几分钟来计算这些总和的负担。根据记录的数量，这可能会出现问题。
索引视图：可能是解决此类问题的正确方法，depending on your constraints，以及我们正在讨论的行数（以我为例；在有成千上万行的情况下，我一直追求它）。


筛选索引

您还可以为每个状态创建一个过滤索引（这有点滥用；但是可以工作），然后简单地在进行求和/计数时，只需要依赖与要查找的状态匹配的索引即可。

要创建过滤索引：

CREATE NONCLUSTERED INDEX FI_OpenStatus_Opportunities
    ON dbo.Opportunities (AccountManagerId, Status, ActualAmount)
    WHERE status = 'OPEN';
GO

同样，您的总和和计数（每列一个）：

CREATE NONCLUSTERED INDEX FI_SalesCycleEnquiry_Status_Opportunities
    ON dbo.Opportunities (AccountManagerId, Status, SalesCycle, ActualAmount)
    WHERE status = 'OPEN' and SalesCycle = 'Enquiry'

（以此类推）。

我并不是说这是您最好的主意；但这是一个主意。它是否好取决于它在您的环境中对工作负载的性能（我无法回答）。

索引视图

您还可以创建一个包含此汇总信息的索引视图。这有点高级，取决于您。

要做到这一点：

  CREATE VIEW [SalesCycle_Summary] WITH SCHEMABINDING AS
    SELECT AccountManagerID, Status, SUM(ActualAmount) AS MONETARY
    ,COUNT_BIG(Status) as Counts 
FROM [DBO].Opportunities
GROUP BY AccountManagerID, Status
GO


-- Create clustered index on the view; making it an indexed view
CREATE UNIQUE CLUSTERED INDEX IDX_SalesCycle_Summary ON [SalesCycle_Summary] (AccountManagerId);

然后（取决于您的设置），您可以直接加入该索引视图，也可以通过提示将其包括（尝试使用前者）。

最后，如果这些都不起作用（索引视图周围有些陷阱-我已经有大约6个月没有使用它们了，所以我不太记得那个困扰我的具体问题），则可以随时创建CTE，完全放弃Linq-To-SQL。

这个答案有点超出范围（因为我已经给出了两种方法，并且它们需要您进行大量调查）。

要调查这些行为：


从您的Linq-To-SQL语句（ here's how you do that）获取生成的SQL。
打开SSMS，然后在查询窗口中打开以下内容：


SET STATISTICS IO ON
SET STATISTICS TIME ON
选中“显示实际查询计划”和“显示估算的查询计划”复选框
将生成的SQL复制到其中；运行。

在继续操作之前，请解决与索引有关的所有问题。如果您收到缺少索引警告；调查并解决这些问题，然后重新运行基准测试。


这些起始数字是您的基准。


Statistics IO会告诉您查询正在进行的逻辑和物理读取的数量（越低越好-专注于首先读取大量数据的区域）
统计信息TIME告诉您查询运行了多长时间并将其结果显示给SSMS（请确保将 SET NOCOUNT ON设为打开，以免影响结果）
实际查询计划会准确告诉您正在使用的内容，SQL Server认为缺少的索引以及其他可能影响结果的问题，例如隐式转换或错误的统计信息。 Brent Ozar Unlimited has a great video on the subject，所以我在这里不再重述。
估计的查询计划告诉您SQL Server认为将要发生的事情-这些并不总是与实际查询计划相同-您需要确保考虑到调查中的差异。


这里没有“简单”的答案；答案取决于您的数据，数据使用情况以及可以对基础架构进行的更改。在SSMS中运行此代码后，您将看到其中有多少是Linq-To-SQL开销，以及有多少是查询本身。