查询执行速度非常慢，有什么办法可以进一步改进吗? [英] The query is executing very slowly, is there any way to improve it any further?

问题描述

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

I have the following query, and because of a lot of SUM function calls, my query is running too slow. I have a lot of records in my database and I would like to get a report from the current year and last year (Last 30 days, Last 90 days and last 365 days) for each one:

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

有谁知道如何改进我的查询以加快运行速度?

Does anyone have any idea how can I improve my query in order to run faster?

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

I was encouraged to move the DATEADD function call to the where statement and load first two years first then filter them in columns, but I am not sure the suggested answer is executed and works, it could be found here: https://stackoverflow.com/a/59944426/12536284

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

If you agree with the above solution, please show me how can I apply it in my current query?

仅供参考，我在 C#、实体框架(DB-First)中使用这个 SP，如下所示:

Just FYI, I am using this SP in C#, Entity Framework (DB-First), something like this:

var result = MyDBEntities.CalculatorSP();

推荐答案

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

As it has been mentioned already, the execution plan will be really helpful in this case. Based on what you've shown it seems you have extracted 12 columns of 15 total columns from tb1 (a), so you can try to run your query without any join and just against the tb1 to see whether your query is working as expected. Since I can see nothing wrong with your SUM function calls, my best guess is you have an issue with your joins, I would suggest to do the following. You can start by excluding the last join for instance, INNER JOIN tb5 e on c.col7 = e.id and any related usage of it like e.Class as [Class] and e.Class in your group by statement. We are not going to exclude it completely, this is just a test to make sure whether the problem is with that or not, if your query runs better and as expected you can try to use a temp table as a workaround instead of the last join, something like this:

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/

Actually, Temporary tables are tables that exist temporarily on the SQL Server. The temporary tables are useful for storing the immediate result sets that are accessed multiple times. You can read more about it here https://www.sqlservertutorial.net/sql-server-basics/sql-server-temporary-tables/ And here https://codingsight.com/introduction-to-temporary-tables-in-sql-server/

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

Also I would strongly recommend, if you are using the Stored Procedure, set the NOCOUNT to ON, it can also provide a significant performance boost, because network traffic is greatly reduced:

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

基于 这个:

SET NOCOUNT ON 是一个设置语句，它阻止显示受 T-SQL 查询语句影响的行数的消息.这在存储过程和触发器中使用，以避免显示受影响的行消息.在存储过程中使用 SET NOCOUNT ON 可以显着提高存储过程的性能.

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.

这篇关于查询执行速度非常慢，有什么办法可以进一步改进吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！