Suggestion in imporving a stored procedure updatin

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Suggestion in imporving a stored procedure updatin

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-03-20 : 02:58:01

I am using sql server 2008.
I have a stored procedure in which I am updating a field of my related tables.I have to do it in a transaction
Other then my first table my parameter fkJobID is just a part of a composite primary key
Problems is that 3 tables I might have upto 4 millions record.
I am getting command time out in some of updates.
Please suggest any imporovement in my stored procedure.

BEGIN
BEGIN TRANSACTION
Begin

Update ImportJob
Set imjIsdeleted = 1
where JobID = @JobID

Update Header
Set hdrIsDeleted = 1
where fkJobID = @JobID

Update Segment
Set segIsDeleted = 1
where fkJobID = @JobID

Update MailPieceUnit
Set mpuIsDeleted = 1
where fkJobID = @JobID

Update MailPieceUnitRelationShip
Set mcrIsDeleted = 1
where fkJobID = @JobID

Update MailerPostageAccount
Set mpaIsDeleted = 1
where fkJobID = @JobID

Update Component
Set cptIsDeleted = 1
where fkJobID = @JobID

Update ContainerSummary
Set csmIsDeleted = 1
where fkJobID = @JobID

Update ContainerQuantity
Set cqtIsDeleted = 1
where fkJobID = @JobID

Update PackageQuantity
Set pqtIsDeleted = 1
where fkJobID = @JobID

Update WalkSequence
Set wsrIsDeleted = 1
where fkJobID = @JobID

Update SeedName
Set snrIsDeleted = 1
where fkJobID = @JobID

Update PieceDetail
Set pdrIsDeleted = 1
where fkJobID = @JobID

Update PostageAdjustment
Set parIsDeleted = 1
where fkJobID = @JobID

Update IntelligentMailRange
Set imrIsDeleted = 1
where fkJobID = @JobID

Update OriginalContainerIdentification
Set ociIsDeleted = 1
where fkJobID = @JobID

Update PostageStatement
Set psrIsDeleted = 1
where fkJobID = @JobID

IF @@ERROR <> 0
SET @ErrorVar = -1
ELSE
SET @ErrorVar = 0

END
COMMIT TRANSACTION
END

GO

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Kristen
Test

22859 Posts

Posted - 2010-03-20 : 06:41:59

Is fkJobID indexed in EACH/ALL of those tables? If not that may help.

Shouldn't matter then that the table has millions of rows UNLESS "WHERE fkJobID = @JobID" is matching LOTS of rows.

NOTE:

IF @@ERROR <> 0 ... set errorvar ...

You need to do this after EACH Update, not once at the end. SET @ErrorVar = 0 at the beginging, then


IF @@ERROR <> 0
BEGIN
	SET @ErrorVar = -1
END

after each Update. Then at the bottom:


IF @ErrorVar = 0
BEGIN
	COMMIT
END
ELSE
BEGIN
	ROLLBACK
END

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-03-20 : 12:41:15

Thanks Kristen
I will try it after putting index

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources