none
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. RRS feed

  • Question

  • I'm doing a LINQ query and I'm getting the following exception after about 30 seconds (con.CommandTimeout is set to 30):

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    When I run the same query within SQL Server, it reports that it takes only 2 seconds.  The LINQ query I'm doing is:

                    var query =
                     (from se in con.ScheduledEvents
                      join dss in con.DocentShiftSchedules on se.EventId equals dss.EventId
                      into a
                      from b in a.DefaultIfEmpty()
                      join d in con.DocentInfos on b.DocentAssigned equals d.Index
                      into x
                      from y in x.DefaultIfEmpty()
                      where
                            se.Day >= startDate.Date &&
                            se.Day <= endDate.Date
                      orderby se.Day,
                              se.OutReach ascending,
                              se.ShiftName,
                              se.TimeStart,
                              se.EventId,
                              b.ShiftCaptain descending,
                              y.NVFlag ascending,
                              y.LastName,
                              y.FirstName
                      select new MainScheduledEventData
                      {
                          EventId = se.EventId,
                          Day = se.Day,
                          TimeStart = se.TimeStart,
                          TimeEnd = se.TimeEnd,
                          NumberDocentsRequired = se.NumberDocents,
                          NumberDocentsNeeded = 0,
                          DisplayName = y.DisplayName,
                          FirstName = y.FirstName,
                          LastName = y.LastName,
                          NVFlag = y.NVFlag,
                          ShiftCaptain = b.ShiftCaptain,
                          ShiftName = se.ShiftName,
                          Comment = se.Comment,
                          DocentIndex = y.Index,
                          AssignmentId = b.Index
                      });
                    events = query.ToList();

    Which seems to translate to the following SQL:

    SELECT [t2].[DisplayName] AS [DisplayName], [t2].[FirstName] AS [FirstName2], [t2].[LastName] AS [LastName2], [t2].[NVFlag] AS [NVFlag2], [t1].[ShiftCaptain] AS [ShiftCaptain2], [t0].[EventId] AS [EventId2], [t0].[Day], [t0].[TimeStart], [t0].[TimeEnd], [t0].[NumberDocents] AS [NumberDocentsRequired], [t0].[ShiftName], [t0].[Comment], [t2].[Index] AS [DocentIndex], [t1].[Index] AS [AssignmentId]
    FROM [dbo].[ScheduledEvent] AS [t0]
    LEFT OUTER JOIN [dbo].[DocentShiftSchedule] AS [t1] ON ([t0].[EventId]) = [t1].[EventId]
    LEFT OUTER JOIN [dbo].[DocentInfo] AS [t2] ON [t1].[DocentAssigned] = ([t2].[Index])
    WHERE ([t0].[Day] >= @p0) AND ([t0].[Day] <= @p1)
    ORDER BY [t0].[Day], [t0].[OutReach], [t0].[ShiftName], [t0].[TimeStart], [t0].[EventId], [t1].[ShiftCaptain] DESC, [t2].[NVFlag], [t2].[LastName], [t2].[FirstName]
    

    I have indexes on the ScheduledEvent.Day, DocentShiftSchedule.DocentAssigned, and DocentShiftSchedule.EventId. The execution plan shows 56% of the cost in a SORT and  22% in a HASH MATCH (LEFT OUTER JOIN). The query returns 19126 rows. Sql Data Profiler shows the query taking 3.28 seconds of execution time but running for 32 seconds and has 1046317 "reads". 

    I understand I can increase the ConnectionTimeOut, but why is this query Timing Out in LINQ/C# while only taking 2-3 seconds on the SQL Server directly.

    Paul P.

    Saturday, July 28, 2012 3:09 PM

All replies

  • Hi Paul;

    The most likely cause is that when you execute the SQL statement in SQL Management tool it is not going through a network to get the SQL, execute it and return the results while executing from your program it is going through the network. Is the network that the SQL server is using being heavily used?

    Are queries that return a smaller result set work?

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Saturday, July 28, 2012 9:50 PM
  • My SQL Server (Express) is running on the same computer as the application is running on. And, the network has nothing else running on it. So, I don't think it is the network.


    Using a smaller date range (startDate and endDate) and results return quicker and without a problem (with 30 second timeout).
    • Edited by ZooGuy1492 Sunday, July 29, 2012 2:18 AM
    Sunday, July 29, 2012 2:08 AM
  • Hi Paul;

    Well seeming that the SQL connection is working with small data being return the connection setup must be OK.

    At this point I can think of two thing that may be at issue. The first being that when SQL send data back to the client application it uses a Tabular Data Stream Protocol or just TDS stream. This protocol uses a network packet size of 512 bytes. So if the data being returned is greater then this then multiple network packets will be sent back to the client. If the number of packets being sent back is large it may exceed the 30 second time limit. The second issue may be that the query that EF is creating may be not as efficient as a SQL that you can write in TSQL. To test this case is to execute a EF command directly against the data store with a command like the following:

    var query = con.ExecuteStoreQuery<MainScheduledEventData>("Your SQL Query Here As a String", null );

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Sunday, July 29, 2012 2:20 PM
  • I couldn't find a ExecuteStoreQuery method but I used ExecuteQuery and did this:

    string querySQL = "SELECT [t2].[DisplayName] AS [DisplayName], [t2].[FirstName] AS [FirstName], [t2].[LastName] AS [LastName], [t2].[NVFlag] AS [NVFlag], " +
    "[t1].[ShiftCaptain] AS [ShiftCaptain], [t0].[EventId] AS [EventId], [t0].[Day], [t0].[TimeStart], [t0].[TimeEnd], [t0].[NumberDocents] AS [NumberDocentsRequired], " +
    "[t0].[ShiftName], [t0].[Comment], [t2].[Index] AS [DocentIndex], [t1].[Index] AS [AssignmentId]" +
    "FROM [dbo].[ScheduledEvent] AS [t0]" +
    "LEFT OUTER JOIN [dbo].[DocentShiftSchedule] AS [t1] ON ([t0].[EventId]) = [t1].[EventId] " +
    "LEFT OUTER JOIN [dbo].[DocentInfo] AS [t2] ON [t1].[DocentAssigned] = ([t2].[Index]) " +
    "WHERE ([t0].[Day] >= '" + startDate.ToShortDateString() + "') AND ([t0].[Day] <= '" + endDate.ToShortDateString() + "') " +
    "ORDER BY [t0].[Day], [t0].[OutReach], [t0].[ShiftName], [t0].[TimeStart], [t0].[EventId], [t1].[ShiftCaptain] DESC, [t2].[NVFlag], [t2].[LastName], [t2].[FirstName] ";
    var query = con.ExecuteQuery<MainScheduledEventData>(querySQL);
    events = query.ToList();

    This query seemed to execute extremely quickly (around 2 seconds).  But my original query which was in LINQ took over 30 seconds. Why the difference?  Should I not be using LINQ?

    Also, why did my LINQ query result in: [t2].[FirstName] AS [FirstName2], [t2].[LastName] AS [LastName2], [t2].[NVFlag] AS [NVFlag2]?


    • Edited by ZooGuy1492 Sunday, July 29, 2012 11:51 PM
    Sunday, July 29, 2012 11:14 PM
  • I'm not sure what I did but my LINQ query is now taking about 2-3 seconds. So I guess everything is fine.
    Monday, July 30, 2012 1:20 AM
  •  

    To your question, "Also, why did my LINQ query result in: [t2].[FirstName] AS [FirstName2], [t2]. [LastName] AS [LastName2], [t2].[NVFlag] AS [NVFlag2]?", I do not know the answer to this question.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, July 30, 2012 12:57 PM