使用COM互操作时，.NET数据库调用速度慢，通过查询分析器快 [英] .NET database calls slow when using COM Interop, fast via query analyser

问题描述

我有一个SQL查询查找基于SSN的人，并返回PERSONID（标识列）。有论者表中的SSN列的索引。

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.

我有一个使用的COM / .NET互操作调用此查询旧的VB 6应用程序。当它运行速度相对较慢。我成立了一个跟踪使用SQL事件探查器，每个呼叫有400毫秒，600毫秒之间的持续时间。

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.

如果我运行通过查询分析器非常相同的查询，我得到一个持续时间LT; 30毫秒。我也有一个ASP.NET web站点，使完全相同的电话，得到大于30ms少的持续时间。

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.

通常我会怀疑COM / .NET互操作的开销是创建延迟。不过我发现了一丝超时SQL事件探查器中。我看不出开销在客户端会影响我得到了一个服务器端的数据库跟踪的数字。

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?

编辑：

我发现了问题。我设置SQL事件探查器捕获执行计划，并发现，当存储过程是通过VB应用程序调用，执行计划不使用SSN上的索引。但是，当相同的SP是通过asp.net或QA叫，适当的指数被调用。我送一个给的sp_recompile服务器，并从该点前进的VB应用程序，在适当的速度运行。

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.

我还是不明白什么，就是为什么VB应用程序没有使用相同的缓存查询计划的其他客户端。

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

推荐答案

检查参数（@SSN）的传递给SQL类型。更多的往往不是参数添加这样的：

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()) {
     ...
   }
}

此模式遗憾的是增加了 @SSN 参数为 NVARCHAR 键入（即统一code） 。 SQL Server的数据类型$ P ​​$ pcedence 的要求之间的比较的规则一个NVARCHAR和VARCHAR要做为NVARCHAR，因此在执行的查询，如果请求的SQL语句：

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;

该查询无法从索引中受益的SSN列，以便执行表扫描来代替。 90％我调查要求查询运行缓慢的应用程序，但很快从SSMS的时代是这样的问题，因为绝大多数开发者实际运行的在SSMS不同的的查询与对比（它们使用VARCHAR参数或硬codeD值）。

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).

如果这确实是问题，解决的办法很简单：明确指定参数类型的 SqlDbType.VarChar 。

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

这篇关于使用COM互操作时，.NET数据库调用速度慢，通过查询分析器快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！