views:

3697

answers:

17

Update: Looks like the query does not throw any timeout. The connection is timing out.

This is a sample code for executing a query. Sometimes, while executing time consuming queries, it throws a timeout exception.

I cannot use any of these techniques: 1) Increase timeout. 2) Run it asynchronously with a callback. This needs to run in a synchronous manner.

please suggest any other techinques to keep the connection alive while executing a time consuming query?

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}
A: 
command.CommandTimeout *= 2;

That will double the default time-out, which is 30 seconds.

Or, put the value for CommandTimeout in a configuration file, so you can adjust it as needed without recompiling.

Chris
i cannot change the timeout. It is against the standards here. :(
dragon
Bummer. Missed that. My bad!
Chris
You're not increasing the timeout on the connection, though...you're just increasing the timeout on the command. In other words, once the command's lifetime is through, so is that increased timeout length. Perhaps that's enough for you to get around your (insert epithet here) standard?
nathaniel
well. i am looking for solutions before I suggest anything.
dragon
as per my manager, i need to explore all options.
dragon
A: 

If you absolutely cannot increase the timeout, your only option is to reduce the time of the query to execute within the default 30 second timeout.

Terrapin
Is this documented anywhere? You really can't increase the length, just decrease?
tyndall
That's not the way I answered the question - the user is asking what he can do if increasing the timeout is *not* an option. So I answered him by suggesting he should decrease the time in which his query executes so as to stay within the timeout limit. The timeout can be arbitrarilty set up or down.
Terrapin
A: 

You should break your query up into multiple chunks that each execute within the timeout period.

Terrapin
it is just one query. sometimes it takes a little bit longer. I am looking for any way to keep the connection alive..
dragon
The ONLY way to keep the connection alive is to increase the timeout time. That's what increasing the timeout does... it keeps the connection alive.
Terrapin
+1  A: 

I have to agree with Terrapin.

You have a few options on how to get your time down. First, if your company employs DBAs, I'd recommend asking them for suggestions.

If that's not an option, or if you want to try some other things first here are your three major options:

  1. Break up the query into components that run under the timeout. This is probably the easiest.
  2. Change the query to optimize the access path through the database (generally: hitting an index as closely as you can)
  3. Change or add indices to affect your query's access path.
Sam Erwin
+4  A: 

You should first check your query to see if it's optimized and it isn't somehow running on missing indexes. 30 seconds is allot for most queries, even on large databases if they are properly tuned. If you have solid proof using the query plan that the query can't be executed any faster than that, then you should increase the timeout, there's no other way to keep the connection, that's the purpose of the timeout to terminate the connection if the query doesn't complete in that time frame.

Pop Catalin
+1  A: 

If you are constrained from using the default process of changing the timeout value you will most likely have to do a lot more work. The following options come to mind

  1. Validate with your DBA's and another code review that you have truly optimized the query as best as possible
  2. Work on the underlying DB structure to see if there is any gain you can get on the DB side, creating/modifying an idex(es).
  3. Divide it into multiple parts, even if this means running procedures with multiple return parameters that simply call another param. (This option is not elegant, and honestly if your code REALLY is going to take this much time I would be going to management and re-discussing the 30 second timeout)
Mitchel Sellers
true. problem is management wants me to explore all options. :)
dragon
+1  A: 

We recently had a similar issue on a SQL Server 2000 database.

During your query, run this query on your master database on the db server and see if there are any locks you should troubleshoot:

select 
  spid,
  db_name(sp.dbid) as DBname,
  blocked as BlockedBy,
  waittime as WaitInMs,
  lastwaittype,
  waitresource,
  cpu,
  physical_io,
  memusage,
  loginame,
  login_time,
  last_batch,
  hostname,
  sql_handle
from sysprocesses sp
where (waittype > 0 and spid > 49) or spid in (select blocked from sysprocesses where blocked > 0)

SQL Server Management Studio 2008 also contains a very cool activity monitor which lets you see the health of your database during your query.

In our case, it was a networkio lock which kept the database busy. It was some legacy VB code which didn't disconnect its result set quick enough.

Geir-Tore Lindsve
A: 

I tend to dislike increasing the connection/command timeout since in my mind that would be a matter of taking care of the symptom, not the problem

Geir-Tore Lindsve
+1 since you make it clear why they probably have this policy..
Torbjørn
+12  A: 

Since you are using ExecuteNonQuery which does not return any rows, you can try this polling based approach. It executes the query in an asyc manner (without callback) but the application will wait (inside a while loop) until the query is complete. From MSDN. This should solve the timeout problem. Please try it out.

But, I agree with others that you should think more about optimizing the query to perform under 30 seconds.

        IAsyncResult result = command.BeginExecuteNonQuery();

        int count = 0;
        while (!result.IsCompleted)
        {
            Console.WriteLine("Waiting ({0})", count++);
            System.Threading.Thread.Sleep(1000);
        }
        Console.WriteLine("Command complete. Affected {0} rows.",
        command.EndExecuteNonQuery(result));
Gulzar
Thanks. this seems to be working. will wait for some more time if anything better comes up..
dragon
Checking the result.IsCompleted is redundant.
M. Jahedbozorgan
The OP indicated that it is that query that times out. Whether synchronous or asynchronous, a query timeout issue would still be the same.e.g put WAITFOR DELAY '00:01:00' in your query.If your command.Timeout is 30s, this will still throw a query timeout at 30 seconds with either async or sync calling.
stephbu
@stephbu - looks like the query is not timing out. it is the connection that is timing out. this solution works fine for now.
dragon
I am not 100% sure, but I don't think that a SqlConnection can time out - as far as I know only opening a new connection might time out (15 seconds default). So I still believe that the command times out (This might look like a connection timeout if the command times out while trying to read from the connection.).
Daniel Brückner
+1  A: 

If you are prohibited from using the features of the data access API to allow a query to last more than 30 seconds, then we need to see the SQL.

The performance gains to be made by optimizing the use of ADO.NET are slight in comparison to the gains of optimizing the SQL.

And you already are using the most efficient method of executing SQL. Other techniques would be mind numbingly slower (although, if you did a quick retrieval of your rows and some really slow client side processing using DataSets, you might be able to get the initial retrieval down to less than 30 seconds, but I doubt it.)

If we knew if you were doing inserts, then maybe you should be using bulk insert. But we don't know the content of your sql.

MatthewMartin
A: 

just set sqlcommand's CommandTimeout property to 0, this will cause the command to wait until the query finishes... eg:

SqlCommand cmd = new SqlCommand(spName,conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
Vikram Sudhini
+1  A: 

This is an UGLY hack, but might help solve your problem temporarily until you can fix the real problem

    private static void CreateCommand(string queryString,string connectionString)
    {
        int maxRetries = 3;
        int retries = 0;
        while(true)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(queryString, connection);
                    command.Connection.Open();
                    command.ExecuteNonQuery();
                }
                break;
            }
            catch (SqlException se)
            {
                if (se.Message.IndexOf("Timeout", StringComparison.InvariantCultureIgnoreCase) == -1)
                    throw; //not a timeout

                if (retries >= maxRetries)
                    throw new Exception( String.Format("Timedout {0} Times", retries),se);

                //or break to throw no error

                retries++;
            }
        }
    }
Chad Grant
A: 

hey dragon

have you thought about breaking the query down into several smaller chunks?

Also, have you ran your query against the Database Engine Tuning Advisor in:

Management Studio > Tools > Database Engine Tuning Advisor

Lastly, could we get a look at the query itself?

cheers

andy
A: 

Have you tried wrapping your sql inside a stored procedure, they seem to have better memory management. Have seen timeouts like this before in plan sql statement with internal queries using classic ADO. i.e. select * from (select ....) t inner join somthingTable. Where the internal query was returning a very large number of results.

Other tips 1. Performing reads with the with(nolock) execution hint, it's dirty and I don't recommend it but it will tend to be faster. 2. Also look at the execution plan of the sql your trying to run and reduce the row scanning, the order in which you join tables. 3. look at adding some indexes to your tables for faster reads. 4. I've also found that deleting rows is very expensive, you could try and limit the number of rows per call. 5. Swap @table variables with #temporary tables has also worked for me in the past. 6. You may also have saved bad execution plan (heard, never seen).

Hope this helps

A: 

Update: Looks like the query does not throw any timeout. The connection is timing out.

I.o.w., even if you don't execute a query, the connection times out? because there are two time-outs: connection and query. Everybody seems to focus on the query, but if you get connection timeouts, it's a network problem and has nothing to do with the query: the connection first has to be established before a query can be ran, obviously.

Frans Bouma
I agree. dragon, you can't get a connection timeout during normal command execution. Show your exception with a stack.
Shrike
A: 

It might be worth trying paging the results back.

Thomas Bratt
A: 

I used this code

IAsyncResult result = command.BeginExecuteNonQuery();

    int count = 0;
    while (!result.IsCompleted)
    {
        Console.WriteLine("Waiting ({0})", count++);
        System.Threading.Thread.Sleep(1000);
    }
    Console.WriteLine("Command complete. Affected {0} rows.",
    command.EndExecuteNonQuery(result));

that I found here but when I run my app result.iscomplete is set true but no command is run ! my command try to run a SP

where is the problem ?

regards

Alibm
You have posted this as a "reply" to a 2008 question. It is unlikely to get much attention. It would be better to post this as a *new* question (see "Ask Question" at the top of the page), fully explaining your scenario.
Marc Gravell