A while back I created an application that syncs an Exact e-Synergy database with a database that belongs to a system that builds upon data from the e-Synergy database but has to run disconnected from the database cluster that holds the e-Synergy database.

Due to all sorts of restrictions and logic data-integrity rules that are not implemented directly in the e-Synergy database, the queries tend to get a bit complex at times. Even though the databases are hosted in database clusters that pack a pretty punch, performance is critical. After writing the software and the required queries, the optimizing began, creating indexes and reordering where statements and joins for optimal index usage.

While I believed we had things covered, during maintenance today I learned that a set of queries took over 300ms when they should be done in under a single millisecond. After running the SQL Server Profiler, I was able to isolate the queries that were taking to long and executed it in the SQL Management Studio 2005 with the option enabled to view the execution plan.

After taking a look at the execution plan, I noticed that SQL Server didn’t use the index I would suspect he would use. The index that offered the most benefit wasn’t used. I’ve noticed this behavior before when you use a function on a column, indexes cannot be used either and an indexed view may be an option. In my case, I didn’t perform a function in my where statement, so SQL server had to wrap a function in there on it’s own, which eliminated an essential column from being used by the right index and in my case, use the wrong index. When looking a bit closer, I noticed that this was due to the fact that SQL Server puts a CONVERT_IMPLICIT in the T-SQL code when executing it. The server did this because my field in the table was of SQL type VarChar, and that by using .NET’s Parameters.AddWithValue() function, all strings get added as NVarChar SQL type (ASCII Vs. Unicode).

By rewriting the piece of code that uses .AddWithValue() to a specific Parameters.Add() with a parameter of SQL type VarChar, the problem is solved, the correct index is used and execution time was 450 times faster, so beware that when you use VarChar fields in your database that you don’t use Parameters.AddWithValue() on strings since they get added as NVarChar’s.