主页 / dba / 问题 / 177518
Accepted
Serdia
Asked: 2017-06-29 08:27:59 +0800 CST

为什么计算列会突然降低性能？

  • 772

用户能够在上午 10 点之前运行报告。在同样的报告变得非常缓慢之后，有时用户只是没有耐心等待。经过一些故障排除后，我找到了导致延迟的列。它是计算列，它使用函数来获得结果。

大约在同一时间，我收到了另一个关于运行缓慢的报告，它总是运行良好。经过一些故障排除后，我发现了导致延迟的列：

where (Amount - PTD) <> 0

同样，该Amount列是计算列。

所以我的问题是：为什么总是作为报告一部分的所有突然计算列开始显着降低性能？大约在上午 10 点之后会发生什么？如果我制作这些专栏有什么缺点persisted？

谢谢

--FUNCTION 带来的列：

ALTER FUNCTION [dbo].[CalcInvoiceAmtPTD]
(@SInvNum INT, @entityGuid uniqueIdentifier) RETURNS MONEY
AS
BEGIN
    DECLARE @Amt MONEY

    declare @toplevel uniqueidentifier
    set @toplevel = (select dbo.gettoplevelentity(@entityguid))

    declare @t table 
    (
        Guid uniqueidentifier
    )

    insert into @t select * from dbo.getlinkedentities(@toplevel) where guid is not null
    declare @tbl table (amount money, glacctid int)

    select @amt = isNull(sum(amount), 0) from tblfin_journalpostings jp
        inner join tblfin_journal j on j.transactnum = jp.transactnum 
            and (voiderfor is null and voidedby is null)and j.transdescid <> 'I'
        inner join tblfin_glaccounttypes glt on glt.glacctid = jp.glacctid and glt.accounttype = 'p'
        inner join @t t on t.guid = jp.entityguid
    where invoicenum = @SInvNum

    RETURN ISNULL(@Amt  , 0)
END
sql-server performance

