优化两个旧的长期运行的存储过程 - SQL Server 2014 [英] Optimizing two old long-running stored procedures - SQL Server 2014

问题描述

我正在处理两个旧存储过程，这些过程需要2天的执行时间，以便对它们进行优化。

第一个有一些输入参数，对一些本地表变量完成一些插入和更新操作，并使用一个调用第二个存储过程的游标。光标选择约300,000个融资合约。

第二个存储过程处理每个单一合约，并进行一些插入和更新操作

针对某些本地表变量而且，并且有2个while循环，以便将合同方面的总付费金额b
除以每个到期分期付款。每个while循环迭代100-200次。

第一个过程被应用程序调用3-4次。

解决方案

< blockquote>

当然不可能优化我看不到的代码。

我只能说是一个递归的CTE，它只增加一个计数器而不添加更多的行是一个伪装的光标，所以没有任何意外，它没有运行得更快。

根据你的描述，重写为完美的基于设置是一个相当大的挑战这需要至少约50-100小时的工作。

Hi,
I'm working on two old stored procedures that take 2 days as execution time, in order to optimize them.
The first one has some input parameters, accomplishes some insert and update operations against some local table variables and uses a cursor that calls the second stored procedure. The cursor selects about 300.000 financing contracts.

The second stored procedure processes each single contract, with some insert and update operations
against some local table variables and, and has 2 while loops in order to divide the total paid amount
for contract respect to each due installments. Each while loop iterates for 100-200 times.
The first procedure is called 3-4 times by an application.

解决方案

It is of course impossible to optimize code that I don't see.

All I can say is that a recursive CTE that just increments a single counter without adding more rows is very much a cursor in disguise, so there is not really any surprise that it does not run any faster.

From what you describe, rewriting to be perfectly set-based is quite a challenge that requires at least some 50-100 hours of work.

这篇关于优化两个旧的长期运行的存储过程 - SQL Server 2014的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！