Tuesday, June 29, 2021

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

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"]);
                        }

 

 

Capture33.PNG

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! 

 

 

Posted at https://sl.advdat.com/3h5fEZI