SSIS存储过程性能问题

Question

我正在使用SSIS将原始数据行转换为事务。一切都很顺利，直到我为一个名为"splitpercentage"的新字段添加了逻辑。执行SQL命令。新字段只是将值转换为小数，例如02887将转换为0.2887。

新的逻辑按预期工作，但现在需要8小时运行，而不是5分钟。

请在这里查看完整的原始代码和新代码:

非常感谢任何帮助!

新逻辑导致性能差:

IF TRIM(SUBSTRING(@line, 293, 1)) = 1
BEGIN
SET @SplitPercentage = 1
END
ELSE 
BEGIN
SET @SplitPercentage = CAST(''.'' + TRIM(SUBSTRING(@line, 294, 4)) AS decimal(7, 4))
END

Answer 1

虽然您当前的代码不理想，但我在您的新表达式(SUBSTRING(),TRIM(), concatation,CAST)中没有看到任何会导致如此剧烈的性能影响的内容。我怀疑原因在别处。

但是，我相信您的表达式可以简化以消除IF。给定一个5个字符的字段"nnnnn"如果您希望将其视为小数n.nnnn，则应该能够在单个语句中使用STUFF()注入小数点:

@SplitPercentage = CAST(STUFF(SUBSTRING(@line, 293, 5), 2, 0, '.') AS decimal(7, 4))

STUFF()在位置2注入小数点(替换0个字符)。我看不需要TRIM().

(您将在Exec ('...')语句中使用双引号)

Answer 2

请尝试如下更改IF/ELSE代码块:

SET @SplitPercentage = IIF(TRIM(SUBSTRING(@line, 293, 1)) = ''1''
, 1.0000
, CAST(''.'' + TRIM(SUBSTRING(@line, 294, 4)) AS DECIMAL(7, 4)));

Answer 3

您遇到的挑战是"我有一个巨大的动态查询过程，我无法调试"。当我遇到这些问题时，我试着把问题分解成更小的、可解决的、基于集合的选项。

阅读这一堆代码，我的伪代码应该是这样的

1. 对于Inbound_Transaction_Source中的所有数据，按给定的源值(@SourceName)
2. 通过将当前行切成块来完成所有这些数据验证，类型更正和清理

您可以通过将数据切片而失去基于行的方法。在我的生活中，我倾向于使用CROSS APPLY，但是CTE，派生表，任何你认为有意义的都是有效的。

我喜欢这种方法的原因是，你可以看到你正在构建的东西，测试它，然后修改它，而不用担心你会打乱一个纸牌屋。

-- Column ordinal declaration and definition is offsite
SELECT
*
FROM 
[dbo].[Inbound_Transaction_Source] AS ITS
CROSS APPLY
(
SELECT
CurrentAgentNo = SUBSTRING(ITS.line, @CurrentAgentStartColumn, 10) 
,   CurrentCompMemo = SUBSTRING(ITS.line, @CompMemoStartColumn + @Multiplier, 1)
,   CurrentCommAmount = SUBSTRING(ITS.line, @CommAmountStartColumn + @Multiplier, 9)
,   CurrentAnnCommAmount = SUBSTRING(ITS.line, @AnnCommAmountStartColumn + @Multiplier, 9)
,   CurrentRetainedCommAmount = SUBSTRING(ITS.line, @RetainedCommAmountStartColumn + @Multiplier, 9)
,   CurrentRetainedSwitch = SUBSTRING(ITS.line, @RetainedSwitchStartColumn + @Multiplier, 9)
-- etc
-- A sample of your business logic
, TransactionSourceSystemCode = SUBSTRING(ITS.line, 308, 3)
)NamedCols
CROSS APPLY
(
SELECT
-- There's some business rules to be had here for first year processing
-- Something special with position 102
SUBSTRING(ITS.line,102 , 1) AS SeniorityBit
-- If department code? is 0079, we have special rules
,   TRIM(SUBSTRING(ITS.line,141, 4)) As DepartmentCode
)BR0
CROSS APPLY
(
SELECT
CASE 
WHEN NamedCols.TransactionSourceSystemCode in ('LVV','UIV','LMV') THEN
CASE WHEN BR0.SenorityBit = '0' THEN '1' ELSE '0' END
WHEN NamedCols.TransactionSourceSystemCode in ('CMP','FAL') AND BR0.DepartmentCode ='0079' THEN
CASE WHEN BR0.SenorityBit = '1' THEN '0' ELSE '1' END
WHEN NamedCols.TransactionSourceSystemCode in ('UIA','LMA','RIA') AND BR0.SenorityBit > '1' THEN
'1'
WHEN NamedCols.TransactionSourceSystemCode in ('FAL') THEN
'1'
ELSE '0'
END
)FY(IsFirstYear)
WHERE Source = @SourceName 
ORDER BY Id;

为什么处理时间从5分钟增加到8小时?

它可能与动态SQL的更改无关。当一个SSIS包运行时，它是"永远"的。相对于正常情况，最好是在它还在运行的时候，看看你的源和目标，并记下发生了什么，因为它很可能是两者之一。

游标使您的生活变得复杂，一旦您开始以集合为单位思考，它就不需要了，但它不太可能是给定的性能问题的根源，因为您有一个可靠的正常基线。此外，该查询是带有单个过滤器的单个表查询。

您的SSIS包的数据流可能是芯片拍摄源到目的地提取和加载或Slurp和Burp，没有中间转换(因为逻辑都在存储过程中)。如果是这种情况，那么只有两个可能的争用性能点是源和目标。由于源看起来微不足道，那么很可能是其他进程在这8小时内占用了目的地。如果您在源和目标上运行了sp_whoisactive之类的东西，那么您可以识别阻塞您运行的进程。