is cursor or sp_msforeachtable quicker.....
-
i have a monthly job that runs a re-index on all the tables in a db.
It looks like this:
CREATE procedure DWPROD_sp_dw_rebuildindexes_all_ff80
as
/* Script used to rebuild all the indexes in the database with a fill
factor of 80%.
*/
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT "Reindexing " + @TableName -- not neccessary but will leave it
DBCC DBREINDEX(@TableName,' ',80) -- will allow room to grow when loading
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
------------------------------
I'm wondering if it would be any quicker to run sp_msforeachtable with teh dbcc reindex command instead.
Or is it about the same?
-
I have no idea why, but the cursor is faster. Time trials on one of our databases with over 500 tables took about 100 milliseconds as a cursor and 1000 milliseconds using sp_MSforeachtable (10 times longer). I used the following.
declare @start_time datetime
set @start_time = GetDate()
--BEGIN BLOCK COMMENT HERE TO USE sp_MSforeachtable
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ( 'PRINT ''Reindexing ' + @TableName + '''' ) -- not neccessary but will leave it
-- DBCC DBREINDEX(@TableName,' ',80) -- will allow room to grow when loading
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
--END BLOCK COMMENT HERE TO USE sp_MSforeachtable
--EXEC sp_MSforeachtable @command1='print ''Reindexing ?'' '
print DATEDIFF( MS, @start_time, GetDate() )
-
Hi!
>>I have no idea why, but the cursor is faster.
I have idea
Look at master.dbo.sp_MsForeachtable - and you will find cursor.
It is not astonishing that sp_MsForeachtable works slower.
But look from another point - time for reindexing is MUCH greater than time for FETCH NEXT. Is there real difference what to use?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply