SP RUNS ONCE THEN GETS STUCK IN A LOOP
-
Hi,
has anyone ever encountered this problem where they have a stored procedure which runs perfectly fine the first time you run it(takes 14 seconds) but say if i try and run it again afterwards (i.e. 2 mins or 2 hours later) it seems to get stuck in a loop and i have to cancel the job and then start it again which basically means the job is useless.
The stored procedure its running is simply updating a view.
Any ideas?
kind regards
-
Does your stored procedure have parameters, and if so, are you passing different parameters in the second time? It's always helpful if you post your code, by the way.
John
-
Haven't seen that, but there are possibilities for what's wrong. WITH CHECK in the view definition, data changes, etc.
Can you post some code? Is there looping in the view?
-
The stored procedure itself simply updates a column with a corresponding column in the view ala...
UPDATE vw_CostPriceUpdate_Update
SET Cost =[UK Cost]
thats all the sp does.
the strange thing is that this works fine on another server with the only difference i can see so far is that under current activity in the locks/proccesses id the one which isn't working creates a new process an additional process to handle running the tempdb database however on the server that works it runs this tempdb database process within the same process as the update procedure is called.
And my first thought was that after the first instance it wasn't deleting all the locks it made after it finished but it is.
-
Also if iw as to just run a really basic stored procedure ala...
print 'hello'
or write to the events log
then this would work fine.
-
it looks like you are actually updating a view, rather than directly accessing the underlying tables:
UPDATE vw_CostPriceUpdate_Update
SET Cost =[UK Cost]
if you do sp_helptext vw_CostPriceUpdate_Update, i bet the definition is a bit more complex, and is the cause of your problem.
if the statistics are outdated on the production box, but not on the other server as you noted, you'd probably see a performance hit, as the executaion plan might be outdated due to statistics.
you would problalby be able to directly update one of the underlying tables of the view to avoid the performance hit of the updatable view.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Good catch, Lowell...
I've seen it where the view totally recalculates for each individual update... kinda drags performance down a bit
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
this is the definition, not very complex.
CREATE VIEW dbo.vw_CostPriceUpdate_Update
AS
SELECT dbo.vw_CostPriceUpdate_UK.RECORD_NUMBER AS [UK Record Number], dbo.vw_CostPriceUpdate_UK.DEPOT AS [Depot UK],
dbo.vw_CostPriceUpdate_UK.PART AS [Part UK], dbo.vw_CostPriceUpdate_UK.COST AS [UK Cost],
dbo.vw_CostPriceUpdate_Non_UK.RECORD_NUMBER, dbo.vw_CostPriceUpdate_Non_UK.DEPOT, dbo.vw_CostPriceUpdate_Non_UK.PART,
dbo.vw_CostPriceUpdate_Non_UK.COST
FROM dbo.vw_CostPriceUpdate_Non_UK INNER JOIN
dbo.vw_CostPriceUpdate_UK ON dbo.vw_CostPriceUpdate_Non_UK.PART = dbo.vw_CostPriceUpdate_UK.PART
also that sounds right but would that be correct as it works fine the first time you run it but its the second time that it seems to get caught in a loop and never ends. the first time takes 15 seconds and i have left the second time running for over 21 hours so it's really annoying.
-
Additional notes:
Running SQL Server 2000 (SP4)
The view i'm calling the update on is on open query views of a linked server
-
I think the update is a little deeper, you name your views just like I do with "vw_" preceeding it,and that makes me think you need to take look a the update in profiler.
the update sets cost=[UK COST]; in your example, since there's no WHERE statement, it is going to affect every record. That's going to require a table scan on the underlying tables, right? If the tables are huge, and statistics are a bit out, then the execution plan might literally take forever as it goes down the wrong decision path.
The view you are updating is a view of another view. the sql statement is going to have to resolve vw_CostPriceUpdate_Non_UK.COST in order to update as well, which adds to the overhead.
if you update statistics , I believe the update would go through faster. I've had a similar performance hit with view-of-a-view for SELECT statements, but designwise I haven't tried very many updatable views.
CREATE VIEW dbo.vw_CostPriceUpdate_Update
AS
SELECT
dbo.vw_CostPriceUpdate_UK.RECORD_NUMBER AS [UK Record Number],
dbo.vw_CostPriceUpdate_UK.DEPOT AS [Depot UK],
dbo.vw_CostPriceUpdate_UK.PART AS [Part UK],
dbo.vw_CostPriceUpdate_UK.COST AS [UK Cost],
dbo.vw_CostPriceUpdate_Non_UK.RECORD_NUMBER,
dbo.vw_CostPriceUpdate_Non_UK.DEPOT,
dbo.vw_CostPriceUpdate_Non_UK.PART,
dbo.vw_CostPriceUpdate_Non_UK.COST
FROM dbo.vw_CostPriceUpdate_Non_UK
INNER JOIN dbo.vw_CostPriceUpdate_UK ON dbo.vw_CostPriceUpdate_Non_UK.PART = dbo.vw_CostPriceUpdate_UK.PART
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
That seems valid but it's identical on another server and works fine there. Does it also explain why it works fine the first time but fails the second time.
Also an additional note, this server recently changed domain name and i've been through all permissions settings etc and they seem fine although is there anything in this that could affect it somewhere.
-
Seems to me it would have to be something in the data that is being used to do the update. What is different between the first and second time it is run?
-
Absolutely nothing is different it's really perplexing as to why it fails the second time you run it. Although looking at the processes the update processes status is set to sleeping and wait type as exchange if that has any bearing.
-
Yet when it works fine the status is Runnable and the wait type is OLEDB
-
In researching it could be linked to this problem with parallel processes as that's why am getting the Exchange (Wait-Type)
http://www.dbforums.com/t656327.html
http://www.dbforums.com/showthread.php?threadid=529043
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply