如何提高此存储过程的性能

Below is the stored procedure's dynamic query result which is fetching records from a view and other tables using join as shown below. It is fetching only 5 rows, but taking 13 seconds to execute. I need to reduce execution time.

下面是存储过程的动态查询结果,它使用join从视图和其他表中获取记录,如下所示。它只获取5行,但需要13秒才能执行。我需要减少执行时间。

How can I achieve that? I have already used execution plan no index is missing in the table used in this query. Suggest any other way. I also analyzed that view is taking much time to execute, is there any way to improve view performance as well?

我怎样才能做到这一点?我已经使用了执行计划,在此查询中使用的表中没有丢失索引。建议任何其他方式。我还分析了这个视图需要花费很多时间来执行,有没有办法改善视图性能?

DECLARE @MonthId int

SELECT TOP 1 
    @MonthId = Month_Id 
FROM
    CVBAT_Mart.dbo.Fact_AE_CurrentMonth WITH (NOLOCK)

SELECT    
    Site_Churn_Cd    
    ,SUM(FAE.Site_Count) AS Site_Count    
    ,SUM(FAE.Account_Count) AS Account_Count        
    ,FORMAT(SUM(TY_DailyVolume),'0,0,') as TY_ADV    
    ,FORMAT(SUM(LY_DailyVolume),'0,0') as LY_ADV    
    ,Site_Churn_Cd_color =    
              CASE WHEN Site_Churn_Cd = 'D' THEN 'f8bd19'    
                   WHEN Site_Churn_Cd = 'G' THEN '33ccff'    
                   WHEN Site_Churn_Cd = 'L' THEN 'ccff66'    
                   WHEN Site_Churn_Cd = 'N' THEN 'ffcccc'    
                   WHEN Site_Churn_Cd = 'U' THEN 'c0c0c0'    
                   ELSE Site_Churn_Cd 
              END
FROM 
    CVBAT_Mart.dbo.vFact_AE_Service_Category_TT FAE WITH (NOLOCK)     
INNER JOIN 
    common_ESTAT.dbo.SR_Hierarchy_ESTAT_Promoted_Monthly ESTAT WITH (NOLOCK) ON FAE.Enprs_Employee_Id = ESTAT.SR_Level_1_ID 
WHERE 
    c_Excluded_Flag = 'N'    
    AND c_Site_Primary_AE = '1'    
    AND FAE.Month_Id = @MonthId      
    AND Customer_Shipment_Role_Cd = '03'    
    AND EXISTS (SELECT 1         
                FROM CVBAT_Mart.dbo.DIM_Geography DG WITH (NOLOCK)    
                WHERE Organization_Active_Ind = 1    
                  AND DG.Region_Num = ESTAT.SR_Level_1_Region_Num    
                  AND DG.District_Num = ESTAT.SR_Level_1_District_Num)
  GROUP BY    
      Site_Churn_Cd

2 个解决方案

#1

Without an execution plan and/or additional schema data there isn't much to go on. EXISTS can cause performance concerns and should be replaceable with a JOIN condition. I don't think this will solve your problem but try it anyways.

没有执行计划和/或其他架构数据,就没有太多可继续的了。 EXISTS可能会导致性能问题,并且应该可以通过JOIN条件进行替换。我不认为这会解决你的问题,但无论如何都要尝试。

When posting queries like this, please make sure you alias all fields. The aliases help to imply some schema information, though it is not nearly as good as actual schema data.

发布此类查询时,请确保为所有字段设置别名。别名有助于暗示某些架构信息,尽管它不如实际架构数据好。

DECLARE @MonthId INT
SELECT  TOP 1
        @MonthId = Month_Id
FROM    CVBAT_Mart.dbo.Fact_AE_CurrentMonth WITH (NOLOCK)

SELECT  Site_Churn_Cd,
        SUM( FAE.Site_Count ) AS Site_Count,
        SUM( FAE.Account_Count ) AS Account_Count,
        FORMAT( SUM( TY_DailyVolume ), '0,0,' ) AS TY_ADV,
        FORMAT( SUM( LY_DailyVolume ), '0,0' ) AS LY_ADV,
        Site_Churn_Cd_color = CASE
                                  WHEN Site_Churn_Cd = 'D' THEN 'f8bd19'
                                  WHEN Site_Churn_Cd = 'G' THEN '33ccff'
                                  WHEN Site_Churn_Cd = 'L' THEN 'ccff66'
                                  WHEN Site_Churn_Cd = 'N' THEN 'ffcccc'
                                  WHEN Site_Churn_Cd = 'U' THEN 'c0c0c0'
                                  ELSE Site_Churn_Cd
                              END
FROM    CVBAT_Mart.dbo.vFact_AE_Service_Category_TT AS FAE WITH (NOLOCK)
JOIN    common_ESTAT.dbo.SR_Hierarchy_ESTAT_Promoted_Monthly AS ESTAT WITH (NOLOCK)
    ON  ESTAT.SR_Level_1_ID = FAE.Enprs_Employee_Id
JOIN    CVBAT_Mart.dbo.DIM_Geography AS DG WITH (NOLOCK)
    ON  DG.Organization_Active_Ind = 1
    AND DG.Region_Num = ESTAT.SR_Level_1_Region_Num
    AND DG.District_Num = ESTAT.SR_Level_1_District_Num
WHERE   c_Excluded_Flag = 'N'
    AND c_Site_Primary_AE = '1'
    AND Customer_Shipment_Role_Cd = '03'
    AND FAE.Month_Id = @MonthId
GROUP BY Site_Churn_Cd

#2

You mention that the query returns only 5 rows, but note that the query is an aggregation of rows spanning two tables. The performance of the query will be proportional to the number of rows before aggregation is applied. You can reason with the time taken based on that rather than using the final aggregated row count.

您提到查询只返回5行,但请注意,查询是跨越两个表的行的聚合。查询的性能将与应用聚合之前的行数成比例。您可以基于此而不是使用最终聚合行计数来计算所花费的时间。

Let's for the moment assume that even before aggregation the number of rows is reasonable. In that case, you would need to ensure you have the right set of indexes to facilitate the WHERE conditions, the JOIN and the EXISTS. This is where looking at the execution plan for your query would guide you.

让我们暂时假设即使在聚合之前,行数也是合理的。在这种情况下,您需要确保拥有正确的索引集以方便WHERE条件,JOIN和EXISTS。这是查看您的查询的执行计划将指导您的地方。

You can even break down the query into each stage check performance at each stage. I notice that the first table involved in the query is actually a View, going by the naming. It is possible that the view is another set of table joins.

您甚至可以将查询分解为每个阶段的每个阶段检查性能。我注意到查询中涉及的第一个表实际上是一个视图,通过命名。视图可能是另一组表连接。

Overall, your best bet would be build up the query in steps and check for any optimizations with respect to indexes at each step. Being able to read and understand the query execution plan is essential to get the best result.

总的来说,您最好的选择是按步骤构建查询,并检查每个步骤中有关索引的任何优化。能够阅读和理解查询执行计划对于获得最佳结果至关重要。

#1