循环与调用函数的SQL性能差异

I have a stored procedure to insert records.

我有一个存储过程来插入记录。

I have to calculate a date value for a column with a specific logic. Currently I've created a loop for the data being inserted, and do the calculation to populate the date.

我必须为具有特定逻辑的列计算日期值。目前我已经为插入的数据创建了一个循环，并进行计算以填充日期。

The concern is I need to avoid using the loop to insert data and need to insert them as a batch. In order to do that I'll have to move the date calculation logic to a function.

关注的是我需要避免使用循环来插入数据，并需要将它们作为批处理插入。为了做到这一点，我必须将日期计算逻辑移动到一个函数。

What will be the differnce in performance wise of looping data (currently have) and using a function.

循环数据（当前有）和使用函数的性能差异是什么。

Here is my stored procedure:

这是我的存储过程：

WHILE @C <= @WeeklyDataCount
BEGIN
    DECLARE @PopulateDate DATE;

    SELECT 
        @Value = D.Value, 
        @FromDate = D.FromDate
    FROM 
        #WeeklyData D
    WHERE 
        D.AutoId = @C;

    -- Sample Date calculation logic that needs to move to a function
    @DayCount = SELECT COUNT(*)
                FROM DayTable

    @Counter2 = 1;

    WHILE @Counter2 < @DayCount
    BEGIN
        SET @PopulateDate = DATEADD(DAY, (-1 * @Counter2), @FromDate);
        SET @Counter2 = @Counter2 + 1;
    END
    -- End of Day Calculation Logic

    INSERT INTO TABLE1 (Value, PopulateDay)  
    VALUES(@Value, @PopulateDate)

    SET @C= @C +1;
END

1 个解决方案

#1

Your whole loop can be replace with one statement (I assume table DayTable is equal for every row from #WeeklyData).

你的整个循环可以用一个语句替换（我假设表的DayTable对于来自#WeeklyData的每一行都是相同的）。

INSERT INTO TABLE1 (Value,PopulateDay)
SELECT
    D.Value,
    DATEADD(DAY,(-1 * ((DayCount * (DayCount - 1))/2)),D.FromDate)
FROM #WeeklyData D
CROSS JOIN (SELECT COUNT(*) AS DayCount FROM DayTable) C
    WHERE D.AutoId <= @WeeklyDataCount