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!
Posted at https://sl.advdat.com/3h5fEZI
