SQL Server Performance

Cursor problem

Discussion in 'General Developer Questions' started by query4u, Jul 23, 2009.

  1. query4u New Member

    i have written one cursor in that i am fetching records(around 1000/5000 it varies) , for every record i am doing some operations like checking validations,insertion,updation etc on various tables, but if any error comes it directly come out of loop & operation gets stop. some times sql time out error comes. i have done exception handling then also it's not returning the error code. also in front end it 's not showing any error, simply the process gets stop. can anybody give me solution for this. i am using Sql 2000 as database & .net 2.0 as frontend.
    Thanks in advance
  2. melvinlusk Member

    Can you post the code?
  3. query4u New Member

    Thanks melvinlusk for helping me. as i can not post the entire code in this as three/four different procedure also gets called. i will just explain you, i have written a cursor in which i am fetching some records from a table, using that individual record i am passing some parameters to 3/4 different stored procedures where i do some validation/insertion/update operations. for each records this process is repeating. but i mention in my earlier post sometimes the process gets stops in between & it never send an exception to front end.This process gets stop at any record & this is not happening every time means for ex. if there are 10000 records it may works properly & for 500 records it may not. we can not give guaranty.also in sql error logs it is not showing anything. Is there any alternate solution for this. please help me.
  4. melvinlusk Member

    Without seeing the code, my guess is that one of the values you're pulling from a table that you pass to a procedure is bogus....maybe a null value or something of that nature. I would try to inspect the records that you're passing to the procedure manually to see if they look suspect.
  5. query4u New Member

    I will upload the file for you. Is there any alternative solution to cursor in MSSQL 2000? so that process can be faster
  6. melvinlusk Member

    You may be better off using WHILE loops instead of cursors. For example, instead of using this cursor:
    IF Exists (SELECT * FROM tab2 WHERE UploadID = @UploadID AND STATUS = 0 AND VoucherNumber = '-1'BEGIN DECLARE @TotalAmount DECIMAL(16,2) SET @TotalAmount = 0.0
    DECLARE UploadAllocationCursor CURSOR FOR
    SELECT AllocationID, [Name], MobileNumber, EmailID, Amount, ExpiryDate, AdditionalNotes FROM tab2 WHERE UploadID = @UploadID AND STATUS = 0 AND VoucherNumber = '-1'SELECT @err = @@error IF @err <> 0 BEGIN DEALLOCATE UploadAllocationCursor RETURN @err END OPEN UploadAllocationCursor SELECT @err = @@error IF @err <> 0 BEGIN DEALLOCATE UploadAllocationCursor RETURN @err END FETCH NEXT FROM UploadAllocationCursor INTO @CursorAllocationID, @CursorName, @CursorMobileNumber, @CursorEmailID, @CursorAmount, @CursorExpiryDate, @AdditionalNotes
    WHILE (@@FETCH_STATUS=0 OR @err <> 0) BEGIN
    SET @NewCreatedBy = @CreatedBy + '~NOTE:' + @AdditionalNotes
    Take the data from table "tab2", put it in a temp table, and go through the records:create
    table #tmpTab2(
    AllocationID datatype,[Name] datatype
    ,MobileNumber datatype
    ,EmailID datatype
    ,Amount datatype
    ,ExpiryDate datatype
    ,AdditionalNotes datatype
    ) insert
    into #tmpTab2 select * from tab2 WHERE UploadID = @UploadID AND STATUS = 0 AND VoucherNumber = '-1'while
    exists (select 1 from #tmpTab2)BEGIN
    DECLARE @TotalAmount DECIMAL(16,2)
    SET @TotalAmount = 0.0 EXEC usp_Manage_Gift -1, @CursorName, @CursorMobileNumber, @CursorEmailID, 0, @GifteecreatedBy, @DefaultDate, '-1', 1, @CompanyID, '-1', 1, 1, @RetGifteeID OUTPUT, @GifteeERRORCODE OUTPUT SELECT @err = @@error IF @err <> 0 GOTO Fail IF (@GifteeERRORCODE = '000' OR @GifteeERRORCODE = '006')
    BEGIN UPDATE tab2 SET Status = 3 WHERE AllocationID = @CursorAllocationID EXEC usp_Manage_Vouch -1, @IssueType, @RetGifteeID, @CompanyPromotionID, @CursorAmount, @CursorAmount, @CursorExpiryDate, 1, @NewCreatedBy, 1, @UploadType, @RetVoucherID OUTPUT, @ERRORCODE OUTPUT SELECT @err = @@error IF @err <> 0 GOTO Fail IF (@ERRORCODE = '000')
    UPDATE tab2 SET Status = 1, VoucherID = @RetVoucherID, Remarks = --------Logic continues---------------------------delete
    from #tmpTab2 where UploadID = @UploadIDEND
    Have you tried running a trace against this?

Share This Page