Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Question Unanswered: Cursor update performance issue

    I am using the following code which works fine except that when there are alot of rows being used (> 500) this performs really slow and I get a timeout error. Any ideas on how to make this faster since I need to update multiple rows, multiple times with multiple values?

    <code>
    DECLARE Item_Cursor CURSOR LOCAL FAST_FORWARD FOR
    Select FileObjectId,CopyFileObjectId From FileObject
    Where CopyFileObjectId IS NOT NULL
    AND CreateTime = @copyTime

    set @LastError = @@error
    if(@LastError <> 0) goto ERR_HANDLE

    OPEN Item_Cursor
    FETCH NEXT FROM Item_Cursor INTO @NewId,@OldId

    WHILE @@FETCH_STATUS = 0
    BEGIN
    --update idhierarchies
    Update FileObject Set IdHierarchy=Replace(IdHierarchy,'.'+Cast(@OldId as varchar)+'.','.'+Cast(@NewId as varchar)+'.')
    Where CopyFileObjectId IS NOT NULL
    AND CreateTime = @copyTime

    set @LastError = @@error
    if(@LastError <> 0) goto ERR_HANDLE

    --update parent ids
    Update FileObject Set ParentId=@NewId
    Where ParentId=@OldId
    AND CopyFileObjectId IS NOT NULL
    AND CreateTime = @copyTime

    set @LastError = @@error
    if(@LastError <> 0) goto ERR_HANDLE

    FETCH NEXT FROM Item_Cursor INTO @NewId,@OldId
    END
    CLOSE Item_Cursor
    DEALLOCATE Item_Cursor

    </code>

  2. #2
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    I've read that when you declare a cursor the result of the select-statement actually gets written to the temp-database and that's whats causing you such delays. But from what I can read from your procedure here it should be possible to do those updates without the use of a cursor...?
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  3. #3
    Join Date
    Mar 2003
    Posts
    2

    suggestions

    If you have any suggestions, let me know... I can't figure out how to do it in one or two update statements, that's definitely how I would prefer to do it and try to shoot for for every op. I just couldn't figur eout how to do that here.


    Originally posted by Frettmaestro
    I've read that when you declare a cursor the result of the select-statement actually gets written to the temp-database and that's whats causing you such delays. But from what I can read from your procedure here it should be possible to do those updates without the use of a cursor...?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •