Increasing the Command Timeout for SQL command

Question

I have a little problem and hoping someone can give me some advice. I am running a SQL command, but it appears it takes this command about 2 mins to return the data as there is a lot of data. But the default connection time is 30 secs, how do I increase this, and apply it to this command?

public static DataTable runtotals(string AssetNumberV, string AssetNumber1V)
{
    DataTable dtGetruntotals;

    try
    {
        dtGetruntotals = new DataTable("Getruntotals");

        //SqlParameter AssetNumber = new SqlParameter("@AssetNumber", SqlDbType.VarChar, 6);
        //AssetNumber.Value = AssetNumberV; 

        SqlParameter AssetNumber = new SqlParameter("@AssetNumber", SqlDbType.VarChar, 10);
        AssetNumber.Value = AssetNumberV;

        SqlParameter AssetNumber1 = new SqlParameter("@AssetNumber1", SqlDbType.VarChar, 10);
        AssetNumber1.Value = AssetNumber1V;

        SqlCommand scGetruntotals = new SqlCommand("EXEC spRunTotals @AssetNumber,@AssetNumber1 ", DataAccess.AssetConnection); 
        // scGetruntotals.Parameters.Add(AssetNumber);
        scGetruntotals.Parameters.Add(AssetNumber);
        scGetruntotals.Parameters.Add(AssetNumber1);

        SqlDataAdapter sdaGetruntotals = new SqlDataAdapter();
        sdaGetruntotals.SelectCommand = scGetruntotals;
        sdaGetruntotals.Fill(dtGetruntotals);

        return dtGetruntotals;
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error Retriving totals Details: Processed with this error:" + ex.Message);
        return null;
    }
}

Vacancy

Consider adding the timeout value to the config file too, otherwise any adjustment would mean a code redeploy. – Needs

Answer 1

it takes this command about 2 mins to return the data as there is a lot of data

Probably, Bad Design. Consider using paging here.

default connection time is 30 secs, how do I increase this

As you are facing a timeout on your command, therefore you need to increase the timeout of your sql command. You can specify it in your command like this

// Setting command timeout to 2 minutes
scGetruntotals.CommandTimeout = 120;

Wyler

you are welcome, you can accept it then. Though i will seriously refrain myself from using a query that takes 2 minutes. try using pagination. – Wyler

Any disadvantage of increasing the timeout to 10 min or 30 min? – Pasto

@ManishKumarSingh Who will wait for 10 minutes for a query to return the results? This is an era of milliseconds. – Wyler

@Wyler When you or a small team of people are the only ones using the software, and the query is only run a few times a year. It's usually not worth optimizing in those situations. (Still, when writing backend corporate software, a few well-designed indices can protect your sanity.) – Noriega

@Wyler I beg to differ..... I have a similar situation that runs correctly in Production (a few seconds) but on a test server which is stripped down and shared by multiple resources its over 30 seconds. In this situation there is nothing wrong with extending the time out. – Machicolation

Bad design? Not always. At a previous workplace when we had to do maintenance on production we switched to backup database. During this time we would get timeouts even though the statistics were kept when we restored to the backup server(s). They would sometimes generate a different execution plan when some queries were run that took longer so we extended the timeout in config on the DR / backup servers even though they were basically the same hardware as production. There is no guarantee that you will have the same execution plan. We tried FULL SCAN after the restore with no luck. – Deccan

Answer 2

Add a command timeout to your SqlCommand. Please note time is in seconds.

// Setting command timeout to 1 second
scGetruntotals.CommandTimeout = 1;

Zena

Any disadvantage of increasing the timeout to 10 min or 30 min? – Pasto

Manish, would you want to wait 10 mins or 30 mins for the process to get the data? If its an overnight process and its the only thing and isn't going to interfere anywhere else, 30 mins why not. Or if you're not waiting for the result, why not. – Orest

Answer 3

Since it takes 2 mins to respond, you can increase the timeout to 3 mins by adding the below code

scGetruntotals.CommandTimeout = 180;

Note : the parameter value is in seconds.

Lazarolazaruk

Answer 4

Setting command timeout to 2 minutes.

 scGetruntotals.CommandTimeout = 120;

but you can optimize your stored Procedures to decrease that time! like

• removing courser or while and etc
• using paging
• using #tempTable and @variableTable
• optimizing joined tables

Raccoon

Answer 5

Setting CommandTimeout to 120 is not recommended. Try using pagination as mentioned above. Setting CommandTimeout to 30 is considered as normal. Anything more than that is consider bad approach and that usually concludes something wrong with the Implementation. Now the world is running on MiliSeconds Approach.

Mincemeat

This is a good point, but sometimes pagination is not possible. For example csv exports or similar file downloads. – Manilla

Just wrong: "Setting CommandTimeout to 30 is considered as normal. Anything more than that is consider bad approach and that usually concludes something wrong with the Implementation." – Hylton

@MertAkcakaya - may I know why so? – Mincemeat

@PranavKulshrestha There is no general opinion or a valid reason to say that setting command timeout to more than 30 is bad approach. We have lots of stored procedures that take more than 30 seconds to execute. – Hylton

@MertAkcakaya That's true. No such technical document. I commented on the basis of General standard we follow. Thanks though – Mincemeat

If someone needs to set it above 30 they have to, perhaps they can't rewrite the query and "Just have to get it done" – Busload