sql server - .NET database calls slow when using COM Interop, fast via query analyser

Question

I have an Sql query that looks up a person based on SSN and returns the PersonID (identity column). There is a index on the SSN column in the persons table.

I have an old VB 6 application that uses COM/.NET interop to call this query. When it does it runs relatively slow. I set up a trace using SQL Profiler and each call has a duration between 400ms-600ms.

If I run the very same query via the query analyser, I get a duration < 30ms. I also have a ASP.NET web site that makes the same exact call and get durations less than 30ms.

Normally I would suspect that the COM/.NET interop overhead is creating the delay. However I'm getting the trace times out of SQL Profiler. I can't see how overhead on the client side would effect the numbers I'm getting out of a server-side database trace.

What else could be causing this issue?

EDIT:

I discovered the issue. I setup sql profiler to capture the execution plan and discovered that when the stored procedure was called via the VB app, the execution plan wasn't using the index on SSN. However when the same SP was called via asp.net or QA, the proper index was called. I sent a sp_recompile to the server, and from that point forward the VB app was running at adequate speed.

What I still don't understand, is why the VB app wasn't using the same cached query plan as the other clients.

Answer 1

Check the type of parameter (@SSN) you pass to SQL. More often than not the parameter is added like this:

List<...> GetBySSN(string ssn) {
   SqlCommand cmd = new SqlCommand (@"select ... from ... where SSN=@SSN", conn);
   cmd.Parameters.AddWithValue("@SSN", ssn);
   using (SqlDataReader rdr = cmd.ExecuteQuery()) {
     ...
   }
}

This pattern unfortunately adds the @SSN parameter as a NVARCHAR type (ie. Unicode). The rules of SQL Server Data Type Precedence require the comparison between a NVARCHAR and a VARCHAR to be done as NVARCHAR, so the query is executed as if the following SQL was requested:

select ... from ... where CAST(SSN as NVARCHAR) = @SSN;

This query cannot benefit from an index on the SSN column so a table scan is performed instead. 90% of the times I investigate the claim 'the query runs slow from app but fast from SSMS' is this problem, because the vast majority of developers actually run a different query in SSMS to compare with (they use a VARCHAR argument or a hard coded value).

If this is indeed the problem, the solution is trivial: explicitly specify the parameter type as SqlDbType.VarChar.