查询在 SSMS 中需要 1.5 秒，但从 .NET Windows 应用程序运行时超时？
当我从 Windows 应用程序运行查询时，我有一个查询超时。然而，当我直接在 SSMS 中运行它时，它运行良好，不到 2 秒。所有其他查询在应用程序中运行良好。我还运行了探查器，并确认该应用程序确实正在运行我期望的查询（当我获取探查器显示的由应用程序运行的代码并在 SSMS 中运行它时，需要不到 2 秒的时间）。
运行时间较长的查询不会在应用程序中超时。
到底是怎么回事？
string result = dbHelper.GetTypedDataTable(dtMyProjects, "sp_get_DashboardPMMyProjects", MyUtility.DWConnection, new string[] { "'" + shortNTID + "'" });
static public string GetTypedDataTable(DataTable dt, string sSPName, string connectionName, string[] arrayParameters)
{
if (connectionName == "default")
{
SetUpConnection(sSPName, "sqlConn.ConnectionString", "SP");
}
else
{
SetUpConnection(sSPName, connectionName, "SP");
}
cmdSELECT.CommandText += " " + arrayParameters[0].ToString();
int countParameters = arrayParameters.Length;
for (int i = 1; i < countParameters; i++)
{
cmdSELECT.CommandText += ", " + arrayParameters[i].ToString();
}
return FillDataTable(dt);
}
private static void SetUpConnection(string sSPName, string connectionName, string sqlType)
{
//sqlConn.ConnectionString = ((string)(configurationAppSettings.GetValue("sqlConn.ConnectionString", typeof(string))));
sqlConn.ConnectionString = ((string)(configurationAppSettings.GetValue(connectionName, typeof(string))));
daHlpr.SelectCommand = cmdSELECT;
cmdSELECT.Connection = sqlConn;
if (sqlType == "SP")
{
cmdSELECT.CommandText = "EXEC " + sSPName;
}
else
{
cmdSELECT.CommandText = sSPName;
}
}
private static string FillDataTable(DataTable dt)
{
try
{
daHlpr.SelectCommand.CommandTimeout = 60;
daHlpr.Fill(0, 1000000000, dt);
}
catch (Exception ex)
{
return ex.Message.ToString();
}
return dt.Rows.Count.ToString(); //return the count of rows in the table being returned
}
I have a query that is timing out when I run it from my windows application. However it runs fine, in under 2 seconds when I run it directly in SSMS. All other queries run fine in the app. I have also run profiler and confirmed that the app is indeed running the query I expect it to (when I take the code that profiler shows was run by the app and run it in SSMS, it takes less than 2 seconds).
Longer running queries do not time out in the app.
What is going on?
string result = dbHelper.GetTypedDataTable(dtMyProjects, "sp_get_DashboardPMMyProjects", MyUtility.DWConnection, new string[] { "'" + shortNTID + "'" });
static public string GetTypedDataTable(DataTable dt, string sSPName, string connectionName, string[] arrayParameters)
{
if (connectionName == "default")
{
SetUpConnection(sSPName, "sqlConn.ConnectionString", "SP");
}
else
{
SetUpConnection(sSPName, connectionName, "SP");
}
cmdSELECT.CommandText += " " + arrayParameters[0].ToString();
int countParameters = arrayParameters.Length;
for (int i = 1; i < countParameters; i++)
{
cmdSELECT.CommandText += ", " + arrayParameters[i].ToString();
}
return FillDataTable(dt);
}
private static void SetUpConnection(string sSPName, string connectionName, string sqlType)
{
//sqlConn.ConnectionString = ((string)(configurationAppSettings.GetValue("sqlConn.ConnectionString", typeof(string))));
sqlConn.ConnectionString = ((string)(configurationAppSettings.GetValue(connectionName, typeof(string))));
daHlpr.SelectCommand = cmdSELECT;
cmdSELECT.Connection = sqlConn;
if (sqlType == "SP")
{
cmdSELECT.CommandText = "EXEC " + sSPName;
}
else
{
cmdSELECT.CommandText = sSPName;
}
}
private static string FillDataTable(DataTable dt)
{
try
{
daHlpr.SelectCommand.CommandTimeout = 60;
daHlpr.Fill(0, 1000000000, dt);
}
catch (Exception ex)
{
return ex.Message.ToString();
}
return dt.Rows.Count.ToString(); //return the count of rows in the table being returned
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如今，两秒已经是相当长的时间了，肯定需要大量的处理。
如果您使用存储过程，则可能需要禁用参数嗅探。
基本上，在 SP 执行后立即将每个 SP 参数存储在新实例化的变量中。这是包含更多详细信息的链接：
http://sqlserverperformance.wordpress.com/2007/06/07/how-to-disable-parameter-sniffing-in-sql-server-2005/
另外，虽然这看起来很明显，但有时这很容易被忘记。这些问题可能会在开发计算机重新启动、Sql Server 服务重新启动或 Sql Server 计算机重新启动时消失。
Two seconds is a pretty long time these days, there must be a decent amount of processing required.
If you are using a stored procedure, you may need to disable parameter sniffing.
Basically, store each SP parameter in a newly instantiated variable immediately upon SP execution. Here's a link with more details:
http://sqlserverperformance.wordpress.com/2007/06/07/how-to-disable-parameter-sniffing-in-sql-server-2005/
Also, though it may seem obvious, sometimes it is easy to forget. These problems may evaporate upon Dev Machine Reboot, Sql Server Service Restart, or Sql Server Machine Reboot.
尝试删除并重新创建存储过程。
我的类似帖子
try dropping and recreating the stored procedure.
My similar post
您是否正确执行了读取器或非读取器命令？诸如此类的简单拼写错误可能会导致它在等待插入或更新查询响应时超时。某些代码也可能有助于查找问题。
Are you correctly executing a reader or non reader command? A simple typo such as this can cause it to timeout waiting for a response an an insert or update query. Some code might also help with finding the issue.
SQL Server 以最大 RAM 运行，随着时间的推移，越来越多的查询永远无法运行。最终没有人会跑。回收了 SQL Server 来回收内存，现在我必须诊断为什么它使用了这么多 RAM。谢谢大家！
SQL Server was running at maximum RAM and as the day progressed, more and more queries were taking forever. Eventually none would run. Recycled SQL Server to reclaim the memory, and now I will have to diagnose why it was using so much RAM. Thanks all!