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;
    }
}

Consider adding the timeout value to the config file too, otherwise any adjustment would mean a code redeploy. — christiandev, Aug 26 '13 at 09:11

Ehsan · Accepted Answer · 2020-06-08T01:01:20.467

119

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;

edited Jun 08 '20 at 01:01

answered Aug 26 '13 at 08:54

Ehsan

28,801
6
51
61

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. – Ehsan Aug 26 '13 at 11:53
Any disadvantage of increasing the timeout to 10 min or 30 min? – Manish Kumar Oct 03 '16 at 12:31
1

@ManishKumarSingh Who will wait for 10 minutes for a query to return the results? This is an era of milliseconds. – Ehsan Oct 06 '16 at 03:54
13

@Ehsan 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.) – Slothario Mar 09 '17 at 01:02
4

@Ehsan 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. – logixologist Aug 08 '17 at 13:24
3

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. – Charles Byrne Feb 08 '18 at 16:04

score 21 · Answer 2 · edited May 20 '15 at 03:38

21

Add timeout of your SqlCommand. Please note time is in second.

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

edited May 20 '15 at 03:38

Kuzon

692
5
18
48

answered Aug 26 '13 at 08:54

Ajay

6,233
15
62
122

Any disadvantage of increasing the timeout to 10 min or 30 min? – Manish Kumar Oct 03 '16 at 12:33
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. – MiscellaneousUser Mar 17 '17 at 19:30

score 2 · Answer 3 · edited Apr 18 '17 at 13:34

2

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.

edited Apr 18 '17 at 13:34

Draken

3,049
13
32
49

answered Apr 18 '17 at 13:03

Akash Srivastava

21
1

Emam · Answer 4 · 2019-07-24T07:44:55.943

0

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

edited Jul 24 '19 at 07:44

answered May 23 '19 at 07:25

Emam

43
7

score -3 · Answer 5 · answered Dec 01 '17 at 04:48

-3

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.

answered Dec 01 '17 at 04:48

Pranav Kulshrestha

101
6

3

This is a good point, but sometimes pagination is not possible. For example csv exports or similar file downloads. – Koja Mar 26 '18 at 10:42
5

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." – Mert Akcakaya Jul 26 '18 at 09:42
@MertAkcakaya - may I know why so? – Pranav Kulshrestha Jul 27 '18 at 05:44
5

@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. – Mert Akcakaya Jul 27 '18 at 08:38
1

@MertAkcakaya That's true. No such technical document. I commented on the basis of General standard we follow. Thanks though – Pranav Kulshrestha Aug 01 '18 at 05:29
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" – Stuart.Sklinar Dec 19 '18 at 12:24

Increasing the Command Timeout for SQL command

5 Answers5

Linked