执行时间具有相同的存储过程的每个迭代较慢

本文介绍了执行时间具有相同的存储过程的每个迭代较慢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 从C＃.NET应用程序的在网络上运行同一存储过程变得越来越慢，每个后续执行。它似乎采取 两倍的时间量为previous执行（多达最大值，继续阅读）。的执行时间变得越来越慢，直到1 2的情景发生，在该点处SPROC的第一次执行是快再次如果一个的SqlConnection 打开并保持在所有测试开启，该存储过程变得越来越慢，直到其他存储过程或查询运行。如果一个的SqlConnection 打开，周围的每个执行关闭，存储过程变得越来越慢，直到至少8分已经过去了。这只是发生在几个存储过程。一个是一个简单的 SELECT 与2 加入，（存储过程1 的），另一种是查询大量1600+线存储过程（存储过程2 的）。时代似乎永远超越的的存储过程1 的67秒正好60秒，为的存储过程2 的执行。 存储过程1 的时间不超过一秒钟开始执行，存储过程2 的需要7秒开始。如果该存储过程使用相同的的SqlConnection 在应用程序中运行这还只是发生。只要2个独立的的SqlConnection 物体时，它们表现为如上所述相同，但都是独立的。在 SqlConnection1 被越来越慢，但第一次相同的存储过程运行在 SqlConnection2 ，它的运行存储过程多次快速。然后，它也将得到更慢 SqlConnection2 多次运行时。如果在应用程序运行在同一台计算机上的SQL Server 2008 R2安装（运行Windows Server 2008），这不会发生。执行时间始终保持一致。从Management Studio中运行存储过程也不会变慢每次执行;它始终是一致的。清除执行计划缓存（在SQL Server）对观察到的行为没有任何影响。它采取了好几天来缩小这个问题最初是在一个更大的应用观察，以创建一个测试应用程序，可方便地检测/重现出来。这是我读过的这里，有4-8分钟的超时（后在SQLConnection.close（）被称为code），此时它会关闭数据库连接到数据源。这似乎符合方案2我上面提到的。这使我相信它是关系到的SqlConnection 使用（和底层数据库连接到数据源），因为连接池在我的情况下，已启用，但为什么我观察这种行为，如何解决呢？我们使用的是.NET 2.0框架，如果有什么差别。有上述许多细微之处，所以请让我知道如果我需要澄清什么。唯一的堆栈溢出的问题与任何相似之处是this,但无关我的问题。 编辑：下面code是我的WinForms测试应用程序在启动时执行： SqlConnectionStringBuilder connectionStringBuilder =新SqlConnectionStringBuilder（）;connectionStringBuilder.DataSource = m_DataSource;connectionStringBuilder.InitialCatalog = m_InitialCatalog;connectionStringBuilder.UserID = m_UserID;connectionStringBuilder.Password = m_Password;connectionStringBuilder.IntegratedSecurity = FALSE;的connectionString = connectionStringBuilder.ConnectionString;m_DatabaseConnection =新的SqlConnection（的connectionString）;然后我有2个按钮;其中的一个要求的 SPROC 1 的上述和其他调用一个不同的存储过程不具有相同的减速的问题。 ：下面$ C $，C是任一按钮点击（唯一的区别是存储过程的名称）执行 m_DatabaseConnection.Open（）;m_DatabaseCommand =新的SqlCommand（GetCompanies，m_DatabaseConnection）;m_DatabaseCommand.Parameters.AddWithValue（@ StatusID，StatusID）;m_DatabaseCommand.CommandType = CommandType.StoredProcedure;m_DatabaseCommand.CommandTimeout = 0;SqlDataAdapter的databaseDataAdapter =新的SqlDataAdapter（m_DatabaseCommand）;数据集databaseDataSet =新的DataSet（）;databaseDataAdapter.Fill（databaseDataSet）;m_DatabaseConnection.Close（）;解决方案 下面是我的想法来调试此问题：尝试调用SqlConnection.ClearAllPools（）报废连接后。如果这能解决问题，这个问题是联系在一起肯定的特定连接。接下来，请用显式事务的存储过程。接下来，调用存储过程之前调用SqlConnection.ClearAllPools（）。在多少数据并在存储过程返回？请张贴您使用打开连接并执行存储过程的C＃code。创建一个独立的控制台应用程序正在播放你所看到的行为。这将（可能）证明的东西在你的应用程序的问题，因为控制台应用程序将运行得很好。Running the same Stored Procedure from C# .Net application over a network gets progressively slower with each subsequent execution. It appears to take twice the amount of time as the previous execution (up to a max value; read on). The execution time becomes progressively slower until 1 of 2 scenarios happens, at which point the first execution of the SPROC is "fast" again.If an SqlConnection is opened and remains open during all testing,the SPROC gets progressively slower until any other SPROC or query is run.If an SqlConnection is opened and closed around each execution, theSPROC gets progressively slower until at least 8 minutes has passed.This only happens with a few Stored Procedures. One is a simple SELECT query with 2 JOINs, (SPROC 1) another is a massive 1600+ line SPROC (SPROC 2).The execution times appear to never go beyond exactly 60 seconds for SPROC 1 and 67 seconds for SPROC 2. SPROC 1 takes less than a second to execute initially, and SPROC 2 takes 7 seconds initially.This also only happens if the SPROC is run using the same SqlConnection in the application. As soon as 2 separate SqlConnection objects are used, they behave the same as stated above, but are independent. Running the SPROC multiple times on SqlConnection1 gets progressively slower, but the first time the same SPROC is run on SqlConnection2, it's "fast". It will then also get slower when run multiple times on SqlConnection2.This does not happen if the application is run on the same computer with SQL Server 2008 R2 installed (running Windows Server 2008). The execution time is always consistent.Running the Stored Procedure from within Management Studio also does not get slower with each execution; it is always consistent.Clearing the execution plan cache (in SQL Server) has no effect on the observed behavior.It has taken quite a few days to narrow down this issue originally observed in a much larger application, in order to create a test app to easily test/reproduce it.From what I've read here, there is a timeout of between 4 and 8 minutes (after SqlConnection.Close() is called in code) at which point it closes the database connection to the data source. This appears to be in line with the scenario 2 I mentioned above.This leads me to believe it is related to the SqlConnection used (and the underlying database connection to the data source) since connection pooling is enabled in my case, but why am I observing this behavior, and how do I fix it?We are using the .Net 2.0 Framework, if that makes any difference.There are many fine details listed above, so please let me know if I need to clarify anything.The only Stack Overflow question with any similarities is this, but was unrelated to my issue.Edit:The following code is executed in my WinForms test app on startup:SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();connectionStringBuilder.DataSource = m_DataSource;connectionStringBuilder.InitialCatalog = m_InitialCatalog;connectionStringBuilder.UserID = m_UserID;connectionStringBuilder.Password = m_Password;connectionStringBuilder.IntegratedSecurity = false;connectionString = connectionStringBuilder.ConnectionString;m_DatabaseConnection = new SqlConnection(connectionString);I then have 2 buttons; one of which calls SPROC 1 mentioned above, and the other calls a different SPROC which does not have the same slowdown issue. The following code is executed on either button click (only difference being the SPROC name):m_DatabaseConnection.Open();m_DatabaseCommand = new SqlCommand("GetCompanies", m_DatabaseConnection);m_DatabaseCommand.Parameters.AddWithValue("@StatusID", StatusID);m_DatabaseCommand.CommandType = CommandType.StoredProcedure;m_DatabaseCommand.CommandTimeout = 0;SqlDataAdapter databaseDataAdapter = new SqlDataAdapter(m_DatabaseCommand);DataSet databaseDataSet = new DataSet();databaseDataAdapter.Fill(databaseDataSet);m_DatabaseConnection.Close(); 解决方案 Here are my ideas to debug this problem:Try calling SqlConnection.ClearAllPools() after Disposing the connection. If this fixes the problem, the problem is tied to a particular connection for sure.Next, enclose the SPROC in an explicit transaction.Next, call SqlConnection.ClearAllPools() before invoking the SPROC.How much data does the SPROC return?Please post the C# code you are using to open the connection and execute the SPROC.Create a standalone console app that is reproducing the behavior you are seeing. This will (likely) prove that something in your app is the problem because the console app will run just fine. 这篇关于执行时间具有相同的存储过程的每个迭代较慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！