Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Cursor fetch loops endlessly Expand / Collapse
Author
Message
Posted Sunday, November 17, 2013 8:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 10:02 AM
Points: 14, Visits: 49

I am having a cursor which I use to update a 2nd table containing unique IDs from an identity column.
The cursor typically returns 100-300 records and the code is looping through the cursor to update the column.

The code is similar to the below.

This has been working fine for over 2 years. Now suddenly, while my cursor still only returns about 150 rows, the loop runs indefinitely, i.e. it adds tens of thousands of records into the "lpn" table, and it does update the "addresses" table. When it reaches the last record of the cursor, it starts again at the beginning basically, updating the record in the "addresses" table which corresponds to the first record in my cursor and so on.

So the loop never ends, and it just keeps updating the "addresses" table.

I can of course add a statement with an integer increment within the WHILE loop counting how many records it has inserted and check it against the total number of records in my cursor. If I do that, it properly ends.

But the code has been working for years. Why would it suddenly stop working? What could cause this?

I know that concurrent fetch operations can mess things up with the @@fetch_status variable, but
there are no queries and no other stored procedures running on that instance which do any sort of fetch.



declare @newLPN int;
declare @currentID int;
set @newLPN = 0;
set @currentID = 0;

DECLARE cursorlpn CURSOR FOR
select AccountID from Addresses
where country = 'HR';

fetch next from cursorlpn into @currentID

while (@@fetch_status = 0)
begin
insert into lpn (deliveryID) values (0);
set @newLPN = scope_identity();
update Addresses set lpn = @newLPN where AccountID = @currentID;

fetch next from cursorlpn into @currentID
end


Post #1514994
Posted Sunday, November 17, 2013 9:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 7,042, Visits: 12,971
The best solution would be to get rid of c.u.r.s.o.r. *cough* in the first place.
Insert all data into lpn at once and use the OUTPUT clause to get the returned identity values. Then perform a singel update to the Addresses table for all AccountID's at once.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1514997
Posted Sunday, November 17, 2013 9:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 10:02 AM
Points: 14, Visits: 49

Well, I guess you're right, and I agree. I inherited the code and yes, it won't stay this way.

But my question is rather why does something like this occur - it did work for 2 years and it's perfectly valid T-SQL, so why does SQL Server suddenly decide it has to go into a spin turn-around?
It was consistent and reproducable, I tried it with various cursor filters, about 20 times.
Post #1515003
Posted Sunday, November 17, 2013 9:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:41 AM
Points: 23,081, Visits: 31,621
Peter Schulz-485500 (11/17/2013)

Well, I guess you're right, and I agree. I inherited the code and yes, it won't stay this way.

But my question is rather why does something like this occur - it did work for 2 years and it's perfectly valid T-SQL, so why does SQL Server suddenly decide it has to go into a spin turn-around?
It was consistent and reproducable, I tried it with various cursor filters, about 20 times.


Based on just the code for the cursor, I have no idea as it looks correct. Since we don't have access to your system and the data there really isn't much we can tell you. A guess would be something changed recently. Could be the structure of the data, could be something in the data.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1515004
Posted Sunday, November 17, 2013 10:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 7,042, Visits: 12,971
Can you provide a test scenarion so we can reproduce it on our machines?
I'm confident the c.u.r.s.o.r. *cough* doesn't restart "all of a sudden, all by itself".

My guess would be there's a code change "outside" that fires the code where the loop is being called. Maybe a trigger added to the Addresses table or something like that.

If you run a profiler trace, do you spot anything unexpected?




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1515007
Posted Sunday, November 17, 2013 12:33 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 1,786, Visits: 5,682
Generally speaking, in most programming languages it is a bad idea to "loop" through a data set that you are updating.

