Lesson Learned #178: What is the command timeout when we are querying an External Table?

[Jose_Manuel_Jurado]

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 1.000.000.000 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)
                    {
                        stopWatch.Start();
                        C.SqlCommand command = new C.SqlCommand("SELECT top " + tries.ToString() + "*  FROM [PerformanceVarcharNVarchar3]", oConn);
                        command.CommandTimeout = 0;
                        Console.WriteLine("------------------> Exec N#" + tries.ToString());
                        command.ExecuteNonQuery();
                        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. 

Enjoy!