Could you show us your execution plan? Maybe you just need an index or reorganize if they have one
I told my customer to log the "Showplan" with his sqlprofiler. Hopefully the timeout will occur again today so that i can check this. I can't reproduce the timeout on our testmachines...
//Why you get different behaviour, I don't know, but one guess is that the SqlCommand object in the code where you set
the state to 32 has CommandTimeout set to 0 or a //higher value, and the other SqlCommand object has not.
I'm using a OR-Mapper-tool Lightspeed which is using a SQLCommand in the background. The two statements are executed right after the other and I'm using the same unitOfWork (thats the object which creates and executes my statements ). So this has to be the
same timeout-value.
// It seems that there is a blocking problem. You do have an index on DeliveryID, don't you?
hm...you are right. i just checked it on the server and there are several indexes but non for the deliveryId column...thanks
for this hint! I will create an index if my customer will allow me that ;)
But even if the index will speed up the hole statement-execution I have another statement which usually needs 1-2 seconds that also runs into a timeout from time to time (one day
it works fine, another day it takes more than 30 seconds..-->Timeout)
I just saw, that i didn't post the whole statement:
here is the correct one:
'created by my OR-mapper
exec sp_executesql N'UPDATE
Document
SET
State = @p2
WHERE
(Document.DeliveryId = @p0 AND Document.State = @p1)',N'@p0 uniqueidentifier,@p1 int,@p2 int',@p0='3E9AFBD9-6360-4551-8CCE-1D4E0C700F74',@p1=32,@p2=30
'same but easier to read
UPDATE Document SET State = 30 WHERE (Document.DeliveryId = @p0 AND Document.State = '3E9AFBD9-6360-4551-8CCE-1D4E0C700F74')
This statement usually takes only 1-2 seconds (the state-column has an index)...
here is the second statements which cause a timout exception:
'original statement from my OR-Mapper
exec sp_executesql N'UPDATE
Document
SET
State = @p4
WHERE
((((Document.State = @p0 OR Document.State = @p1) AND Document.LetterClosedDate IS NOT NULL) AND Document.DeliveryTypeNo = @p2) AND Document.SendLetter = @p3)',N'@p0 int,@p1 int,@p2 int,@p3 bit,@p4 int',@p0=20,@p1=23,@p2=10,@p3=1,@p4=32
'same but easier to read
UPDATE Document SET State = 32 WHERE
(
(
((Document.State = 20 OR Document.State = 23) AND Document.LetterClosedDate IS NOT NULL)
AND Document.DeliveryTypeNo = 10
)
AND Document.SendLetter = 1
)
usually it takes about 10 seconds (too long right ;)) and sometimes it takes over 30 seconds...
