sqlparameter 性能不佳

发布于 2024-12-22 10:35:06 字数 881 浏览 2 评论 0原文

我有一个 Web 服务，因此处理程序始终会同时被多次调用。

在里面我创建了 SqlConnection 和 SqlCommand。我必须执行大约 7 个不同的命令。不同的命令需要不同的参数，所以我只添加一次：

command.Parameters.Add(new SqlParameter("@UserID", userID));
command.Parameters.Add(new SqlParameter("@AppID", appID));
command.Parameters.Add(new SqlParameter("@SID", SIDInt));
command.Parameters.Add(new SqlParameter("@Day", timestamp.Date));
command.Parameters.Add(new SqlParameter("@TS", timestamp));

然后在执行过程中我只需更改 CommandText prorerty，然后调用 ExecuteNonQuery();或执行标量()；

我面临性能问题。例如，很少的调试和分析显示，该命令

command.CommandText = "SELECT LastShowTS FROM LogForAllTime WHERE UserID = @UserID";

平均需要大约 50 毫秒。如果我把它改成：

command.CommandText = "SELECT LastShowTS FROM LogForAllTime WHERE UserID = '" + userID.Replace("\'", "") + "'";

那么平均只需要1ms！

我只是不知道在哪里调查问题。

原文

I have a web service, so the handler is called multiple times concurrently all the time.

Inside I create SqlConnection and SqlCommand. I have to execute about 7 different commands. Different commands require various parameters, so I just add them once:

command.Parameters.Add(new SqlParameter("@UserID", userID));
command.Parameters.Add(new SqlParameter("@AppID", appID));
command.Parameters.Add(new SqlParameter("@SID", SIDInt));
command.Parameters.Add(new SqlParameter("@Day", timestamp.Date));
command.Parameters.Add(new SqlParameter("@TS", timestamp));

Then during execution I just change CommandText prorerty and then call ExecuteNonQuery(); or ExecuteScalar();

And I face performance issue.
For example little debuggin and profiling shows, that command

command.CommandText = "SELECT LastShowTS FROM LogForAllTime WHERE UserID = @UserID";

takes about 50ms in avarage. If I change it to:

command.CommandText = "SELECT LastShowTS FROM LogForAllTime WHERE UserID = '" + userID.Replace("\'", "") + "'";

then it takes only 1ms in avarage!

I just can't get a clue where to investigate the problem.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

执手闯天涯 2024-12-29 10:35:07

听起来它已经缓存了一个非典型的 @UserID 值（早期的值之一）的查询计划，并且正在为以后的查询重用一个糟糕的计划。在第二种情况下这不是问题，因为每个人都有单独的计划。我怀疑您只需要

OPTION (OPTIMIZE FOR UNKNOWN)

在查询中添加：，这将使其不那么热衷于盲目地重用计划。

另一种理论：

userID 的类型（在 C# 中）和 UserID 的类型（在数据库中）可能不匹配。这可以像 unicode 与 ANSI 一样简单，也可以是 int 与 varchar[n] 等。如果有疑问，请非常具体配置参数时，添加正确的子类型和大小。

澄清

事实上，看起来这里的问题是 C# string (unicode) 和数据库 varchar(n) (ANSI) 之间的差异。因此，应显式添加 SqlParameter (DbType.AnsiString)。

That sounds like it has cached a query-plan for an atypical @UserID value (one of the early ones), and is reusing a poor plan for later queries. This isn't an issue in the second case since each has a separate plan. I suspect you just need to add:

OPTION (OPTIMIZE FOR UNKNOWN)

to the query, which will make it less keen to re-use plans blindly.

Alternative theory:

You might have a mismatch between the type of userID (in the C#) and the type of UserID (in the database). This could be as simple as unicode vs ANSI, or could be int vs varchar[n], etc. If in doubt, be very specific when configuring the parameter, to add it with the correct sub-type and size.

Clarification

Indeed, it looks like the problem here is the difference between a C# string (unicode) and the database which is varchar(n) (ANSI). The SqlParameter should therefore be explicitly added as such (DbType.AnsiString).

回复收藏 0 原文