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

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 exe开发者_运维问答cution 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.

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.