A SQL query executed well by SQL Server, but returning timeout when executed by ADO.NET

Question

I have a server that executing SQL queries against Azure SQL Server using ADO.NET

When I'm trying to run a specific query by my server (using ADO.NET) I get a timeout error, but when I'm executing the same query by SQL Server, I get results after 1-2 seconds.

I tried to increase to timeout in the connection string and in the SqlCommand object with no results.

I saw one potential solution to change the timeout in the SqlCommand object to 0, I tried and got results after a long time, but it works only on my local machine and not on my production server

This is the code I'm using in my server to integrate my DB:

using (var connection = new SqlConnection(ConnectionString))
{   
    var command = new SqlCommand
    {
        CommandText = query
    };

    foreach ( var parameter in parameters)
        command.Parameters.AddWithValue(parameter.Key, parameter.Value ?? Convert.DBNull);

    command.Connection = connection;

    try
    {
        _logger.Info("Open connection to db");
        connection.Open();

        _logger.Info("Execute command");
        SqlDataReader reader = command.ExecuteReader();

        List<Column> columns = CreateColumnList(reader);
    }
    catch (Exception e)
    {
        _logger.Error(e);
    }
}

This is the exception message I get:

The timeout to perform has expired. The timeout period passed before completing the action or the server is not responding

To run the "same query" in SSMS you would have to call sp_executesql with all the parameters. Using TSQL local variables, or literal values instead will produce a different query plan. — David Browne - Microsoft
– David Browne - Microsoft, Commented Apr 28, 2019 at 14:26
Try monitoring your database after issuing the query to see what is holding it from running. Maybe it is being locked for some reason. Also, I'd recommend specifying the type of the command object, the CommandType property, so it doesn't have to infer if it is a text (a select statement) or a stored procedure. — Albert
– Albert, Commented Apr 28, 2019 at 14:36
I suggest you avoid AddWithValue. If the column type is varchar, the parameter type will be nvarchar with strings so an index on the key column will not be used if you are using a SQL collation. You probably are using a varchar literal or variable in the SSMS query. — Dan Guzman
– Dan Guzman, Commented Apr 28, 2019 at 14:37
@DavidBrowne-Microsoft a little extra - in order to pick up the same execution plan it's also necessary that the session contain the same set options. — tukaef
– tukaef, Commented Apr 28, 2019 at 14:46
I agree with David. You should capture T-SQL executed in .NET by using SQL Profiler or Extended Events. Then paste both query, in this thread. — user11380812
– user11380812, Commented Apr 28, 2019 at 15:02

Dan Guzman · Accepted Answer · 2019-04-28 14:55:36Z

2

Instead of AddWithValue, specify the actual column database type and max length. For example:

command.Parameters.Add(parameter.Key, SqlDbType.VarChar, 30).Value = parameter.Value ?? Convert.DBNull);

This practice will ensure the parameter type and length matches that of the underlying column and facilitate efficient index use by the query. A big problem with AddWithValue is that it will infer data type nvarchar with strings, which will prevent a SARGable expression against an varchar column with a SQL collation.

The reason the SSMS query runs fast is likely because the ad-hoc query specifies a varchar literal or variable.

edited Apr 28, 2019 at 14:55

answered Apr 28, 2019 at 14:48

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

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

3 Comments

user11380812 Over a year ago

This problem might be caused by using DataReader. We do not really know what "Creates ColumnList(reader)" try to accomplish. Although your provide great answer, I am not sure that will really solve the problem. The best approach is to capture T-SQL statement executed in ADO.NET and paste this statment in this thread.

Dan Guzman Over a year ago

@DarkoMartinovic, my answer was an educated guess based on the assumption that the timeout occurred during ExecuteReader. A timeout will not occur once the reader is returned.

user11380812 Over a year ago

I like your answer and really like "an educated guess". IMO, the most important thing is to educate users how to ask a question. This question should be rewritten and add some more details.

Collectives™ on Stack Overflow

A SQL query executed well by SQL Server, but returning timeout when executed by ADO.NET

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related