Developer Webs
Developer Webs

Reputation: 1021

Declaring variables in loop versus before loop

Test #1 I have the SQL below that iterates through a cursor which has 374 records. I've tested the query a dozen times and it runs in 3 mins 40 secs (give or take a few seconds).

Test #2 I modify the query so that all declare statements appear before the while loop, the query then consistently completes 15 - 20 seconds faster.

I would have thought there would be almost no noticeable difference. Is a declare statement within a loop that much overhead, or is something else at play?

The cursor deals with the exact same records in both tests. I run test 1, then 2, then 1 again, then 2 again, etc. Test 2 has not run any worse than 8 seconds faster than test 1.

I'm pretty sure the declare statements are not causing the issue, but I can't explain why test 2 is consistently faster.

I should also mention that there are no other connections to the database while the tests are running.

declare @fillfactor int = 100


declare tableCursor CURSOR for
select  table_catalog, table_schema, table_name
from    information_schema.tables
where   table_type = 'BASE TABLE'
order   by table_schema, table_name

open tableCursor

declare @db varchar(128), @schema varchar(128), @table varchar(128)

fetch next from tableCursor into @db, @schema, @table

declare @cmd nvarchar(2000)
declare @lastLogged datetime = getdate()
declare @msg as varchar(500)
declare @startDate datetime = getdate()
declare @processed int = 0

while @@FETCH_STATUS = 0
begin
    declare @tableQualified varchar(200) = concat('[', @db, '].[', @schema, '].[', @table, ']')

    set @cmd = 'ALTER INDEX ALL ON ' + @tableQualified + ' REBUILD WITH (FILLFACTOR = 100)'
    exec (@cmd)

    fetch next from tableCursor into @db, @schema, @table

    set @processed += 1

    declare @now datetime = getdate()

    if datediff(second, @lastLogged, @now) > 30
    begin
        declare @elapsedSec int = datediff(second, @startDate, @now)
        declare @mins int = @elapsedSec / 60
        declare @seconds int = @elapsedSec % 60

        set @msg = concat(@processed, ' tables processed in ', @mins, 'm ', @seconds, 's')
        raiserror (@msg, 10, 1) with nowait
        
        set @lastLogged = getdate()
    end
END

Upvotes: 0

Views: 113

Answers (0)

Related Questions