SqlDataSource 超时。在管理工作室中确定
在我继承的代码中，我有一个 SqlDataSource，它有一个相当复杂的 select 语句，对于某些 SelectParameters，总是超时（“超时已过期。在操作或服务器完成之前超时时间已过”没有响应。”）。
当我在 Management Studio 中使用相同的参数运行完全相同的查询时，查询永远不会超时，并且总是花费不到一秒的时间。
有谁知道这里可能出现什么问题吗？我无法理解它。
In code that I inherited, I have a SqlDataSource that has a fairly complex select statement that for certain SelectParameters, always times out ("Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.").
When I run the exact same query with the same parameters in management studio, the query never times out and always takes less than a second.
Does anyone have an idea what the problem could be here? I can't make sense of it.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
只是瞎猜：参数实际上并不相同。在 SSMS 中，您为查询传递 ASCII 参数，而在 ADO.Net 中，您传递 Unicode 参数。当
myValue是字符串时，SqlCommand.Parameters.AddWithValue("@myParam", myValue)将添加 NVARCHAR 类型的参数。由于 SQL 中的转换规则，如果您有 SELECT ... FROM ... WHERE myField = @myParam 并且 myField 是 Ascii (VARCHAR) 并且 @myParam 是 Unicode (NVARCHAR)，则执行必须执行表扫描无法在 myField 上使用索引，导致与 SSMS 执行相比性能非常糟糕。正如我所说，这只是一个盲目的尝试，但却是一个常见的陷阱，而且调试起来相当微妙。
Just a shot in the dark: The parameters are not actually the same. In SSMS you pass in ASCII parameters for the query, while in ADO.Net you pass Unicode ones.
SqlCommand.Parameters.AddWithValue("@myParam", myValue)will add a parameter of type NVARCHAR whenmyValueis a String. Due to te conversion rules in SQL if you haveSELECT ... FROM ... WHERE myField = @myParamand myField is Ascii (VARCHAR) and @myParam is Unicode (NVARCHAR) then the execution must do a table scan, cannot use an index on myField, resulting in awfull performance when compared to SSMS execution.As I said, this is just a shot in the dark, but is a common pitfall and fairly subtle to debug.
可能是锁定/阻塞，如果人们正在数据库中工作，您的选择可能会等到他们的事务完成。超时可能会命中或错过，具体取决于数据库中的其他事务。
在 Management Studio 中，运行
SET SHOWPLAN_ALL ON，然后运行查询。在输出中查找“SCAN”。如果您进行表或索引扫描，您更有可能成为锁定/块的受害者，因为您必须处理整个索引/表，并且任何人锁定其中的行都会迫使您等待。当您运行应用程序并且屏幕刷新不快时，请在管理工作室中运行此操作：
它将为您提供一些基本信息以及当前正在进行的任何锁定。
could be locking/blocking, if people are doing work in the database your select may wait until their transaction is complete. The timeout would be hit or miss, depending on the other transactions in the database.
in management studio, run
SET SHOWPLAN_ALL ON, and then run your query. Look for "SCAN" in the output. If you have a table or index scan you are more likely to be a victim of locking/block, since you must process the entire index/table and anyone locking a row in there will force you to wait.when you run the application, and screen is not refreshing fast run this in management studio:
it will give you some basic info an any locking currently going on.
尝试以下操作，也许这会澄清发生了什么：
在 sql 探查器中，捕获复杂 SQL 语句转换成的精确语句，并在 Viual Studio 中运行它。
当有问题的sql语句运行时，去管理中查看活动监视器工作室。它可以让您了解什么可能会阻止 sql。
查看同时运行的其他内容非常重要。应用程序是多线程的吗？ sql连接在使用后是否立即关闭/处置（如果没有，可能无法及时关闭）？多个线程使用同一个 sql 连接吗？
Try following, maybe this will clarify what's going on:
In sql profiler, capture exact statement into which your complex SQL statement translates and run it in Viual Studio.
When the sql statement in question is running, go check activity monitor in management studio. It could give you an idea, what might be blocking the sql.
It's important to see what else is running at the same time. Is application multi-threaded? Is sql connection getting closed/disposed immediately after use (if not, it may not get closed timely)? Is same sql connection used by multiple threads?