This article is contributed. See the original author and article here.

Today, I received a very good question from a customer about what is the command timeout that external tables is using. Following I would like to share with you my experience playing with this. 


We need to know that the command timeout is configured by application side, so, for this reason, I developed a small C# application with the following characteristics:


  • Every loop I’m increasing the SELECT TOP in 100000 rows.

  • PerformanceVarcharNVarchar3 is an external tables that contains around of rows. 

  • I used the connection statistiics to obtain the time invested on every operation. 

  • Changing the command timeout to multiple values (in this case to 0) the operation will wait until the command timeout is reached. 

                    for (int tries = 1; tries <= nRows; tries+=100000)
                        C.SqlCommand command = new C.SqlCommand("SELECT top " + tries.ToString() + "*  FROM [PerformanceVarcharNVarchar3]", oConn);
                        command.CommandTimeout = 0;
                        Console.WriteLine("------------------> Exec N#" + tries.ToString());
                        IDictionary currentStatistics = oConn.RetrieveStatistics();
                        if (bMetric)
                            Console.WriteLine("ID Connection: " + oConn.ClientConnectionId.ToString());
                            Console.WriteLine("BytesReceived:        " + currentStatistics["BytesReceived"]);
                            Console.WriteLine("BytesSent:            " + currentStatistics["BytesSent"]);
                            Console.WriteLine("SelectCount:          " + currentStatistics["SelectCount"]);
                            Console.WriteLine("SelectRows:           " + currentStatistics["SelectRows"]);
                            Console.WriteLine("ExecutionTime:        " + currentStatistics["ExecutionTime"]);
                            Console.WriteLine("Network Server time:  " + currentStatistics["NetworkServerTime"]);




In this situation, as we could see, the command timeout that a query that is running using External Table will be the same that the application has. 





Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.