SQL Server Performance

Time out Expired..command time out

Discussion in 'T-SQL Performance Tuning for Developers' started by ramesh, Feb 10, 2004.

  1. ramesh New Member

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

    I am getting the above error when i run the web application using ASP.NET. When i debug the application, i found that there is problem with the query timeout. I have increased the commandtimeout to 20 minutes instead of default(30 seconds) and was able to run the application successfully.

    Is this the correct solution or can anyone suggest if you have any alternative solution?

    Here is a bit of code of my Data Access Layer class.

    ***********************************************************************************
    'Get the connection from Web.config
    conObject.ConnectionString = Configuration.ConfigurationSettings.AppSettings (strConnString)
    conObject.Open()

    dapObject = New SqlDataAdapter(strSpName, conObject)
    cmdObject.CommandType = CommandType.StoredProcedure
    cmdObject.Connection = conObject
    cmdObject.CommandTimeout = 20 -- this has been set to avoid query time out
    cmdObject.CommandText = strSpName
    ***********************************************************************************

    Appreciate your help.

    Best Regards,
    Ramesh
  2. Chappy New Member

    run the very same stored procedure from query analyser and see how long it takes. I suspect the problem is not in your connectivity but in the performance of the procedure.
  3. ramesh New Member

    I am inserting 1 million records into a table as below

    Insert into tableA
    select A1,A2,A3 from tableB

    If i don't use the commandtimeout then the above query will timeout and if i set the commandtimeout to more than 30 secs then the query will run fine..

    Can you suggest on the above explanation?

    Ramesh
  4. Chappy New Member

    what indices are on tableA, if any ?
  5. Twan New Member

    you have actually decreased the timeout from 30 seconds to 20 seconds... 20 minutes would be 1200 seconds and so cmdObject.CommandTimeout = 1200 would be needed

    It is common to have to increase the commandtimeout for long running sql batches, however don't just increase it for everything as it can cause problems sometimes when there a communication issues and the client is left waiting for 20 minutes

    Cheers
    Twan
  6. ramesh New Member

    There are no indexes on the table A.

    I realised after posting the code.Twan, you are correct it is 1200 seconds. Any how i am setting the session timeout as 20 minutes for the Web users.

    Thanks
    Ramesh
  7. rage1973 New Member

    I would advise against setting any timeout to 20 mins. You could have a rogue code that can set exclusive locks which is just gonna hold onto the lock for the duration until it timeouts. I would set it to maybe 30 secs more than the slowest time it took to run the stored proc just to give it some cushion.

Share This Page