0

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
12
  • 1
    Instead of trying to roll your own I'd strongly advise using Ola Halengren's Index maintenance scripts. For example, EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @LogToTable = 'Y' will rebuild or reorg all tables based on the fragmentation level and log results Commented Nov 28, 2024 at 8:52
  • In any case you only posted a single incomplete script (where's the cursor?). We can't say why one script or the other behaves differently like this. The "fast" script may be skipping tables or the "slow" script may have an extra slow operation. Commented Nov 28, 2024 at 9:15
  • 2
    No idea why it's any other way, but watch out with declares in loops, they aren't re-initialized so the original values are being kept. This might not be a problem in your case, but it's something a lot of people miss out Commented Nov 28, 2024 at 10:33
  • 1
    Also you are not using QUOTENAME it should be declare @tableQualified nvarchar(1000) = CONCAT_WS(',', QUOTENAME(@db), QUOTENAME(@schema), QUOTENAME(@table)) Commented Nov 28, 2024 at 14:29
  • 1
    Expanding on @siggemannen comment - Variables declared without an explicit initializer will not be reinitialized (reset to null) with each loop iteration. However, if an explicit initializer is present, the initialization expression will be evaluated and result assigned to the variable with each iteration. Commented Nov 29, 2024 at 3:09

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.