I recently started using TSQLT through Redgate's SQL Test to create and run unit tests. I have run into a problem though. Unit tests that take more than a couple minutes to execute will time out, which halts the execution of all other unit tests.
How can I extend the length of the timeout for tSQLt?
My "unit testing" is probably not actually unit testing, but I am not familiar with another testing methodology that fits better.
I am working on a project to improve the speed of the nightly refresh of our data warehouse. Currently, this process takes five hours. Through rearranging the tasks to run in parallel where possible, I have cut that time down to two hours. My problem is that unless I can find a way to prove that the new process has the exact same end result as the old process, QA will spend the next year checking every value in every column in every row in every table. Either that or the project will be scrapped as being "too hard".
So the testing that I have come up with is this: I have a database where I run a script to copy over the resulting tables after they are processed in our testing environment using the new method that I created. Then, back in the testing environment, I run the old process to update the tables. Then, I run one unit test per table to prove that the contents of the archived tables processed using the new method is exactly identical to the contents of the tables re-processed using the old method.
Unfortunately due to the size of some of these tables (millions of rows) some of the unit tests are timing out. The following is the error that I am recieving:
Test Procedure: [HR360_unitTest1].[HR360_DW_Job6].[test fact_group_clients Identical Contents] on emr\preprod System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource
1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at RedGate.SQLTest.tSQLt.FrameworkWrapper.#kz(SqlCommand #LGj) at RedGate.SQLTest.tSQLt.FrameworkWrapper.#7qHc(String #2xAd, SqlParameter[] #LvPb) at RedGate.SQLTest.tSQLt.FrameworkWrapper.#qd4b(String #LGxc) at RedGate.SQLTest.tSQLt.TestRunner.Execute(SqlConnection connection) ClientConnectionId:519569ed-03ce-4510-b226-9ff18e0f1d8d Error Number:-2,State:0,Class:11
If there is no way to increase the timeout for tSQLt, then I will either have to find another way to automatically test that the contents of these tables is identical (in a way that is repeatable at-will) or abandon the project.
The timeout issue turned out to be within Redgate SQL Test. Therefore, the simple solution for long running unit tests would be to run them directly in the tSQLt framework by calling tSQLt.Run. In my testing, tSQLt does not appear to have a timeout issue. As of this writing, I have a unit test that has been running continuously for 19 hours without timing out.
This unit test, taking so long to run, creates it's own problem in my particular case. I will solve this issue with a hashing solution or EXCEPT solution as discussed in the thread on the response from @datacentricity.