I managed to come across an unexpected issue today when using C# + MS SQL. I was writting a program that involved doing a lot of quries on a varchar field for merging data into a customer database. It did not perform as expected. Since i had already run my own test quries to make sure the index's had been created correctly and they were being used by the quries when executed.
The main problem when i started to run the program in C# it was only managing around 4-5 quries per second (i was expecting 1000's per second). This occured because the string's in c# was being converted to nvarchar using the SqlCommand.Parameters when it is passed to sql server.
For this example i have create a small test table in sql and a short c# program that does a search.
Our table for the test
CREATE TABLE [dbo].[Test](
[TestID] [uniqueidentifier] NOT NULL,
[Name] [varchar](255) NOT NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Test] ADD CONSTRAINT [DF_Test_TestID] DEFAULT (newid()) FOR [TestID]
And a short c# program.
static void Main(string args)
DBConn Conn = new DBConn("Data Source=SQL;Initial Catalog=Testing;Integrated Security=True;Connect Timeout=30;Timeout=120");
SqlCommand sql = new SqlCommand("SELECT * FROM Test WHERE Name = @Name");
So using the sql server profiler i was able to extract the query that was actually being executed. So here is what you would expect to be executed and what is really happening in reality.
SELECT * FROM Test
WHERE Name = 'Misc'
EXEC sp_executesql N'SELECT * FROM Test WHERE Name = @Name',
The above is only slightly different. Of course you can see the data converstion to nvarchar for the @Name parameter. However it results in the following different in performance in ms sql. Here they have the same timing's because i am only using a small amount of test data but in the real system they were 99% for the index scan and 1% for the index seek over approx 120k rows.
If oyu look into the details properties in the index scan it will show that it has the following
The solution to this problem is actually really simple. Add the following to control your Parameter types.
sql.Parameters["@Name"].SqlDbType = SqlDbType.VarChar;