You see that database? OFF WITH HIS HEAD!
A while ago I was chatting with a friend that complained about a migration script timing out on his local machine. When I looked at the script, it was fairly obvious what was wrong:
DECLARE @VideoID UNIQUEIDENTIFIER
DECLARE @NewID UNIQUEIDENTIFIER
DECLARE VideoCursor CURSOR READ_ONLY
FOR
SELECT ID FROM Video
OPEN VideoCursor
FETCH NEXT FROM VideoCursor
INTO @VideoID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @NewID = NEWID()
INSERT INTO Content (ID, Body, FormatBody, Plain)
SELECT @NewID, ContentItem.Body, Video.FormatBody, Video.Plain
FROM ContentItem
INNER JOIN Video
ON Video.Id=ContentItem.ID
WHERE Video.Id=@VideoID
UPDATE Video SET ContentId=@NewID WHERE Video.Id=@VideoID
UPDATE ThumbImage SET ContentId=@NewID WHERE Video_id=@VideoID
FETCH NEXT FROM VideoCursor
INTO @VideoID
END
The script was using a cursor!
Every time you want a use a cursor, you must fast for three days while reading the memoires of Edgar F. Codd.
Cursors are evil!
Let us see how we can make this work using set based logic, shall we?
INSERT INTO #TempContent
SELECT newid() as NewId, Video.Id as OldId, ContentItem.Body, Video.FormatBody, Video.Plain
FROM ContentItem
INNER JOIN Video
ON Video.Id=ContentItem.ID
WHERE Video.Id=@VideoID
INSERT INTO Content(ID, Body, FormatBody, Plain)
SELECT NewId, ContentItem.Body, Video.FormatBody, Video.Plain
UPDATE Video
SET ContentId=NewId
FROM #TempContent
WHERE Video.Id=OldId
UPDATE ThumbImage
SET ContentId=NewId
FROM #TempContent
WHERE Video.Id=OldId
DROP TABLE #TempContent
I can assure you that this will work faster, read better, get parallelize by the database and in generally be better behaved than the previous version.