While loop or dynamic execution

Question

I have a table: VIEWING_17to31_aug_server which has 393085432 rows.

I was trying to update a field by a join condition (the join condition is correct). But the query could not be executed as it exceeded query temp space.

I then tried to use while loop which did not break but has been running for ages now.

CREATE VARIABLE @var_period_start               datetime;
CREATE VARIABLE @var_period_end                 datetime;
CREATE VARIABLE @scanning_day                   datetime;

SET @var_period_start  = '2013-08-17';
SET @var_period_end    = '2013-08-31';
SET @scanning_day = @var_period_start;

while @scanning_day <= dateadd(dd,0,@var_period_end)
begin
    UPDATE VIEWING_17to31_aug_server
    SET A.calculated_scaling_weight = B.calculated_scaling_weight
    FROM VIEWING_17to31_aug_server AS A
    LEFT JOIN sk_prod.viq_viewing_data_scaling AS B
    ON A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
    AND A.VIEWING_DAY = B.ADJUSTED_EVENT_START_DATE_VESPA
    AND A.VIEWING_DAY = @SCANNING_DAY
End

Is there any other way I can get it done faster? Will dynamic execution help?

Answer 1

I think you need to increment @scanning_day within the loop, at the moment the while condition will never be met because @scanning_day remains constant as @var_period_start. e.g.

SET @scanning_day = dateadd(dd, 1, @scanning_day);

So your full script would become:

CREATE VARIABLE @var_period_start               datetime;
CREATE VARIABLE @var_period_end                 datetime;
CREATE VARIABLE @scanning_day                   datetime;

SET @var_period_start  = '2013-08-17';
SET @var_period_end    = '2013-08-31';
SET @scanning_day = @var_period_start;

while @scanning_day <= dateadd(dd,0,@var_period_end)
begin
    UPDATE VIEWING_17to31_aug_server
    SET A.calculated_scaling_weight = B.calculated_scaling_weight
    FROM VIEWING_17to31_aug_server AS A
    LEFT JOIN sk_prod.viq_viewing_data_scaling AS B
    ON A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
    AND A.VIEWING_DAY = B.ADJUSTED_EVENT_START_DATE_VESPA
    AND A.VIEWING_DAY = @SCANNING_DAY;

    SET @scanning_day = dateadd(dd, 1, @scanning_day);
End