4 个回答

  1. 我将把它扔在那里，因为没有其他人提到它。

    在计算列中使用标量值函数是一个糟糕的主意。

    • 强制对表的所有查询连续运行（即使您没有选择计算列）
    • 强制维护（索引、CHECKDB）连续运行，即使您使用的是 Enterprise
    • 逐行执行
    • 如果在WHERE子句中使用，可能会导致基数估计出现问题

    以下是我写的一些关于类似主题的博客文章。

    这么多年了还是连载

    计算列中的标量函数

    检查约束中的标量函数

    • 7
  2. Best Answer

    有和没有计算列的性能差异很好知道，并且可能会导致整体查询的改进，但它并不能真正解释为什么相同的查询有显着的性能差异，计算列，在不同一天中的时间。

    您的查询在上午 10 点之后运行速度变慢的原因有很多。当然，如果没有您提供的大量信息，我们很可能无法告诉您系统上发生的事情会导致此时发生这种变化。

    但是，这里有几种可能性：

    • 活动增加- 最简单的可能性是查询在上午 10 点之后运行较慢，因为您的系统在上午 10 点之后更加活跃。在美国，一家在全国各地设有办事处但服务器位于东部时区（员工通常在上午 9 点左右到达）的企业将在上午 10 点之前在系统中拥有东部和中部时区的所有员工。对于我工作过的此类企业，这已超过员工总数的 2/3。因此，系统通常在该点之后运行速度会慢一些。

      我还处理过某些活动往往在一天中的某些时间持续发生并且可能产生重大影响的情况。在我工作的一个系统上，每天下午同一时间出现的减速最终与在办公室外工作的员工几乎同时返回办公室并同步他们的设备以上传他们的日常活动有关。

      或者，从稍微不同的方向来看：也许查询本身在上午 10:15 以与上午 8:15 相同的速度运行 - 但办公室中可能有一半的人在上午 10 点之前正在流式传输内容，并且可用带宽将报告数据放到某人的桌面上已减少了 5 倍。

      如果这是您的问题，那么您需要准确确定瓶颈是什么（服务器上的 CPU/内存；服务器和用户之间的可用网络带宽），并查看是否可以增加限制资源。（或者，如果是最后一种情况，让每个人都停止流式传输内容！）

    • 其他活动的类型- 除了简单的特定活动外，在一天中不同时间发生的某些类型的活动也可能会产生影响。如果人们从上午 10 点左右开始对您的数据库进行销售呼叫，则可能有足够INSERT多的UPDATE活动针对您正在点击的一个或多个表，因此SELECTs 可能必须等待这些表清除才能运行。更糟糕的是，如果应用程序在有人开始销售电话时锁定了一行（以确保不会有两个人同时尝试给同一个潜在客户打电话），等待时间可能会更长。

      根据您的报告要求和应用程序的工作方式，如果这是您的实际问题，您可能可以使用READ_COMMITTED_SNAPSHOT_ISOLATION，甚至是WITH (NOLOCK)表格​​提示来解决这种情况。

    • 使用模式- 在上午 10 点之前和之后使用报告的方式也可能有所不同。如果您缓存了一个查询计划，该计划期望传入的参数之一具有高度选择性，并且在上午 10 点之后，大多数查询运行都使用选择性较低的值，这可能会影响事情。

      举个例子：假设您的公司总部位于俄亥俄州辛辛那提（该州与另外两个州肯塔基州和印第安纳州接壤）。您 90% 的客户在俄亥俄州，其余 10% 在相邻的两个州。报告按各州的每日销售额运行，程序是最后运行俄亥俄州。如果查询计划是使用 构建的state = 'KY'，并且在上午 10 点之前使用该或 Indiana 运行，并且使用state = 'OH'，那么查询计划可能根本无法执行。

      另一方面——也许某个聪明的灵魂过去曾遇到过这个问题——他的解决方案是从上午 10 点到下午 5 点每 15 分钟清除一次计划缓存。这也可能会混淆查询（尽管您希望它会混淆所有内容）。

    • 磨损——也许你每晚凌晨 1 点都在重建你的统计数据；但是上午 10 点之前的活动已经对数据进行了足够的更改，因此这些统计信息已关闭。

    • 主观时间- 用户可能会启动报告，去喝杯咖啡，然后回来发现它在上午 10 点之前完成，然后在办公桌前运行它，然后等待它完成。不是说这可能是答案，而是发生了奇怪的事情。

    • “其余的......” - 以上绝不是详尽的可能性列表，只是基于我多年来遇到的一些想法。

    当您收到此类问题的报告时，向您的用户群征求有用的反馈非常重要。问以下问题：

    • 性能下降真的发生在每天上午 10 点吗？它每天都会有所不同，还是只在星期一和星期二发生？
    • 性能下降实际上只影响此报告吗？还是会影响系统上的其他活动？（如果在上午 10 点之后一切都需要两倍的时间，但大多数事情仍然只需要 5-30 秒，而此报告在上午 10 点之前需要 5 分钟，之后需要 10 分钟，那么您可能会得到您所描述的行为，但它可能完全无关与报告）。
    • 您在上午 10 点之前和之后使用报告的方式有什么不同吗？
    • 是否有过像整个上午 10 点之前那样快速返回报告的时间？一整天？如果是这样，这种变化是渐进的，还是有一个可识别的点，即上午 10 点后的表现从好到坏？如果有，什么时候？

    我还要注意，虽然您已经告诉我们，如果您删除计算列，这个查询需要 4 分钟到 4 秒，但您没有告诉我们在 8:15、9:15 需要多长时间, 10:15 和 11:15。你没有提到查询的查询计划，以及上午10点前后是否是同一个计划；将上午 10 点之前和之后的实际计划执行发布到PasteThePlan，这里的人们可以看到有什么不同，并就可以解决问题的方法提供明智的建议。即使这些信息没有指向答案，它也可以排除一些可能性。

    • 4

  3. 你应该始终保持计算列的持久化，除非你有非常一致的插入/更新，并且从该表中读取的次数很少。

    我为什么这么说？

    因为将持久化添加到计算列意味着计算完成的时刻（在每次插入/更新时）列的值被写入数据库。每当您再次需要这些值时，sql server 只需将它们发送给您已经计算过的值。这样，每次您对该表进行查询并请求该列时，CPU 都不需要重新计算它们。这可以为您的报告节省大量时间，因为您再次进行某种计算

    缺点是每次执行任何类型的插入或更新时，sql server 都需要再次计算该值，这会减慢繁忙表上的插入/更新速度。

    现在，为了进一步改进您的查询，您需要熟悉 SARGAable 查询，简而言之，这意味着您不能对 where 条件下的列进行任何类型的计算/修改，例如：

    where (Amount - PTD) <> 0 ; 
where Ammount + ' ' = 'Something' ; 
where (Ammount * 2) = 100

    等等等等

    要更熟悉它，请阅读此

    或者，您可以执行以下操作：

     where Amount > PTD or Ammount < PTD

    对于进一步的优化，它取决于列和其他where条件，但您还应该阅读此内容

    1. 使用覆盖索引提高查询性能
    2. 聚集和非聚集索引

    希望这可以帮助

    • 3

  4. 上面的所有讨论都是有帮助的。

    Inline Scalar function讨论中缺少它的好处。

    重写您的 UDF 并使其成为“ Inline Scalar function”

    " Inline Scalar function" 肯定会提高性能

    或者我不会使用该计算列并使用CROSS apply.

    但首先你必须做出正确和优化查询。

    i) 你statistics的没有更新。

    ii) 或者您的数据随着时间的推移而增长。

    • 1

相关问题