Strange timeout problem

Hello, i'm using lightspeed (v4.0) and i'm running into a strange timeout problem:

Im executing an Query like

using (var unitOfWork = context.CreateUnitOfWork())
            {
                var query = new Query(typeof(Document), Entity.Attribute("DeliveryId") == deliveryId && Entity.Attribute("State") == State.IsBeingDelivered);

                var attributes = new Dictionary<object, object> { { "State", State.DeliverySuccessful } };

                unitOfWork.Update(query, attributes);

                unitOfWork.SaveChanges(true);

            }

Sometimes (almost one a day) I'm running into a timeout-exception after exactly 30 seconds. (System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.)

The strange thing about this is that after I get this exception I execute almost the same statement with just another State (State.DeliveryFailed) on the same unitOfWork and this runs about 90 seconds (!!).

Why is the fist statement throwing the timeoutexception after 30 seconds and the second statement can run for more than 90 seconds??

Is Lightspeed changing the timeout-property after a timeout-exception occurs?

can you help me here?

Thanks a lot!

LightSpeed does not adjust the timeout value. The difference between the two cases may simply be that the first one runs slightly too long for an individual database request and therefore throws the exception where as the second one does not trigger a timeout but in running the whole request has multiple queries and collectively these are adding up to 90 seconds of request time.

Either way this sounds like some very long running requests! Have you looked at what is occurring with these queries by running up a profiler to understand what is going on? It sounds like you have a long running query or are encountering a blocking issue coming from lock contention with multiple connections/queries.

I would first get an clear understanding of what is being executed for each request and then look at running the queries manually to look at what is occurring for each query. Sometimes if you have a long running query it may be a case where you are missing an index in your database and once added you can dramatically improve query performance by avoiding a costly table scan. Also you might want to have a look at integrating something like MiniProfiler - http://miniprofiler.com/ - into your site to help track request times, you can integrate this with LightSpeed by following the instructions here: http://www.mindscapehq.com/blog/index.php/2011/07/28/using-the-asp-net-mvc-mini-profiler-with-lightspeed/ to trace the queries performed by LightSpeed.

Jeremy

Since I suspect this will likely be a blocking issue since you are performing an update, have a read through this KB article about diagnosing blocking issues on SQL Server: http://support.microsoft.com/kb/224453

Jeremy

hi jeremy, thanks for your answer!

I guess I have to set an index to "Document.DeliveryId" and "Document.State" to get a much better performance.

LightSpeed does not adjust the timeout value. The difference between the two cases may simply be that the first one runs slightly too long for an individual database request and therefore throws the exception where as the second one does not trigger a timeout but in running the whole request has multiple queries and collectively these are adding up to 90 seconds of request time.

ok, lightspeed doesn't adjust the timeout. But I'm executing almost the same statement (see below). You can see the Lightspeed-statement and the "easier to read"-version ;) the only difference is the state (30 and 31). Any more ideas why one statement is getting a timeout and the second is running for 90 seconds?

'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 = '3E9AFBD9-6360-4551-8CCE-1D4E0C700F74' AND Document.State = 32)

and here is the second which works without a timeout:

'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=31


'same but easier to read
UPDATE Document SET State = 31 WHERE (Document.DeliveryId = '3E9AFBD9-6360-4551-8CCE-1D4E0C700F74' AND Document.State = 32)

thanks for your help !

Try executing these manually via Management Studio - how long are the queries themselves taking? When inspecting the actual queries via profiler, how long does it indicate the queries are taking there?

My assumption is that there is something else which is occuring during the request but the individual queries are not timing out on the second request but for the first one you are hitting a timeout on a specific call. But if thats not the case then presumably you should be seeing a ~90 second query when running this via Management Studio.

Did you look into if there is any blocking occuring? 90 seconds is a very long time for an update!!

Jeremy