Query Timing out when Command Timeout = 0

Question

I have a frustrating issue with a query that typically takes between 1.5-2 minutes to run (due to a lack of ability to modify this database, we cannot improve it more than this time). The query times out, despite the Command Timeout property being set to 0 (this is C# code).

Here is the code that executes the query:

public DataTable GetData()
{
    DataTable results = new DataTable();

    try
    {
        using (var sqlConnection = new SqlConnection(ConfigurationManager.AppSettings["SqlConnectionString"].ToString()))
        {
            String command = _query;

            sqlConnection.Open();
            var sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = command;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandTimeout = 0;
            SqlDataAdapter daM = new SqlDataAdapter(sqlCommand.CommandText, sqlConnection);
            daM.Fill(results);
            sqlConnection.Close();
        }
    }
    catch(Exception e)
    {
        Console.WriteLine("Error " + e.StackTrace);
    }

    Console.WriteLine("Retrieving results for query " + _query);
    Console.WriteLine("Total Results: " + results.Rows.Count);
    return results;
}

I'm not sure where to look for the culprit. Setting a more explicit timeout does nothing, and as I said there's no way to further improve the query that we've been able to find. The connection string has the following parameters:

server =

Integrated Security = SSPI

database =

Connection Timeout = 0

Any advice of where I should look next? We are using Microsoft SQL Server.

Ouch, not being able to modify the database to fix a query that takes that long is challenging. Do you have nonSARGable predicates or joins? What about scalar functions? Sometimes you can still eek some decent performance boost by tweaking the query. — Sean Lange
– Sean Lange, Commented Sep 8, 2015 at 19:46
the best performance we've had out of it is basically making our own temp database, pulling data into temp tables and indexing those tables....its pretty atrocious really — Marshall Tigerus
– Marshall Tigerus, Commented Sep 8, 2015 at 19:53

Andrey Korneyev · Accepted Answer · 2015-09-08 19:39:42Z

13

You have set sqlCommand.CommandTimeout, but later you've created SqlDataAdapter as

SqlDataAdapter daM = new SqlDataAdapter(sqlCommand.CommandText, sqlConnection)

Here adapter implicitly creates and uses new SqlCommand (not the one you've configured) since you've passed there command text, not instance of SqlCommand.

Use another constructor of SqlDataAdapter and create it like

 SqlDataAdapter daM = new SqlDataAdapter(sqlCommand)

edited Sep 8, 2015 at 19:39

answered Sep 8, 2015 at 19:33

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marshall Tigerus Over a year ago

derp....this is what I get for copying and pasting code and not paying attention to it. Thank you

MrDywar · Accepted Answer · 2015-09-08 20:06:14Z

1

Set timeout on SqlConnection does not work in your case, you need do it on SqlDataAdapter.

daM.SelectCommand.CommandTimeout = Value;

Google for "How do you change a SqlDataAdapter .CommandTimeout?"

answered Sep 8, 2015 at 20:06

MrDywar

3162 silver badges7 bronze badges

Collectives™ on Stack Overflow

Query Timing out when Command Timeout = 0

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related