检查表用于固定.NET应用程序的SQL Server超时问题，提高执行时间 [英] A checklist for fixing .NET applications to SQL Server timeout problems and improve execution time
问题描述
一个清单改进的.NET code和SQL Server的执行时间。 任何东西，从基础到怪异的解决方案是AP preciated。
A checklist for improving execution time between .NET code and SQL Server. Anything from the basic to weird solutions is appreciated.
code：
更改默认的超时时间在命令和连接通过<一个href="http://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67438">avgbody.
Change default timeout in command and connection by avgbody.
使用存储过程中由<一个调用，而不是内联SQL语句href="http://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67438">avgbody.
查找阻塞/使用活动监视器由<锁定href="http://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67387">Jay牧羊人。
Look for blocking/locking using Activity monitor by Jay Shepherd.
SQL Server的：
当心参数由<一嗅存储过程href="http://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#68183">AlexCuse.
谨防由<动态扩展数据库href="http://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67660">Martin克拉克。
使用探查发现有任何疑问/存储过程需要更长的时间，然后100毫秒<一href="http://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67598">BradO.
Use Profiler to find any queries/stored procedures taking longer then 100 milliseconds by BradO.
增加交易按<超时href="http://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67438">avgbody.
将转换为动态存储过程转换成静态的由<一个href="http://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67438">avgbody.
Convert dynamic stored procedures into static ones by avgbody.
检查服务器的繁忙是<一个href="http://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67387">Jay牧羊人。
推荐答案
在过去的我的一些解决方案已经：
In the past some of my solutions have been:
-
修正默认的超时的SqlCommand的设置：
Fix the default time out settings of the sqlcommand:
昏暗myCommand作为新的SqlCommand（[DBO]。[spSetUser preferences]，MyConnection的）
Dim myCommand As New SqlCommand("[dbo].[spSetUserPreferences]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.CommandType = CommandType.StoredProcedure
myCommand.CommandTimeout = 120
增加连接超时字符串：
数据源= MyDatabase的;初始目录=匹配;坚持安全信息= TRUE;用户ID =用户;密码=密码; 连接超时= 120
Data Source=mydatabase;Initial Catalog=Match;Persist Security Info=True;User ID=User;Password=password;Connection Timeout=120
增加交易时间在SQL服务器2005
Increase transaction time-out in sql-server 2005
在Management Studio中的工具>选项>设计师增加了交易超时后：即使占优表设计更新连接字符串超时值选中/取消。
In management studio’s Tools > Option > Designers Increase the "Transaction time-out after:" even if "Override connection string time-out value for table designer updates" checked/unchecked.
将转换为动态存储过程转换成静态的
Convert dynamic stored procedures into static ones
请在code调用写一个内联SQL语句在code的存储过程来代替。
Make the code call a stored procedure instead of writing an inline sql statement in the code.
这篇关于检查表用于固定.NET应用程序的SQL Server超时问题，提高执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
