SQL性能问题如何修改下面的脚本? [英] SQL performance issue how to modify the below Script?

问题描述

我编写了一个 SQL 查询，它涉及 INNER JOIN、LEFT OUTER JOIN、UNION 和一个需要很长时间执行的子查询.我的 SQL 技能不是很好，如果有人能帮助我如何优化以下查询以使其更快，我将不胜感激.

I have written a SQL query that involves INNER JOIN, LEFT OUTER JOIN, UNION and a subquery which is taking a long time to execute. My SQL skills are not that great and I would appreciate if someone can help me how can I optimize the following query to make it faster.

SELECT 
    Brand, 
    Quantity,  
    RegionNumber,  
    FinancialGroup,
    CustomerLocationNumber,  
    RecipientCode,  
    Company,  
    Contact,  
    Address1,  
    Address2,  
    City,  
    [State],  
    Zip,  
    Country,  
    SUM(CurrentYearSales) 'CurrentYearSales', 
    SUM(PriorYearSales) 'PriorYearSales'
FROM 
    (SELECT DISTINCT  
         B.BrandDescription AS Brand,   
         (CASE WHEN ISNULL(DS.FinancialGroup,'') = '' THEN 0 ELSE  ISNULL(DS.Quantity, MB.Quantity) END) As Quantity,  
         DS.RegionNumber AS RegionNumber,  
         DA.FinancialGroup 'FinancialGroup',  
         DA.CustomerGroup2 'CustomerLocationNumber',  
         RC.RecipientCode 'RecipientCode',  
         DA.Name AS Company,  
         'ATTN: KITCHEN DEPT' AS Contact,  
         DA.Address1 'Address1',  
         DA.Address2 'Address2',  
         DA.City,  
         DA.[State],  
         DA.Zip,  
         DA.Country,  
         ISNULL(CYearSales,0) 'CurrentYearSales',  
         ISNULL(PYearSales,0) 'PriorYearSales'  
     FROM 
         DealerLocator.DealerAddress DA   
     INNER JOIN 
         DealerLocator.MailingBrand MB ON DA.DealerAddessID = MB.DealerAddessID
     INNER JOIN  
         Brand B ON B.BrandCode = MB.BrandCode  
     INNER JOIN 
         [DealerLocator].[SalesRepCustomerGroup2] SR ON DA.FinancialGroup = SR.FinancialGroup 
                                                     AND DA.CustomerGroup2 = SR.CustomerGroup2  
     INNER JOIN 
         DealerLocator.RecipientCode RC ON DA.DealerAddessID = RC.DealerAddessID  
     INNER JOIN 
         DealerLocator.BrandException BE ON BE.BrandCode = MB.BrandCode 
                                         AND BE.BrandGroupID IS NOT NULL  
     INNER JOIN 
         DealerLocator.BrandGroup BGR ON BE.BrandGroupID = BGR.BrandGroupID   
     LEFT JOIN 
         #SalesSummaryData DS ON DA.FinancialGroup = DS.FinancialGroup 
                              AND DA.CustomerGroup2 = DS.CustomerGroup2  
                              AND DS.WebSiteName = BGR.WebsiteName  
     WHERE 
         DA.IsDeleted = 0 
        --Added below condition by senthil To get only active dealer locations-INC0115761    
         AND MB.IsActive = 1
         AND (DA.IsFedExAllowed = 1 AND DA.IsMailing = 1)  
         AND DS.RegionNumber = RC.RegionNumber 
         AND RC.FinancialGroup = DA.FinancialGroup
         AND RC.CustomerGroup2 = DA.CustomerGroup2
         AND (CASE WHEN ISNULL(DS.FinancialGroup,'') = '' THEN 0 ELSE ISNULL(DS.Quantity, MB.Quantity)END) != 0  
         AND (@UserID IS NULL OR SR.SalesRepID = @UserID)  
         AND (@BrandCodes IS NULL OR EXISTS (SELECT Top 1 1 FROM @BrandData WHERE Data = MB.BrandCode))  
         AND (@FinancialGroups IS NULL OR EXISTS (SELECT Top 1 1 FROM @FinancialData WHERE Data = DA.FinancialGroup))) temp
GROUP BY
    Brand,
    Quantity,  
    RegionNumber,  
    FinancialGroup,
    CustomerLocationNumber,  
    RecipientCode,  
    Company,  
    Contact,  
    Address1,  
    Address2,  
    City,  
    [State],  
    Zip,  
    Country 

在存储过程中，此查询花费了很长时间 - 任何人都可以帮助修改它以提高性能吗?如何提高性能并避免超时问题?

Inside of the stored procedure, this query was taking a long time - can anyone help modify it to improve the performance? How to improve the performance and avoid timeout issues?

推荐答案

我们首先需要您提供一些东西，太长了无法评论.

There are a few things we need from you first, which is too long to comment.

• 阅读获取帮助缓慢Brent Ozar 的团队查询.
• 执行您的查询并向我们提供实际执行计划.请务必将其粘贴到此处并提供链接.
• 请告诉我们更多有关您的环境的信息.特别是 CPU、内存、MAXDOP 设置、并行成本阈值等.
• 查看运行查询时发生的其他情况.可能是在争夺资源吧.sp_whoisactive 是一个很好的脚本来帮助解决这个问题.将结果告诉我们.
• 提供表的索引
• 阅读亚伦关于厨房的博文接收 查询，这是您对可选参数所拥有的，以进行改进.
• 让我们知道 DBCC SHOW_STATISTICS

• Read getting help with a slow query by Brent Ozar's team.
• Execute your query and give us the actual execution plan. Be sure to paste it here and provide the link.
• Tell us more about your environment. Specifically CPU, Memory, MAXDOP Setting, Cost Threshold for Parallelism, etc.
• See what else is going on when you are running your query. Maybe it's contending for resources. sp_whoisactive is a great script to help with this. Give us the results of it.
• Provide the indexes of the tables
• Read Aaron's post about Kitchen Sink queries, which is what you have with your optional parameters, for improvements.
• Let us know the output of DBCC SHOW_STATISTICS

以下是一些可能发生的事情，会减慢您的查询速度，了解上述详细信息有助于集中注意力:

Here are some things that could be happening and would slow down your query, which having the details above would help focus:

• 由于 参数嗅探，或其他东西的数组.
• 您在没有必要时使用 DISTINCT(可能只是猜测).它可能会导致问题.
• 您的表格统计数据 关闭，这会导致优化器请求不太理想的查询计划.Redgate 的文章对此提供了很好的信息.
• 您缺少导致表扫描的索引
• 您正在从小型服务器(CPU、RAM 等)查询数百万行
• 有其他查询或进程正在共享资源，导致锁定或阻塞等
• 您的查询正在等待(无论如何)，但是 Paul Randal 将帮助您确定它们是什么以及如何处理.

• You have a bad query plan due to parameter sniffing, or an array of other things.
• You use DISTINCT when it's not necessary (it may be, just a guess). It can cause issues.
• Your table statistics are off which is causing the optimizer to request a less than optimal query plan. Redgate's article has good info on this.
• You have missing indexes causing table scans
• You are querying millions of rows from a small server (CPU, RAM, etc)
• There are other queries or processes that are sharing the resources, causing locks or blocks, etc
• Your query is waiting on (whatever) but Paul Randal will help you to determine what they are and what to do about it.

执行后计划分析

您有一个表扫描，可以通过建议索引来缓解

You have a table scan which could be mitigated with the suggest index

Missing Index (Impact 66.8326): CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [SalesView].[SalesDetail_History] ([CustomerGroup2]) INCLUDE ([AccountNumber],[BrandCode],[FiscalYear],[FiscalMonth],[cYMonthAmt]

然后，当您尝试更新实际表、SSD、CAB 等时，您会在 TEMP TABLES 上进行大量表扫描...向其中添加索引您的临时表可以帮助解决此问题，但会减慢插入临时表的速度.您只需要尝试一下，看看它是否值得用于此查询.

Then you are getting a lot of table scans on your TEMP TABLES when you try to update your actual tables, SSD, CAB, etc... Adding indexes to your temp tables could help this, but will slow down the insert into the temp table. You'll just have to try it to see if it is worth it for this query.

最后，更改您的 MAXDOP 和 COST 设置，因为此查询中有很多可能不需要的并行性.

Lastly, change your MAXDOP and COST settings, since there is a lot of parallelism in this query which may not be needed.

这篇关于SQL性能问题如何修改下面的脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！