none
SQLCommand Timing Out When Run Multiple Times! RRS feed

  • Question

  •  

    Hi,

    I've created a small application which reports errors and gives examples. What the tool does it run one query to get a list of all the errors and then queries the db once for each error to get examples. My problem is that even though each of the small queries only takes, say 100 seconds to run, its as if the count is not being returned to 0!

     

    I'm putting my time out at 500 seconds and use the SqlConnection.CreateCommand method to create a new command for each time a new query is run (when querying for examples, the command are run in sequence to make sure the db isnt hit too hard) and the time out is assigned each time. The problem is that the command times out all the time around the 5th or 6th time. If I change the time out to 300seconds, then the command times out after querying for the 3rd example. I have concluded that either the command object isnt being disposed of (and thus the program is using the same one?) or when reassigning the timeout its not being taken.

     

    Has anyone experience this before?

    Thanks in advace!

    Wednesday, February 27, 2008 8:07 PM

All replies

  • (I have concluded that either the command object isnt being disposed of (and thus the program is using the same one?)

     

    The command objects don't get disposed because they don't implement the IDisposable interface you need to dispose of all your connections associated to your command object because only the connection object implements IDisposable interface in the dataset.

     

    Wednesday, February 27, 2008 8:18 PM
  • That is a very good point - one which I had overlooked! However, the code is as follows:

     

    Code Snippet

            ///
            /// The database connection for this instance; only one per instance.
            ///
            private SqlConnection DbConnection
            {
                get
                {
                    if (_dbConnection == null)
                        _dbConnection = new SqlConnection("Data Source=Noel;Database=" + _database + ";Integrated Security=SSPI;Asynchronous Processing=true");

                    return _dbConnection;
                }
            }
            private SqlConnection _dbConnection;

            ///
            /// The database command for this instance; only one per instance.
            ///
            private SqlCommand DbCommand
            {
                set
                {
                    lock (this)
                    {
                        _dbCommand = value;
                    }
                }
                get { return _dbCommand; }
            }
            private SqlCommand _dbCommand;

     

     

    The above are the properties that the following code uses:

     

    Code Snippet

    public List<string> GetExampleAggregationIds(string providerid, string errorMessage, DateTime dateFrom, DateTime dateTo, int numberOfExamples)
            {
                List<string> exampleIds = new List<string>();
                string errorQuery = @"  SELECT    <query text>";

               

                SqlDataReader reader = null;
                try
                {
                    // Get a handle on the command so we can cancel it if required.
                    DbCommand = DbConnection.CreateCommand();
                    DbCommand.CommandTimeout = 500;
                    DbCommand.CommandText = errorQuery;
                    

                    // Open the connection and kick off the queries.
                    DbConnection.Open();

                    // Execute the command and enter in all results to the generic list.
                    reader = DbCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                        exampleIds.Add(reader["AggregationId"].ToString());/

                                }
                catch (Exception ex)
                {
                    throw new ApplicationException("Exception occured in 'GetExampleAggregationIds'.", ex);
                }
                finally
                {
                    // Make sure we close and dispose of the reader.
                    if (reader != null)
                    {
                        reader.Close();
                        reader.Dispose();
                    }
                }

                return exampleIds;
            }

     

    Now the reason why I do all this via properties is so that the user can cancel the command (command is running async to the GUI), so I always need to be able to access the current command. So are you saying that in the finally block I should be nulling the command? I thought the 'DbCommand = DbConnection.CreateCommand();' line would ensure that each time I've got a fresh command which would start its time out from scratch again?

    Thanks.

    Wednesday, February 27, 2008 10:01 PM
  • I don't think that is correct and I see a DataReader in your code it implements IDisposable so wrap all your DataReaders with the second Using statement which call dispose for you automatically on classes that implements IDisposable interface.

    Wednesday, February 27, 2008 10:32 PM
  • Again a good point which will clean up my code slightly although I don't see how this would help with the command timeout issues?

    Wednesday, February 27, 2008 10:39 PM
  • But most Dataset code time outs and failure I have seen are related to open connections.

     

    Wednesday, February 27, 2008 10:51 PM
  • Well you obviously have more knowledge than myself in this area so I will certainly try it (at home now so dont have access to the latest source code). I'm not sure it is to do with open connections as the DbConnection.Open() would thrown an exception and it times out to the second.

    Anywho I will certainly give it a try and thank you for your input. Fingers crossed! Smile

    Wednesday, February 27, 2008 11:18 PM
  • As previously suggested, you really want to consider closing all of your connections. It does look like you're closing readers only in your code snippet. Question: why not create a command on demand and get rid of it after it's executed?

     

    Friday, February 29, 2008 3:40 AM
  • Hi Dennis,

    To answer your questions:

    The readers have the behaviour to close the connection - so I assume when they are closed / nulled (in the finally) that the connection would be closed. The lack of an exception being thrown when I try to Open the connection shows they are being closed successfully.

     

    The commands are created on demand (SqlConnection.CreateCommand is called mid method). There is a class visible handle on a created Command so that i can cancel the command asynchronously.

     

    I tried the fix meantioned earlier in this thread and it improved things for me, but I'm still getting time outs. I still need to investigate this issue further, but this week has just been ram packed full of live issues at work so I havent had time to look into it. Really want to get to the bottom of this; I have a feeling it might have been solved by the previous suggestion, but my time out of 10 minutes might be tooooo small. Will certainly update this thread when I have investigated further Smile

    Saturday, March 1, 2008 1:55 AM