none
Exception when querying data: Timeout expired RRS feed

  • Question

  • Using C#, System.Data, to access SQL Server 2005.

     

    Error message:

    Exception when querying data: Timeout expired.  The timeout period elapsed prior
     to completion of the operation or the server is not responding.

     

    SQL Query (LINQ Generated):

     

    Code Snippet

    SELECT [t1].[size], [t1].[name], [t1].[lastUpdated]
    FROM (
        SELECT COUNT(*) AS [value], [t0].[name], [t0].[lastUpdated], [t0].[size]
        FROM [dbo].[FileTable] AS [t0]
        WHERE ([t0].[size] IS NOT NULL) AND ((CONVERT(Float,[t0].[size])) > @p0) AND
     ([t0].[lastUpdated] IS NOT NULL) AND ([t0].[lastAccessed] IS NOT NULL)
        GROUP BY [t0].[size], [t0].[name], [t0].[lastUpdated]
        ) AS [t1]
    WHERE [t1].[value] > @p1

     

     

     

    Connection String:

     

    integrated security=SSPI;user id=administrator;password=password;data
    base=FileListDB;data source=localhost;connection timeout=60

     

     

    It times out at EXACTLY 30 seconds each time.

     

    I have tried the exact same query against a small set of data in the server, and it executes fine, returning correct results.  When I have more data (several million records), it takes longer to process, and dies.

     

    I have executed the query within the SQL Server 2005 itself (clicked new query, cut and paste above query), and it worked fine, however it took nearly a minute before results started comming back.

     

    I think I need some way to make it wait longer before timing out.

     

    I also confirmed that DataContext.Connection.ConnectionTimeout = 60

     

    Thank you.

    Sunday, June 24, 2007 9:38 PM

Answers

  • By default when you execute any command if it does not complete in 30 seconds, then provider throws Timeout exception. You could change it setting CommandTimeout property of SqlCommand to desired value or to 0 to wait until SQL statement finishes. But I believe the main issue here is the actual SQL statement. Try to run it in Management Studio with the Query Execution plan enabled and see where it spends most of the time. It is quite possible that [t1].[value], [t0].[size], [t0].[name], [t0].[lastUpdated]
     fields is not indexed and it leads to the full table scan, affecting performance of the query
    Monday, June 25, 2007 10:43 AM
    Moderator

All replies

  • By default when you execute any command if it does not complete in 30 seconds, then provider throws Timeout exception. You could change it setting CommandTimeout property of SqlCommand to desired value or to 0 to wait until SQL statement finishes. But I believe the main issue here is the actual SQL statement. Try to run it in Management Studio with the Query Execution plan enabled and see where it spends most of the time. It is quite possible that [t1].[value], [t0].[size], [t0].[name], [t0].[lastUpdated]
     fields is not indexed and it leads to the full table scan, affecting performance of the query
    Monday, June 25, 2007 10:43 AM
    Moderator
  • Yup, this appears to be a known 'limitation' with LINQ where if a command takes longer then 30 seconds, it errors out.

     

    Fortunately it seems, the next version of LINQ to SQL will support a .CommandTimeout property that will let me set it to higher then 30 seconds.

     

     

    Friday, July 13, 2007 12:50 AM