Reputation: 1021
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