一个清单改进的.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:
更改默认的超时时间在命令和连接通过avgbody.
Change default timeout in command and connection by avgbody.
使用存储过程中由avgbody.
查找阻塞/使用活动监视器由Jay牧羊人。
Look for blocking/locking using Activity monitor by Jay Shepherd.
SQL Server的:
当心参数由AlexCuse.
谨防由Martin克拉克。
使用探查发现有任何疑问/存储过程需要更长的时间,然后100毫秒BradO.
Use Profiler to find any queries/stored procedures taking longer then 100 milliseconds by BradO.
增加交易按avgbody.
将转换为动态存储过程转换成静态的由avgbody.
Convert dynamic stored procedures into static ones by avgbody.
检查服务器的繁忙是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.