That was the question I recently got from a customer: they had two systems which send comparable queries to the same SQL Server database, and the system using ADO.Net was 'significantly slower' than the other system.
OK, first thing to do if such a question pops up is to run the profiler to measure the difference. And indeed, we saw two queries retrieving the same data, but the one from ADO.Net was more than a factor 20 slower than the other. Very striking was the huge number of page requests (page reads in profiler, or using SET STATISTICS IO ON): the fast query accessed about 100 pages, the ADO.Net one read more than 15000 pages!
The fast query is
exec sp_executesql N'SELECT * FROM dbo.adonetdemo WHERE trackingstring = ''4E0A-4F89-AE'''
The ADO.Net query is
exec sp_executesql N'SELECT * FROM dbo.adonetdemo WHERE trackingstring = @trackingid',
N'@trackingid NVARCHAR', @trackingid = '4E0A-4F89-AE'
My first guess was: wrong execution plan. Either outdated statistics or wrong execution plans can cause a lot of unnecessary data access... So we looked at the execution plans (in Management studio, Ctrl+M and running the query) and noticed... no immediate difference! Both queries used the same indexes in the same order: first the non-clustered index on trackingstring to resolve the where clause, then doing a lookup in the clustered index to retrieve all the columns on the selected row (select *):
The problem identified
The only indication that something weird was going on, was that the cost of the row lookup was estimated at 93% of the cost in the fast query, and only 9% of the cost in the slow ADO.Net query. Only upon closer investigation of the execution plan, we noticed a small but important difference: the slow query did use an implicit operator: converting the trackingstring column (of type varchar) to nvarchar. In other words, it picked the smallest index that contained the varchar column, and converted every string to nvarchar before comparing it with the varchar parameter we provided in the query!
The real problem was the ADO.Net code, which looked like this:
cmd.CommandText = "SELECT * FROM dbo.adonetdemo WHERE trackingstring = @tstring";
cmd.Parameters.AddWithValue("tstring","4E0A-4F89-AE");
Since strings in .Net are unicode, these types are mapped onto NVARCHAR, and if the underlying column happens to be of type VARCHAR, SQL Server will convert the full column to NVARCHAR before using the comparison operator, on every query again! This causes a lot of unnecessary IO, if we know that the input string can easily be written as VARCHAR. In other words: dropping the N from the NVARCHAR variable declaration improved the performance of our query by at least a factor 20!
The solution
There is nothing wrong with ADO.Net if you use it in the right way, but as the above example illustrates, it is easy to shoot yourself in the foot if you don't pay attention. For this example, there are three solutions which would have avoided the problem:
- Using stored procedure: if our query was embedded in a stored proc with a VARCHAR parameter, no harm would be done.
- Using typed datasets: if we had used typed datasets and/or typed table adapters, these would have queried the table metadata before and would have generated the right queries. Be careful though if you later on change a type from NVARCHAR to VARCHAR without regenerating the typed TableAdapters.
- Setting the parameter type in .Net: the Parameter objects in a SqlCommand have a DbType property, with which we can signal the appropriate type:
cmd.CommandText = "SELECT * FROM dbo.adonetdemo WHERE trackingstring = @tstring";
cmd.Parameters.AddWithValue("tstring","4E0A-4F89-AE");
cmd.Parameters[0].DbType = System.Data.DbType.AnsiString;
Nico Jacobs