TSQLTester for SQL Ninja

imageHi, today I would like to share with you idea of testing tool for T-SQL developers who want to not only replace stored procedures with fasters stored procedures but also want to be sure that old code on SQL Server produces exactly the same results as a new optimized and tuned up one. I created this tool as command line to easy integrate with possible automate of testing and TSQLTester.exe has 4 parameters. All of those parameters are localization of files with different contents. First one contains connection string to database server we want to measure execution time and test our solution, second file contains T-SQL tests for example bunch of invocation of stored procedures or bunch of SELECT statements without GO statements inside, third file contains new version of our code that we tuned up it can be stored procedure DROP CREATE file of even file with bunch of that stored procedures as well, fourth file contains old code for example stored procedure DROP CREATE file or other code that brings back old version of code to SQL Server and it is used for testing as well as a base code that produce good results.

To create my tool I used C# 4.0 and Visual Studio 2012. And I referenced 3 following libraries:

  • Microsoft.SqlServer.ConnectionInfo,
  • Microsoft.SqlServer.Management.Sdk.Sfc,
  • Microsoft.SqlServer.Smo.

Because I want to use SMO. And to have them anvaliable please remember to install with SQL Server “Client Tools SDK” feature. And to compile this solution you need to have SQL Server installed on your development machine. Also very important thing is to have backward compatibility of this tool so, I need to specify in app.config as an C# project item with the following content.

<?xml version="1.0"?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>
    <requiredRuntime version="v4.0" />
  </startup>
</configuration>

And here is code of TSQLTester that contains less than 100 lines of C# code and helps a lot with testing not only execution time and measure it in milliseconds but also check exactly content of produced results by testing code with previously run new and old one T-SQL code.

namespace TSQLTester
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.IO;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;

    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length != 4)
            {
                Console.WriteLine(
                "Usage: TSQLTester.exe connectionStringFile, testCodeFile, newCodeFile, oldCodeFile");
                return;
            }

            var connectionStringFile = args[0];
            var testCodeFile = args[1];
            var newCodeFile = args[2];
            var oldCodeFile = args[3];

            RunTest(connectionStringFile, testCodeFile, newCodeFile, oldCodeFile);
        }

        private static string GetContent(string filePath)
        {
            var file = new FileInfo(filePath);
            return file.OpenText().ReadToEnd();
        }

        private static bool CompareDataSets(DataSet fSet, DataSet sSet)
        {
            if (fSet.Tables.Count != sSet.Tables.Count)
                return false;

            for (int t = 0; t < fSet.Tables.Count; ++t)
            {
                var t1 = fSet.Tables[t];
                var t2 = sSet.Tables[t];
                if (t1.Rows.Count != t2.Rows.Count ||
                    t1.Columns.Count != t2.Columns.Count)
                    return false;

                for (int r = 0; r < t1.Rows.Count; r++)
                {
                    for (int c = 0; c < t1.Columns.Count; c++)
                    {
                        if (t1.Rows[r].ToString() !=
                            t2.Rows[r].ToString())
                            return false;
                    }
                }
            }

            return true;
        }

        private static void RunTest(
            string connectionStringFile, string testCodeFile,
            string newCodeFile, string oldCodeFile)
        {
            using (var connection = new SqlConnection(GetContent(connectionStringFile)))
            {
                var server = new Server(new ServerConnection(connection));

                var newTestAdapter = new SqlDataAdapter(
                GetContent(testCodeFile), connection);
                var oldTestAdapter = new SqlDataAdapter(
                GetContent(testCodeFile), connection);

                server.ConnectionContext.ExecuteNonQuery(GetContent(newCodeFile));
                var newDataSet = new DataSet();
                var newTime = Stopwatch.StartNew();
                var newCount = newTestAdapter.Fill(newDataSet);
                newTime.Stop();

                server.ConnectionContext.ExecuteNonQuery(GetContent(oldCodeFile));
                var oldDataSet = new DataSet();
                var oldTime = Stopwatch.StartNew();
                var oldCount = oldTestAdapter.Fill(oldDataSet);
                oldTime.Stop();

                Console.WriteLine("RESULT: {0}, OLD_TIME: {1}ms, NEW_TIME: {2}ms.",
                (newCount == oldCount && CompareDataSets(newDataSet, oldDataSet)
                ? "PASS" : "FAIL"),
                oldTime.ElapsedMilliseconds,
                newTime.ElapsedMilliseconds);
            }
        }
    }
}

So, now you maybe wonder how to use this very small but powerful tool? I have in my mind at least two scenarios. First is that you have stored procedure on a copy of production database and you want to tune up this procedure. But, the same moment you want to be sure that new procedure produces the same output(s) as old one. So, you can use your application and SQL Profiler tool to record all invocation of procedure(s) you want to tune up and you may collect all that uses examples to your testCodeFile.sql file, you may also put on the first line of this file “DBCC DROPCLEANBUFFERS” if you want to clean buffers on database for have the same results. Now you can create newCodeFile.sql with DROP CREATE code for your new optimized stored procedure(s) and oldCodeFile.sql with DROP CREATE code for your old well tested stored procedure(s). Of course, you can open testCodeFile.sql and newCodeFile.sql in SQL Management Studio and play with optimization, analyze execution plans, find bottlenecks and from time to time you can use TSQLTester to check if the new code is faster and create exactly the same output as old one. When some of your change impact to results and TSQLTester FAIL test you may revert back your last change and do something else.

Second scenario I can give you as an example of usage TSQLTester is stored procedure that calculate statuses of rows in table and because of some reasons it is slow because it has cursor and other mistakes (see source code at the end of entry). And you have some ideas how to eliminate this cursor and other issues, but you want to be sure that statuses will be calculated exactly the same on new code. To achieve that you may create testCodeFile.sql with clean buffers of T-SQL, invocation of update procedure and then put series of select statements that will select calculated statuses. Of course, you may have many executions of update store procedure and many selects to produce output to compare old procedure and new one.

At the end I want to tell you why this TSQLTester is so cool? First, it is because huge SQL Management Studio has no such feature and I do not know why if it takes less than 100 lines of code to write. And that it solves INSERT EXEC issues that you cannot solve with tSQLt for example :). Second, it is cool because you can very fast test results of your optimizations and even you may have numbers of folders with different connection strings and tests code and use BAT or PowerShell to invoke all suite of tests on many copies of production databases. And Third reason why TSQLTester is so cool is that it decreases the risk of your impact into production T-SQL code, so your managers will be happy that you take seriously the quality of your work by testing both performance and content or results. If you would like you may download source code of TSQLTester (3666 downloads). Enjoy!

P ;).

4 Replies to “TSQLTester for SQL Ninja”

  1. Just a tiny code improvement (reduce 6 lines of code ;-) ): Replace method “GetContent” with this code:
    String fileContent = System.IO.File.ReadAllText(“myfile.txt”);
    You are using System.IO anyway.

    • Thanks it is cool idea. feel free to replace GetContent with File.ReadAllText and remove my method GetContent. I belive it is a safe replacement, but let me know if that works. Thanks!

  2. If you want to increase timeout you may add 2 following lines of code after creation of new and old code adapters:

    newTestAdapter.SelectCommand.CommandTimeout = 60000;
    oldTestAdapter.SelectCommand.CommandTimeout = 60000;

  3. Pingback: TSQLTester for SQL Ninja with Mocking @ coding by to design

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.