Timeout errors when execution long running procedure

Last Post 21 Apr 2007 01:50 AM by jagsandhu. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sachavieuxroy
New Member
New Member

--
20 Apr 2007 07:31 PM
When I execute a long running procedure, I get timeout errors when other users try to execute other procedures with UPDATE or INSERT statements.



I suspect that the other procedures are trying to execute DML statements on tables that are locked by the long running procedure.



I have a sharred trigger on all my tables that creates and updates records in tables AuditLogDetails and AuditLogParent for keeping a log of modifications. I suspect that tables AuditoLogDetails and AuditLogParent are locked by the long running procedure.



How can I change the LOCKING behavior of the long running procedure to fix the time out errors that I get?

ALTER PROCEDURE [dbo].[spPostPresenceToHistory2]

@PostDate DateTime,

@Department Int,

@Division Int,

@Testing Bit = 0,

@XDoc xml OUTPUT,

@XDoc2 xml OUTPUT,

@ModifierID varchar(20),

@Comment varchar(200)

AS

BEGIN

BEGIN TRANSACTION

DECLARE @PostCount Int,@PreCount Int,@DiffCount Int

IF @Testing=1

BEGIN

PRINT 'DELETE FROM History2_Presence'

EXEC sp_SetPostingProperties 'History2_Presence',@ModifierID,@Comment

SELECT @PreCount=COUNT(*) FROM History2_Presence

IF EXISTS(SELECT E.ID FROM History2_Personel E WHERE E.PostDate=@PostDate)

BEGIN

DELETE FROM History2_Presence FROM History2_Presence H

INNER JOIN History2_Personel Ps ON H.Personel_ID=Ps.ID AND Ps.PostDate=@PostDate

WHERE Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division AND H.Date_de_Presence=@PostDate

AND EXISTS (SELECT P.ID FROM Presence P

WHERE (P.Date_de_Presence=@PostDate AND P.Personel_ID=H.Personel_ID AND P.Travaille_de_Jour=H.Travaille_de_Jour) OR (P.ID=H.ID))

END

ELSE

BEGIN

DELETE FROM History2_Presence FROM History2_Presence H

INNER JOIN Personel As Ps ON H.Personel_ID=Ps.ID

WHERE Ps.Category_Department_ID=@Department AND Ps.Category_Division_ID=@Division AND H.Date_de_Presence=@PostDate

AND EXISTS (SELECT P.ID FROM Presence P

WHERE (P.Date_de_Presence=@PostDate AND P.Personel_ID=H.Personel_ID AND P.Travaille_de_Jour=H.Travaille_de_Jour) OR (P.ID=H.ID))

END

SELECT @PostCount=COUNT(*) FROM History2_Presence

IF @PreCount<>@PostCount

BEGIN

SET @DiffCount = @PreCount-@PostCount

SET @XDoc2.modify('

insert <Table Name="History2_Presence" RecordDeleted="{ sql:variable("@DiffCount") }"/> as last into /Deleted_Records[1]

')

END

END

PRINT 'INSERT INTO History2_Presence'

EXEC sp_SetPostingProperties 'History2_Presence',@ModifierID,@Comment

SELECT @PreCount=COUNT(*) FROM History2_Presence

INSERT INTO [dbo].[History2_Presence]

([ID]

,[User_ID]

,[Personel_ID]

,[Date_de_Presence]

,[Category_Motif_ID]

,[DateEntre]

,[Category_TypeDePresence_ID]

,[Travaille_de_Jour]

,[Heur_Supplementaire_Travaille]

,[prime_transport]

,[Tarif]

,[Jour_Travaille]

,[Montant_Supplementaire_Par_Heur]

,[Salair_par_Jour]

,[Salair_Minimum]

,[IsAutomaticRec])

SELECT [P].[ID]

,[P].[User_ID]

,[P].[Personel_ID]

,[P].[Date_de_Presence]

,[P].[Category_Motif_ID]

,[P].[DateEntre]

,[P].[Category_TypeDePresence_ID]

,[P].[Travaille_de_Jour]

,[P].[Heur_Supplementaire_Travaille]

,[P].[prime_transport]

,[P].[Tarif]

,[P].[Jour_Travaille]

,[P].[Montant_Supplementaire_Par_Heur]

,[P].[Salair_par_Jour]

,[P].[Salair_Minimum]

,[P].[IsAutomaticRec]

FROM [dbo].[Presence] AS P

INNER JOI
jagsandhu
New Member
New Member

--
21 Apr 2007 01:50 AM
Hi Olu,

Hows it going.

I knew you from SQLmag and then discovered on joining DB that you worked there.



Jag

jagsandhu
New Member
New Member

--
21 Apr 2007 01:56 AM
I got the emal Olu thanks.
Oh by the way I am well.

Hows things at Barcap.
jagsandhu
New Member
New Member

--
21 Apr 2007 01:58 AM
I have also sent you an email. Please check if you got it.
jagsandhu
New Member
New Member

--
21 Apr 2007 02:10 AM
Hi Olu

in case youdidn't get my email. my email address is deleted,
keep in touch.
Jag
SQLUSA
New Member
New Member

--
05 Dec 2007 07:09 AM
Peak hours 100% spin is not unusual.

There are several ways to approach it:

1. SQL Server Profiler monitoring for slow queries and sprocs - optimization
2. Usually 1 solves it, if not, disk usage optimization
3. Final solution, Memory, CPU, & disk upgrade

Kalman Toth, Business Intelligence Architect
SQL Server 2005 Training - http://www.sqlusa.com
You are not authorized to post a reply.

Acceptable Use Policy