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 5:32 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 35,769, Visits: 32,439
LutzM (11/17/2013)
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!?! )


Oddly enough, yes... that's pretty much what I'm suggesting. And update the alias like I did instead of the table. It was an undocumented trick in older versions of SQL Server long before the documentation in Books Online caught up with it and it was also the easiest way to do a self-joined update using two aliases for the same table in the FROM clause.

The reason I'm suggesting it is because we discovered, quite by accident, that it can make substantial improvements in performance by giving the optimizer better direction through code. Normally this problem has only appeared on improperly formed JOINed updates where the target table wasn't included in the FROM clause but was included in the WHERE clause. And when I say "appeared", I mean appeared in Spades. My first experience (more years ago) with the problem caused a normally 20 second update to pin 4 CPUs to the proverbial wall for two hours!

A long time ago, certain folks took exception to me blaming this type of problem on "Halloweening" because MS supposedly has code behind the UPDATE statement to prevent it, but it sure does have all the ear marks of "Halloweening" especially since this particular problem in generating thousands of unwanted rows.


--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 #1515043
Posted Sunday, November 17, 2013 6:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 10:02 AM
Points: 14, Visits: 49
Lutz - I am going to rewrite it set-based, sure.
As I see it, cursors are really meant for interaction between SQL Server
and applications which just cannot understand set-based operations.
Not within a stored procedure.

As I said, I didn't write it, I am having to dig into it as it suddenly blocked
the application.

To your other points - the sproc is not being called multiple times, I have
verified this.
I did execute it directly from Mgmt Studio and it did the same - it never returns for
minutes, meanwhile adding hundreds of thousands of records to the lpn table.

I already created a new procedure with the same code, just to be sure
that the sproc object isn't corrupt in some way. Didn't change a thing.

Good idea to put a WHERE condition.
There SHOULD not be a point trying to filter the records - based on what
the app is doing with it. Yes, if you run this twice, it will update these
records again. (The 'HR' is my rewrite, it is a parameter of the sproc
really). This is "allowable" in the application because t it won't ever
(presumably) call the sproc with the same parameter twice.

Jeff - yes I can put the FROM Addresses in the query, but it doesn't
change the behaviour. It also should not.

I'll solve the problem by rewriting. It still bugs me why on earth this
seemingly innocent code just overnight decided to mess up?

Post #1515047
Posted Sunday, November 17, 2013 7:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 7,179, Visits: 15,775
Are you SURE nothing changed in the code?

The likely culprit looks to be here:

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


I'm making a giant assumption, but how many addresses have the same accountID? Not sure what kind of account this might be- but the accounts where I work have many addresses. Assuming yours did too, then the cursor would "loop" for each duplicated accountID in your addresses table.


To check for duplicates:
select accountID, count(*) from addresses where 
country = 'HR'
group by accountid
having count(*)>1

Edit: fixed the missing GROUP BY clause.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1515052
Posted Sunday, November 17, 2013 9:22 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 35,769, Visits: 32,439
Peter Schulz-485500 (11/17/2013)
Jeff - yes I can put the FROM Addresses in the query, but it doesn't
change the behaviour. It also should not.

I'll solve the problem by rewriting. It still bugs me why on earth this
seemingly innocent code just overnight decided to mess up?



Thanks for the feedback, Peter. It was a shot in the dark from successes for slightly similar problems where such things go crazy on their own.


--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 #1515059
Posted Monday, November 18, 2013 10:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 6,847, Visits: 13,388
Is there any way you can provide a setup that could be used to reproduce the odd behavior of the sproc on a standalone machine (meaning that we can test against)?
There has to be a reason for the repetitive call.
And I agree: it's always good to know WHY it occured instead of just making it disappear by "just" rewriting the code...




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 #1515313
Posted Monday, November 18, 2013 1:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:46 AM
Points: 2,734, Visits: 943
Strange behaviour happens with update cursors.

First, make sure triggers are not firing in those tables, triggers are the best away to create bugs and strange behaviours in legacy systems.
Second, caution with null! If there are some rows with null values at AccountID you ill get a bad day at work.
Third, to catch infinite loops try to select to a temp table and fetch from that temp table, it can ill give you some insight about the data the cursor actually fetched and updating it ill no affect that fetched rows.
Finally, after you fix the bug (understand why this happens) try to rewrite that query to get rid of that cursor.

Post #1515334
Posted Monday, November 18, 2013 1:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 30, 2014 10:02 AM
Points: 14, Visits: 49
jcb - thanks, I checked, in fact there are no triggers anywhere in the database. Actually in the production database the AccountID column is an Identity column, sorry, I did not specify this in my sample code.

Lutz, I'll try to set up a test environment. So far I did not succeed. The weird behaviour did not occur when I set up any test environment. Only thing I am sure is that the sproc is called only once, it's not a repeated invocation, it does the turn-around all by itself.

Anyone knows if VB-6 code may be the culprit if it retrieves recordsets and then loops through these via Recordset.MoveNext and so on? That these somehow get counted by SQL Server in the same @@fetch_status variable and thus trick SQL Server into believing fetch_status is always zero?
(Still does not explain why it then turns around to the first row and starts counting anew though)

The apps running here are using VB-6 recordsets extensively, it looks even like they are hardly doing anything else ... As it is a distributed application with various parts running on different stations which must be online almost constantly, I have a bit of a hard time disconnecting everybody. But I'll try this next.
Post #1515348
Posted Monday, November 18, 2013 5:23 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 35,769, Visits: 32,439
Peter Schulz-485500 (11/18/2013)
I have a bit of a hard time disconnecting everybody. But I'll try this next.


Disconnecting everyone and everything is the easy part. Just make sure that you don't lose the connection after you run the following or some nasty ol' web service will snatch up the only connection available.

USE [master]
GO
ALTER DATABASE [YourDatabaseNameHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;


If you're sure that high prived users/apps/servers aren't going to barge in, then you could move to a safer multi-connection mode that still keeps most people out and lets you back in if you lose the connection.

USE [master]
GO
ALTER DATABASE [YourDatabaseNameHere] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;


When you're done, let everyone else back in.

USE [master]
GO
ALTER DATABASE [YourDatabaseNameHere] SET MULTI_USER;




--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 #1515392
Posted Tuesday, November 19, 2013 9:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:46 AM
Points: 2,734, Visits: 943
VB6 argh... it smells as a old poor designed legacy app.


Give it a look.
http://technet.microsoft.com/pt-br/library/ms187308.aspx

"Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully."

Other possible problems are:

someone changed the isolation level and that fetched data is dirty, the application was poor designed and cannot handle some unexpected weird data like duplicated rows.


Post #1515642
Posted Tuesday, November 19, 2013 7:11 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 35,769, Visits: 32,439
jcb (11/19/2013)
VB6 argh... it smells as a old poor designed legacy app.


Hey, Hey, HEY! I'm old, I used to be a great VB6 programmer, I AM a legacy app, and I'm RIGHT HERE!


--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 #1515861
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse