86

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;
    }
}
Improve this question
1
  • 3
    Consider adding the timeout value to the config file too, otherwise any adjustment would mean a code redeploy. Commented Aug 26, 2013 at 9:11

5 Answers 5

Reset to default
139

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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.
@ManishKumarSingh Who will wait for 10 minutes for a query to return the results? This is an era of milliseconds.
@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.)
@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.
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.
|
25

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

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

2 Comments

Any disadvantage of increasing the timeout to 10 min or 30 min?
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.
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.

Improve this answer

Comments

2

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
Improve this answer

Comments

-6

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.

Improve this answer

6 Comments

This is a good point, but sometimes pagination is not possible. For example csv exports or similar file downloads.
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."
@MertAkcakaya - may I know why so?
@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.
@MertAkcakaya That's true. No such technical document. I commented on the basis of General standard we follow. Thanks though
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.