SQLDataReader. ExecuteReader times out

Question

MS sql 2014 backend (on same machine) - Executereader times out (native error code: 258) @ second executereader() in same connection. Why is this so? (No big data amount involved, cmd strings involved are correct)

Note: it doesn't matter if all SELECT goes into separate connection the second always times out. It also doesn't matter if I'd use DataAdapter to catch data.... times out... see below, thanks for the ideas:

using (SqlConnection c = new SqlConnection(cString))
{
    c.Open();
    using (SqlCommand cmd = new SqlCommand(querystringPO, c))
    {

        cmd.Parameters.AddWithValue("@paramRecipe", productionOrderNo);
        using (SqlDataReader rd = cmd.ExecuteReader())
        {
            dtRecipe = new DataTable();
            dtRecipe.Load(rd);
            rd.Close();
        }
    }
    if (dtRecipe.Rows.Count > 0)
    {
        string querystringOpDefs = "SELECT * FROM ReferencedFieldsView_OperationDefinition WHERE RecipeID=@paramOpDef";
        using (SqlCommand cmd1 = new SqlCommand(querystringOpDefs, c))
        {

            cmd1.Parameters.AddWithValue("@paramOpDef", dtRecipe.Rows[0].Field<int>("ID"));
            using (SqlDataReader rd = cmd1.ExecuteReader())
            {
                dtOpDefs = new DataTable();
                dtOpDefs.Load(rd);
                rd.Close();
            }


        }
        string querystringBOMItems = "SELECT * FROM ReferencedFieldsView_BomItem WHERE RecipeID=@paramBOMItem";

        using (SqlCommand cmd2 = new SqlCommand(querystringBOMItems, c))
        {

            cmd2.Parameters.AddWithValue("@paramBOMItem", dtRecipe.Rows[0].Field<int>("ID"));
            using (SqlDataReader rd = cmd2.ExecuteReader())
            {
                dtBOMItems = new DataTable();
                dtBOMItems.Load(rd);
            }
        }
    }
}

Ex below:

Exception: System.InvalidOperationException: Server operation encountered an exception ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader()

Post the full exception, including the call stack and any inner exceptions. You can get that very easily with Exception.ToString(). You didn't even post the message which could explain what kind of timeout it was. Don't just post the message though, post the full exception — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 24, 2019 at 12:21
As for why timeouts occur, if it's command timeouts, it's typically because of bad queries and missing indexes. The way to fix them is to fix the queries and add proper indexes. If there are a lot of data and no optimization is possible, the timeout should be increased — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 24, 2019 at 12:21
The names of the views suggest you're running a BOM query. Those are similar to hierarchical queries and can be very slow unless special care is taken. The typical way to implement them is to use recursion, which is slow. Or, you can replace the Parent/ChildID relation with a hierarchyid field and get very fast queries — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 24, 2019 at 12:26
Use SQL Server Management Studio and run you query on SSMS. The error messages in SSMS are 1000 times better than c# for debugging these type issues. — jdweng
– jdweng, Commented Apr 24, 2019 at 12:27
So you've established there's a lock then, or is that speculating? You can use sys.dm_os_waiting_tasks to see if the query is actually blocked, and why. Having a local server only establishes that the problem is not the network; it can still be the execution plan. — Jeroen Mostert
– Jeroen Mostert, Commented Apr 24, 2019 at 12:55

gericooper · Accepted Answer · 2019-04-24 15:20:24Z

0

Fact of the matter is that using SQL 2K12 the above snippet all worked (same database same etc)

However as sb suggested instead of selecting all fields (*), when explicitly added the req'd fields it started to work.

answered Apr 24, 2019 at 15:20

gericooper

2623 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

SQLDataReader. ExecuteReader times out

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related