SP执行时间非常慢 [英] SP execution time is extremely slow

问题描述

我创建了一个存储过程，该存储过程根据线性自调整规则计算财务利差，并且需要2分钟以上的时间才能完成计算.

I created a stored procedure that calculates a financial spreading based on a linear self adjusting rule and it takes more than 2 minutes to finish the calculations.

最终值经过多次迭代，以便对其进行调整和增强，直到找到最佳的优化最终值为止. 参数如下:

The final value goes through multiple iterations in order to adjust and enhance it till finding the optimal optimized final value. The parameters are the following:

@input1 = 100000
@input2 = 40
@input3 = 106833


BEGIN
DECLARE @X decimal(22,6) = 0
DECLARE @Y decimal(22,6) = 0.001 
DECLARE @Z decimal(22,6)
DECLARE @r decimal(22,6)
DECLARE @v decimal(22,6) 

SET @v = POWER(1/(1+ (@Y/12)), @input2)
    SET @r = ((@Y/@input2) * input1) / (1-@v) 
    IF (@r < @input3)
        SET @Z = @Y + ABS((@X - @Y)/2)
    ELSE
        SET @Z = @Y - ABS((@X - @Y) /2)

    SET @X = @Y
    SET @Y = @Z 


WHILE (ABS(@r - @input3) > 0.001)
BEGIN
SET @v = POWER(1/(1+ (@Y/12)), @input2)
    SET @r = ((@Y/@input2) * @input1) / (1-@v) 
    IF (@r < @input3)
         SET @Z = @Y + ABS((@X - @Y)/2)
    ELSE
         SET @Z = @Y - ABS((@X - @Y) /2)
    SET @X = @Y
    IF @Y = @Z
    BREAK
    SET @Y = @Z
END

RETURN (CAST(@Y AS decimal(22,6)) * 100)



END

运行时间= 2分20秒

run time = 2 mins and 20 seconds

推荐答案

用TSQL编写的存储过程的替代方法可能是用C#编写的SQL CLR函数.您必须使用Visual Studio并创建一个数据库项目.

An alternative to your stored procedure written in TSQL might be a SQL CLR function written in C#. You have to use Visual Studio and create a Database Project.

    public static decimal ConvertTo6(double d)
    {
        return Math.Round(Convert.ToDecimal(d), 6, MidpointRounding.AwayFromZero);
    }

    public static decimal ConvertTo6(decimal d)
    {
        return Math.Round(d, 6, MidpointRounding.AwayFromZero);
    }


    [Microsoft.SqlServer.Server.SqlFunction]
    [return: SqlFacet(Precision = 22, Scale = 6)]
    public static SqlDecimal CalcFinancialSpreading(int input1 = 100000, int input2 = 40, int input3 = 106833)
    {
        decimal x = 0.000000m;
        decimal y = 0.001000m;
        decimal z;
        decimal r;
        decimal v;

        v = ConvertTo6(Math.Pow(1 / (1 + (Convert.ToDouble(y) / 12d)), input2));

        r = ConvertTo6(((y / input2) * input1) / (1 - v));

        if (r < input3)
        {
            z = y + Math.Abs((x - y) / 2);
            z = ConvertTo6(z);
        }
        else
        {
            z = y - Math.Abs((x - y) / 2);
            z = ConvertTo6(z);
        }

        x = y;
        y = z;

        while (Math.Abs(r - input3) > 0.001m)
        {
            v = ConvertTo6((Math.Pow(Convert.ToDouble(1 / (1 + (y / 12))), Convert.ToDouble(input2))));

            r = ((y / input2) * input1) / (1 - v);
            r = ConvertTo6(r);

            if (r < input3)
            {
                z = y + Math.Abs((x - y) / 2);
                z = ConvertTo6(z);
            }
            else
            {
                z = y - Math.Abs((x - y) / 2);
                z = ConvertTo6(z);
            }
            x = y;
            if (y == z) break;
            y = z;
        }

        decimal result = y * 100;

        return new SqlDecimal(result);
    }

作为C#代码执行，结果在45秒内在我的计算机上收到，而TSQL在1分钟56秒内得到接收.

Executed as C# code the result is received in 45 seconds on my machine vs. TSQL in 1 Min 56 seconds.

@wikiCan的荣誉可以通过回答这一个 ...

Kudos to @wikiCan by answering this one...

这篇关于SP执行时间非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！