Tip to optimize SQL Queries

Identification of the problem.

Some days ago, a customer call me to try to solve a performance problem into an existing application connected to SQL Server. Using SQL Server Profile, we found some queries with unexpected times. For example, to find one item in a large table, the SQL query use many seconds (between 4 and 10). Next, after multiple checks, we see that this C# code (using Parameters.AddWithValue) is executed like that by SQL Server and is responsible of this extra time in the application.

using (var cmd = new SqlDatabaseCommand(connection))
{
    cmd.CommandText.AppendLine(" SELECT TOP 1 MyString "):
    cmd.CommandText.AppendLine("   FROM MyTable ");
    cmd.CommandText.AppendLine("  WHERE MyString = @MyValue ");
    cmd.Parameters.AddWithValue("@MyValue", "abc");
    var data = cmd.ExecuteTable();
}

exec sp_executesql N' SELECT TOP 1 MyString
                        FROM MyTable
                       WHERE MyString = @MyValue
',N'@MyValue nvarchar(3)',@MyValue=N'abc'

My first reaction is “that’s correct and I don’t see how to optimize this query.”… But the question “Why this query is so slow” is always there !

The solution.

Many searches and checks later, we found this command CONVERT in SQL Server Profiler.

Eureka… The problem come from the AddWithValue method where we set a C# String parameter value… So the Unicode value is converted later by SQL Server to @MyValue nvarchar(3)’,@MyValue=N‘abc’.

The query can be optimized by setting the correct SqlType (VarChar and not NVarChar) when we define the parameter (or you can change the database structure using NVarchar, NChar and NText… but your database size will be increase).

var param = new SqlParameter()
{
    ParameterName = "@MyValue",
    SqlDbType = SqlDbType.VarChar,
    Value = "def"
};
cmd.Parameters.Add(param);

And without other changes, my application (on this query) is 3 times more fast.

Try your self.

If you want to try your self.

1. First, create a new table in a sample database.

CREATE TABLE MyTable (
  ID INT,
  MyString VARCHAR(80)
)

1. Execute this script to generate 1 million of rows.

DECLARE @row INT;
DECLARE @string VARCHAR(80), @length INT, @code INT;
SET @row = 0;
WHILE @row < 1000000 BEGIN SET @row = @row + 1; -- Build the random string SET @length = ROUND(80*RAND(),0); SET @string = ''; WHILE @length > 0 BEGIN
      SET @length = @length - 1;
      SET @code = ROUND(32*RAND(),0) - 6;
      IF @code BETWEEN 1 AND 26
         SET @string = @string + CHAR(ASCII('a')+@code-1);
      ELSE
         SET @string = @string + ' ';
   END

   -- Ready for the record
   SET NOCOUNT ON;
   INSERT INTO MyTable VALUES (@row, @string)
END

1. Create a C# Console project and use this code to execute a query with NVarchar (Unicode) parameter and with Varchar parameter.

const string CONNECTION_STRING = "Server=(localdb)\\ProjectsV12;Database=SCOTT;Trusted_Connection=True;";
const decimal NB_REQUESTS = 100;

var watcher = Stopwatch.StartNew();
Console.WriteLine(" Starting first request... using NVarChar.");
for (int i = 0; i < NB_REQUESTS; i++)
{
    using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
    {
        cmd.CommandText.AppendLine(" SELECT TOP 1 MyString FROM MyTable WHERE MyString = @MyValue ");
        cmd.Parameters.AddWithValue("@MyValue", "abc");
        var data = cmd.ExecuteTable();
    }
}
Console.WriteLine($"{watcher.ElapsedMilliseconds / NB_REQUESTS} ms by request.");

watcher.Restart();
Console.WriteLine(" Starting second request... using VarChar.");
for (int i = 0; i < NB_REQUESTS; i++)
{
    using (var cmd = new SqlDatabaseCommand(CONNECTION_STRING))
    {
        cmd.CommandText.AppendLine(" SELECT TOP 1 MyString FROM MyTable WHERE MyString = @MyValue ");
        var param = new SqlParameter()
        {
            ParameterName = "@MyValue",
            SqlDbType = SqlDbType.VarChar,
            Value = "def"
        };
        cmd.Parameters.Add(param);
        var data = cmd.ExecuteTable();
    }
}
Console.WriteLine($"{watcher.ElapsedMilliseconds / NB_REQUESTS} ms by request.");

And the result is… Amazing ;-)

In a future version of SqlDatabaseCommand, I’ll add a global property to automatically convert NVarChar, NChar and NText to equivalent VarChar, Char and Text. Your queries will be optimized easily.