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

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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!

Leave a Reply Cancel reply