UPDATE problem inside SQL Script

Question

Post reply

UPDATE problem inside SQL Script

Oracle_91

SSChampion

Points: 14548
More actions
August 3, 2010 at 8:15 am

#238429

Hi,
I have a T-SQL script to be executed as part of refresh.
This script is getting executed fine during the past.
We are trying execute the same script on testing layer but it is getting stuck up at one simple UPDATE statement based on join.
I do not know the reason, what was wrong? I tried to put the trace on and checked in profiler? it was stopping at UPDATE statement in STEP 3
But if pull the piece of code from the stored procedure and if i execute the step by step. it is getting executed fine.
But if we execute as a whole batch in the query analyszer it is again hanging at the same place ( i.e. UPDATE based on temp and actual table). I have few things in mind !!!!
Is there anything wrong in the transaction management ? Is there any problem with the JOIN? but is used to work fine previously!!!!
I tried to check is there any locks and if it is getting blocked or not. but no clue? Am attaching the script,screen shot where exactly it is causing problems and other observation screen shots. Please have a look.
Also, when i tried to cancell the process , it is taking more time and even if we kill the process, it is saying the process is in ROLLBACK state(sp_who2). I have attached the same "rollback_killed.GIF". It get rid of this, am restarting my sql server and again kick off the batch to start the investigation.
Can anyone figure out what is the problem?
Any help would be greatly appreciated.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Oracle_91 SSChampion Points: 14548 More actions · Answer 1

Oracle_91

SSChampion

Points: 14548

August 3, 2010 at 1:44 pm

#1201783

Can anybody figure out what is the problem ?????

Oracle_91 SSChampion Points: 14548 More actions · Answer 2

I have tried WITH NOLOCK option still remains the same.

handkot SSCertifiable Points: 5251 More actions · Answer 3

try this

UPDATE DBO.MEASURE

SET statustypeid = t.statustypeid

FROM #tempFinalMeasure t

WHERE

DBO.MEASURE.measureid=t.measureid AND DBO.MEASURE.statustypeid <> t.statustypeid

I Have Nine Lives You Have One Only
THINK!

Oracle_91 SSChampion Points: 14548 More actions · Answer 4

Good to see a response.:-)

The script was already on production from quite a long time.

Changing the code requires again approval and lot of stuff.

Before that , i have a question what if we specify the extra AND condition. At the same time , am not denying the logic what you suggesteed.

Basically, i want to know what is the problem in the existing T-SQL code.

Any thoughts?

Oracle_91 SSChampion Points: 14548 More actions · Answer 5

Adding to this, status codes are same based in the INNER join.

What if would make the difference if am updating the same value. Why it is hanging. What if i make put that AND condition makes the difference. Only logically we are filtering equilvalent rows. Right?

For example,

select t.cdid,cdi.cdid,t.status,cdi.status

from #tmp t INNER JOIN cdi on t.cdid=cdi.cdid;

1 1 A A

2 2 B B

3 3 C C

4 4 D D

handkot SSCertifiable Points: 5251 More actions · Answer 6

How many records in the table?

Check up statistics and indexes

I Have Nine Lives You Have One Only
THINK!

Oracle_91 SSChampion Points: 14548 More actions · Answer 7

400 records odd.

How would the statistics and indexes would help us on this regard?

Any ways, i will check with it.

Temp table is pretty straight

CREATE TABLE #temp

(CDIID INT,

STATUSTYPEID INT

)

CREATE TABLE dbo.Actual (

CDIID int IDENTITY (1, 1) NOT NULL ,

name varchar (60) NOT NULL ,

statusTypeID tinyint NOT NULL ,

modifiedBy int NULL ,

modifiedOn datetime NULL

)

GO

ALTER TABLE dbo.Actual ADD

CONSTRAINT CDI_PK PRIMARY KEY NONCLUSTERED

(

CDIID

) WITH FILLFACTOR = 90 ON PRIMARY

GO

handkot SSCertifiable Points: 5251 More actions · Answer 8

ok

problem is here

UPDATE CDI

SET statustypeid = t.statustypeid

FROM #tempCDI t

INNER JOIN DBO.CDI CDI ON t.cdiid=CDI.cdiid

1. how many records in the tables CDI and #tempCDI ?

2. how many reads and writes does this query (in Profiler)?

3. locks objects?

I Have Nine Lives You Have One Only
THINK!

Oracle_91 SSChampion Points: 14548 More actions · Answer 9

Table Rows

#tempCDI369

CDI5642

-- The below statisitics i gathered when i manually executed each statement by statement

But if i execute the whole script as batch , the UPDATE is getting hanged up and hence to reads / writes for that statment

CPU : 47

Writes : 65

Locks are applied on only CDI table : Sch-S and TAB level lock

Oracle_91 SSChampion Points: 14548 More actions · Answer 10

I updated the statistics but same result. it is stopping at UPDATE statement.

Oracle_91 SSChampion Points: 14548 More actions · Answer 11

I tried with AND condition. Also, updatestats and again tried to run the script. Still same result !!!

handkot SSCertifiable Points: 5251 More actions · Answer 12

i dont know

do this

create index ix1 on #tempCDI(CDIID)

and try again

I Have Nine Lives You Have One Only
THINK!