T-SQL查询和c# -当性能重要的时候

本文关键字：性能 重要的 查询 T-SQL | 更新日期: 2023-09-27 18:07:53

好的，我想不出一个很好的方式来表达这个在标题中但是场景是这样的:-

你的大楼有报告。该报告位于aspx页面上，后面有c#代码，可以访问T-SQL数据库。

获取数据的表非常大(数百万行)。您需要在另一个表上进行查找以获得这两列(Group和SuperGroup—见下文)，而这个查找表恰好也有几万行(更不用说您实际上必须连接两个表才能正确创建查找表—参见下面的#partGroups)

现在请记住页面正在运行，这将在2分钟后超时…

在此过程中必须做出一些假设:-

  1. 表和他们的布局是不可变的，不管设计是坏的或什么，他们是什么，你必须与他们合作(资产，CoreStockParts和CoreStockPartsGroups)。
  2. 页面超时不能修改
  3. PartNumbers (Text01在资产，PartNo在CoreStockParts)可以，可以和做包含-'s和/或空格在一个表，但不是另一个，所以他们需要删除。
  4. Assets中的partnumber有时可以用一个字符作为前缀，但在CoreStockParts中却不能。

这是我目前基本上得到的:-

    select rtrim(ltrim(Replace(Replace(csp.PartNo,' ',''), '-',''))) as PartNumber, 
        csp.[Description], csp.GroupCode, coalesce(cspg.[Group], 'Unknown') as [Group], coalesce(cspg.SuperGroup, 'Unknown') as SuperGroup
    into #partGroups
    from CoreStockParts as csp
        left join CoreStockPartsGroups as cspg on csp.GroupCode = cspg.Code
    select p.ID, 
        rtrim(Replace(Replace(p.Text01,' ',''), '-','')) as PartNumber1, 
        right(p.Text01, len(p.Text01)-1) as PartNumber2,
        p.Numeric01 as CostAmount, p.Numeric02 as SaleAmount, p.Numeric03 as ExtendedCostAmount, 
        p.Numeric04 as ExtendedSaleAmount, p.Numeric05 as Quantity, p.Date01 as InvoiceDate
    INTO #coreParts
    FROM Assets as p
    WHERE p.Category = 'PART'
    and len(p.Text01) > 0
    select ID, PartNumber1, PartNumber2, [Description], CostAmount, SaleAmount, ExtendedCostAmount, 
        ExtendedSaleAmount, Quantity, InvoiceDate, [Group], SuperGroup
    from #coreParts as cp
        inner join #partGroups as pg on cp.PartNumber1 = pg.PartNumber
    union
    select ID, PartNumber1, PartNumber2, [Description], CostAmount, SaleAmount, ExtendedCostAmount, 
        ExtendedSaleAmount, Quantity, InvoiceDate, [Group], SuperGroup
    from #coreParts as cp
        inner join #partGroups as pg on cp.PartNumber2 = pg.PartNumber

当前在中等服务器负载下完成大约1分45秒。仍然需要添加一些限制，包括但不限于基于Group、SuperGroup和基于InvoiceDate的日期范围进行筛选。最重要的是，一旦我最终有了这些数据，我就需要开始在它上面执行聚合函数，以生成各种组/超级组的销售数量/值等图表。

现在我在想如果我能保持这个速度....这就行了，尽管它并不理想。如果我能加快速度，那就太好了!然而，任何超过15秒的东西，我们就会碰壁。

所以我猜这个问题的关键是多重的:-

  1. 我错过了什么明显的，我可以做的优化这个一般吗?
    2. 在这一点上，将结果返回给c#和LINQ我需要的数字会更好吗?
  2. 我想，如果我在T-SQL中过滤，最好的地方是在临时表的选择，而不是在最后一个语句中产生的mash ?

编辑:好的，更新一下!

首先，我对允许的内容的评估是错误的，看起来，我们已经获得了添加快照表的授权，该快照表可以在夜间完成所有获取数据的工作，以便在第二天的时间和时间上运行报告代码。

特别感谢Blindy和user17594关于索引和防止使用索引的位的输入。(比特，你知道的技术语言8D)。

T-SQL查询和c# -当性能重要的时候

不要在asp页面上做报告，使用后台服务:

  1. 创建一个等待查询的服务(通过管道、套接字、wcf等)

  2. 当你需要一个报告时，从我们的asp页面向服务询问该报告是否已经存在，如果不存在，告诉服务创建它

  3. 向用户显示一个"Loading"消息，并通过Ajax请求你的页面询问你的服务报告是否准备好了

  4. 当服务"咀嚼"了所有的数据，通过你的asp页面呈现你的报告/图表。

将长期运行的进程从网页中分离出来总是一个好主意，以避免超时，并给用户留下一个看起来挂起的页面(假设一个非常紧张的用户开始按F5，因为页面需要2分钟来渲染…您的服务器将崩溃，生成大量报告)。

您可能希望尝试一些事情，看看哪些是有效的。请始终注意，对于任何更改，您都需要测试、测试、再测试，以查看它是否提高了系统的性能。

  • 使用UNION ALL而不是UNION来删除与UNION相关的DISTINCT检查(除非你知道这会重复结果)
  • 或者，删除UNION并用pg.PartNumber IN (cp.PartNumber1,cp.PartNumber2)替换您的join
  • 检查你的索引。and len(p.Text01) > 0将阻止使用索引查找。尝试AND p.Text01 IS NOT NULL AND p.Text01 != ''代替
  • 尝试索引您的临时表CREATE UNIQUE CLUSTERED INDEX IX_partGroups_tmp ON #partGroups (PartNumber)以及CREATE NONCLUSTERED INDEX IX_coreParts_tmp ON #coreParts (PartNumber1,PartNumber2)

尝试将数据处理保持在数据库中，因为数据平台为此进行了优化。尽可能多地进行筛选，并只将需要的内容返回给客户端。

另一个想法是创建Reporting Data服务。

这将在现有数据上运行，使用针对报告优化的结构填充一个单独的数据库，您的报告将在此基础上运行，这应该要快几个数量级，因为所有繁重的工作都由reporting服务完成。

一个缺点是，新数据将是"陈旧的"，这取决于更改填充到报表数据库中的速度。许多企业不会注意到或关心数据过期60或90秒，但这显然取决于您自己的用例。

我的方法是在您的表中添加计算列:

ALTER TABLE CoreStockParts 
ADD PartNumber AS RTRIM(LTRIM((REPLACE(REPLACE(PartNo,' ',''), '-','')));
ALTER TABLE CoreParts
ADD PartNumber1 AS RTRIM(REPLACE(REPLACE(p.Text01,' ',''), '-','')),
    PartNumber2 AS RIGHT(p.Text01, LEN(p.Text01)-1);

现在你有了这些计算列，你可以索引它们:

CREATE NONCLUSTERED INDEX IX_CoreStockParts_PartNumber ON CoreStockParts (PartNumber)
    --INCLUDE ([Description], GroupCode)

不确定您的确切表结构，但在索引中包含非键列可能有助于性能，如果您认为会取消第二行注释。

CREATE NONCLUSTERED INDEX IX_CoreParts_PartNumber1__part ON CoreParts (PartNumber1)
    --WHERE Category = 'PART' AND LEN(Text01) > 0
CREATE NONCLUSTERED INDEX IX_CoreParts_PartNumber2__part ON CoreParts (PartNumber2)
    --WHERE Category = 'PART' AND LEN(Text01) > 0

根据您将如何在其他地方使用该表，该索引可能受益于过滤索引，如果需要，再次取消注释。您可能还需要进一步索引CoreStockPartsGroups.Code和'CoreStockParts.GroupCode'。

最后，我不会使用临时表，通常最好避免使用临时表，因为您会失去源表上现有索引的好处:

WITH PartGroups AS
(   SELECT  csp.PartNumber,
            csp.[Description], 
            csp.GroupCode, 
            ISNULL(cspg.[Group], 'Unknown') AS [Group], 
            ISNULL(cspg.SuperGroup, 'Unknown') AS SuperGroup
    FROM    CoreStockParts AS csp
            LEFT JOIN CoreStockPartsGroups AS cspg 
                ON csp.GroupCode = cspg.Code
)
SELECT  p.ID, 
        p.PartNumber1, 
        p.PartNumber2, 
        p.[Description], 
        CostAmount = p.Numeric01, 
        SaleAmount = p.Numeric02,
        ExtendedCostAmount = p.Numeric03, 
        ExtendedSaleAmount = p.Numeric04, 
        Quantity = p.Numeric05, 
        InvoiceDate = p.Date01, 
        pg.[Group], 
        pg.SuperGroup
FROM    Assets as p
        INNER JOIN partGroups AS pg 
            ON p.PartNumber1 = pg.PartNumber
WHERE   p.Category = 'PART'
AND     LEN(p.Text01) > 0 as cp
UNION 
SELECT  p.ID, 
        p.PartNumber1, 
        p.PartNumber2, 
        p.[Description], 
        CostAmount = p.Numeric01, 
        SaleAmount = p.Numeric02,
        ExtendedCostAmount = p.Numeric03, 
        ExtendedSaleAmount = p.Numeric04, 
        Quantity = p.Numeric05, 
        InvoiceDate = p.Date01, 
        pg.[Group], 
        pg.SuperGroup
FROM    Assets as p
        INNER JOIN partGroups AS pg 
            ON p.PartNumber2 = pg.PartNumber
WHERE   p.Category = 'PART'
AND     LEN(p.Text01) > 0 as cp;

在启用显示实际计划的SSMS中运行，可以通过添加索引进一步改进。

相关文章：