I could imagine that in this case you could have hit a situation where those updates are affecting the underlying data in some way that is messing with the logic of the loop, which would be avoided either by working with a temporary copy of the list of Accounts you are going to update or by using an INSENSITIVE cursor.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1515014
    Posted Sunday, November 17, 2013 2:34 PM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Friday, May 30, 2014 10:02 AM
    Points: 14, Visits: 49
    Here is some test code.

    I am afraid you won't find any problem on the test code, as I could not do so myself. It occurs only in my production database.

    Seems that the general consent is that there couldn't be anything wrong really in SQL Server itself? Has no-one every seen a cursor behaving this way?
    If I can rule this out as a possibility, then well, alright, I'll have to dig into the rest of this application. I just want to know if this is a possibility.

    The application I am having here is a mix of Visual Basic with some direct SQL queries and a handful of stored procedures. The cursor I am talking about is in a stored procedure. This isn't so pretty obviously.
    I am not sure if Visual Basic isn't doing cursor fetching behind the scenes so this could mess up my cursor possibly also.


    -- test database and tables --

    -- create new test data base
    create database tmp4051
    go
    --



    use tmp4051
    go

    IF OBJECT_ID('dbo.Addresses','U') IS NOT NULL
    DROP TABLE Addresses
    IF OBJECT_ID('dbo.lpn','U') IS NOT NULL
    DROP TABLE lpn

    create table Addresses (AccountID int PRIMARY KEY,
    Name1 varchar(50),
    Name2 varchar(50),
    Address1 varchar(50),
    country char(2),
    lpn int NULL)

    create table lpn (deliveryID int,
    lpn int IDENTITY(1,1))


    -- test data --
    insert into Addresses (AccountID, Name1, Name2, Address1, country)
    select 1, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all
    select 2, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all
    select 3, 'Blow', 'Joe', 'Smithson Street 3540', 'D' union all
    select 4, 'Blow', 'Joe', 'Smithson Street 3540', 'D' union all
    select 5, 'Blow', 'Joe', 'Smithson Street 3540', 'D' union all
    select 6, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all
    select 7, 'Blow', 'Joe', 'Smithson Street 3540', 'F' union all
    select 8, 'Blow', 'Joe', 'Smithson Street 3540', 'F' union all
    select 9, 'Blow', 'Joe', 'Smithson Street 3540', 'F' union all
    select 10, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all
    select 11, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all
    select 12, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all
    select 13, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all
    select 14, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all
    select 15, 'Blow', 'Joe', 'Smithson Street 3540', 'A' union all
    select 16, 'Blow', 'Joe', 'Smithson Street 3540', 'A' union all
    select 17, 'Blow', 'Joe', 'Smithson Street 3540', 'US' union all
    select 18, 'Blow', 'Joe', 'Smithson Street 3540', 'HR' union all
    select 19, 'Blow', 'Joe', 'Smithson Street 3540', 'GB' union all
    select 20, 'Blow', 'Joe', 'Smithson Street 3540', 'GB' union all
    select 21, 'Blow', 'Joe', 'Smithson Street 3540', 'GB'


    -- the offending code is here: ---------------

    declare @newLPN int;
    declare @currentID int;
    set @newLPN = 0;
    set @currentID = 0;

    DECLARE cursorlpn CURSOR FOR
    select AccountID from Addresses
    where country = 'HR';

    open cursorlpn

    fetch next from cursorlpn into @currentID

    while (@@fetch_status = 0)
    begin
    insert into lpn (deliveryID) values (0);
    set @newLPN = scope_identity();
    update Addresses set lpn = @newLPN where AccountID = @currentID;

    fetch next from cursorlpn into @currentID
    end

    close cursorlpn
    deallocate cursorlpn


    Post #1515023
    Posted Sunday, November 17, 2013 3:34 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 8:43 AM
    Points: 7,042, Visits: 12,971
    I can't reproduce the scenario you described.
    Based on your sample data the code runs as expected (I'm purposely not saying it runs "fine" ).

    If you already tried several filters (including LOCAL STATIC FORWARD_ONLY) and you still don't want to rewrite it as a set-based solution, you could try to narrow-down the root-cause by assigning a NEWID() to the outer sproc and include that column into the lpn table. Therewith you'd be able to verify that the sproc itself is going crazy or if it's called multiple times.

    My question reagrding the process in general:
    Why isn't there any WHERE condition to ensure that only rows without an already assigned lpn value will be selected?

    What I've seen when runnig your code: If you call it twice, then it will update all rows with country = 'HR' even though those rows already have an lpn value assigned. Is that intentionally?




    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question
    How to post performance related questions
    Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
    Post #1515029
    Posted Sunday, November 17, 2013 3:59 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Yesterday @ 8:58 PM
    Points: 36,794, Visits: 31,253
    Based on a "gut" feeling and previous experience with "run away updates", try replacing the UPDATE statement in your code with the following and let us know what happens.

     UPDATE addr
    SET lpn = @newLPN
    FROM dbo.Addresses addr
    WHERE AccountID = @currentID
    ;




    --Jeff Moden
    "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1515030
    Posted Sunday, November 17, 2013 4:13 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 8:43 AM
    Points: 7,042, Visits: 12,971
    Wait a minute, Jeff!
    You suggest to "simply" add a FROM clause to the update statement? (The alias isn't mandatory, is it?)
    Interesting, what kind of "previous experience" you can refer to... (I'm not sure if I'm supposed to feel glad not to have faced such a situation. Yet!?! )




    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question
    How to post performance related questions
    Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
    Post #1515031
    « Prev Topic | Next Topic »

    Add to briefcase 12345»»»

    Permissions Expand / Collapse