c# - 查询执行速度非常慢，有什么办法可以进一步改进吗？

Question

我有以下查询，因为很多SUM函数调用，我的查询运行速度太慢。我的数据库中有很多记录，我想从今年和去年(过去 30 天、过去 90 天和过去 365 天)中获取每个记录的报告:

SELECT 
    b.id as [ID]
    ,d.[Title] as [Title]
    ,e.Class as [Class]

    ,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Current - Last 30 Days Col1]
    ,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Current - Last 30 Days Col2]

    ,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Current - Last 90 Days Col1]
    ,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Current - Last 90 Days Col2]

    ,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Current - Last 365 Days Col1]
    ,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Current - Last 365 Days Col2]

    ,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-13,GETDATE()) and a.DateCol <= DATEADD(MONTH,-12,GETDATE()) THEN a.col1 ELSE 0 END) as [Last year - Last 30 Days Col1]
    ,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-13,GETDATE()) and a.DateCol <= DATEADD(MONTH,-12,GETDATE()) THEN a.col2 ELSE 0 END) as [Last year - Last 30 Days Col2]

    ,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-5,GETDATE()) and a.DateCol <= DATEADD(QUARTER,-4,GETDATE()) THEN a.col1 ELSE 0 END) as [Last year - Last 90 Days Col1]
    ,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-5,GETDATE()) and a.DateCol <= DATEADD(QUARTER,-4,GETDATE()) THEN a.col2 ELSE 0 END) as [Last year - Last 90 Days Col2]

    ,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-2,GETDATE()) and a.DateCol <= DATEADD(YEAR,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Last year - Last 365 Days Col1]
    ,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-2,GETDATE()) and a.DateCol <= DATEADD(YEAR,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Last year - Last 365 Days Col2]


    FROM 
    tb1 a
INNER JOIN 
    tb2 b on a.id=b.fid and a.col3 = b.col4
INNER JOIN 
    tb3 c on b.fid = c.col5
INNER JOIN       
    tb4 d on c.id = d.col6
INNER JOIN 
    tb5 e on c.col7 = e.id
GROUP BY
    b.id, d.Title, e.Class

有谁知道如何改进我的查询以更快地运行？

编辑:我被鼓励移动 DATEADD对 where 的函数调用声明并首先加载前两年，然后在列中过滤它们，但我不确定建议的答案是否已执行并有效，可以在此处找到: https://stackoverflow.com/a/59944426/12536284

如果您同意上述解决方案，请告诉我如何将其应用到我当前的查询中？

仅供引用，我在 C#、 Entity Framework (DB-First)中使用这个 SP，如下所示:

var result = MyDBEntities.CalculatorSP();

Answer 1

正如已经提到的，execution plan在这种情况下将非常有帮助。根据您所展示的内容，您似乎已经从 tb1 (a) 中提取了 12 列，共 15 列。 ,
所以你可以尝试在没有任何连接的情况下运行你的查询，只针对 tb1查看您的查询是否按预期工作。由于我看不出您的 SUM 函数调用有任何问题，我最好的猜测是您的连接有问题，我建议您执行以下操作。例如，您可以先排除最后一个连接，
INNER JOIN tb5 e on c.col7 = e.id以及它的任何相关用法，如 e.Class as [Class]和 e.Class在您的组中通过语句。我们不会完全排除它，这只是一个
测试以确保问题是否与此有关，如果您的查询运行得更好并且如预期的那样，您可以尝试使用临时表作为解决方法而不是最后一个连接，某事
像这样:

SELECT *
INTO #Temp
FROM
  (
     select * from tb5
  ) As tempTable;

SELECT 
    b.id as [ID]
    ,d.[Title] as [Title]
    ,e.Class as [Class]

    -- SUM Functions

FROM 
    tb1 a
INNER JOIN 
    tb2 b on a.id=b.fid and a.col3 = b.col4
INNER JOIN 
    tb3 c on b.fid = c.col5
INNER JOIN       
    tb4 d on c.id = d.col6
INNER JOIN 
    #Temp e on c.col7 = e.id
GROUP BY
    b.id, d.Title, e.Class

实际上，临时表是临时存在于 SQL Server 上的表。临时表可用于存储多次访问的即时结果集。您可以在此处阅读更多相关信息 https://www.sqlservertutorial.net/sql-server-basics/sql-server-temporary-tables/
和这里 https://codingsight.com/introduction-to-temporary-tables-in-sql-server/

另外我强烈建议，如果您使用存储过程，请设置 NOCOUNT至 ON ，它还可以提供显着的性能提升，因为网络流量大大减少:

SET NOCOUNT ON
SELECT *
INTO #Temp
-- The rest of code

基于 this :

SET NOCOUNT ON is a set statement which prevents the message which shows the number of rows affected by T-SQL query statements. This is used within stored procedures and triggers to avoid showing the affected rows message. Using SET NOCOUNT ON within a stored procedure can improve the performance of the stored procedure by a significant margin.