检索&更新20000条数据记录停止工作

发布于 2024-09-25 06:15:46 字数 1797 浏览 3 评论 0原文

我使用以下代码从 MS SQL 数据库获取所有数据记录，并尝试更新每条记录。该代码在 WebService 中使用。问题是，如果我有 1000 条数据记录，代码运行良好，但现在我有 20000 条数据记录，并且代码首先返回超时。然后我将 cmd.CommandTimeout 设置为零以没有超时。现在，当我在 IE WebSvc 中调用该函数时，IE 窗口仍然是空白的，并且仍然尝试加载某些内容，但没有任何反应。仅更新了 150 条数据记录。

您知道问题可能出在哪里吗？代码不是最好的，那么我应该改变什么？

非常感谢！ WorldSignia

我的代码：

 private string AddNewOrgBez()
        {
            try
            {
                SqlConnection sqlconn = new SqlConnection(this.connectionString);
                SqlCommand cmd;
                SqlDataReader reader;
                sqlconn.Open();

                cmd = new SqlCommand("SELECT * FROM dbo.mydata", sqlconn);
                cmd.CommandTimeout = 0;
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    // Felder holen
                    string okuerzel = reader["O_KURZ"].ToString();
                    string bezeichnung = reader["O_BEZ"].ToString();

                    string[] lines = CreateNewOrgBez(bezeichnung);

                    string sqlcmd = "UPDATE dbo.mydata SET WEB_OBEZ1 = '" + lines[0] + "', WEB_OBEZ2 = '" + lines[1] + "', WEB_OBEZ3 = '" + lines[2] + "' WHERE O_KURZ = '" + okuerzel + "'";

                    SqlConnection sqlconn2 = new SqlConnection(this.connectionString);
                    sqlconn2.Open();
                    SqlCommand cmd2 = new SqlCommand(sqlcmd, sqlconn2);
                    cmd2.CommandTimeout = 0;
                    cmd2.ExecuteNonQuery();
                    sqlconn2.Close();
                }

                reader.Close();
                sqlconn.Close();

                return "OK";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

原文

I am using the following code do get all data records from a MS SQL database and I try to update every single record. The code is used in a WebService. The issue is, that the code runs fine if I have 1000 data records but now I have 20000 data records an the code first returned with an timeout. Then I set the cmd.CommandTimeout to zero to have no timeout. Now when I invoke the function in the IE WebSvc the IE window is still blank and still try to load something but nothing happens. Only 150 datarecords are updated.

Do you have any idea where the issue might be ? Is the code not the best, so what should I change ?

Thank you very much!
WorldSignia

MyCode:

 private string AddNewOrgBez()
        {
            try
            {
                SqlConnection sqlconn = new SqlConnection(this.connectionString);
                SqlCommand cmd;
                SqlDataReader reader;
                sqlconn.Open();

                cmd = new SqlCommand("SELECT * FROM dbo.mydata", sqlconn);
                cmd.CommandTimeout = 0;
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    // Felder holen
                    string okuerzel = reader["O_KURZ"].ToString();
                    string bezeichnung = reader["O_BEZ"].ToString();

                    string[] lines = CreateNewOrgBez(bezeichnung);

                    string sqlcmd = "UPDATE dbo.mydata SET WEB_OBEZ1 = '" + lines[0] + "', WEB_OBEZ2 = '" + lines[1] + "', WEB_OBEZ3 = '" + lines[2] + "' WHERE O_KURZ = '" + okuerzel + "'";

                    SqlConnection sqlconn2 = new SqlConnection(this.connectionString);
                    sqlconn2.Open();
                    SqlCommand cmd2 = new SqlCommand(sqlcmd, sqlconn2);
                    cmd2.CommandTimeout = 0;
                    cmd2.ExecuteNonQuery();
                    sqlconn2.Close();
                }

                reader.Close();
                sqlconn.Close();

                return "OK";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半仙 2024-10-02 06:15:46

您在这里泄漏了每个 SqlCommand - 我建议您检查对 SqlClient 类的使用，以找到 IDisposable 的类并重新构造您的代码以确保使用使用构建。

例如，即使括号内的代码中存在异常，这也可以确保调用 Dispose：

using (SqlCommand cmd2 = new SqlCommand(sqlcmd, sqlconn2))
{
  cmd2.CommandTimeout = 0;
  cmd2.ExecuteNonQuery();
}

为每个 UPDATE 使用新的 SqlConnection 也很昂贵，这应该在环形。冗余连接建立可能是超时的原因。

请注意@ck 的评论，即就效率而言，这种零碎的客户端操作不如在服务器端执行繁重的操作。您应该能够使此代码更好地工作，但这并不意味着它是理想/最快的解决方案。

You are leaking every SqlCommand here - I suggest you review your use of SqlClient classes to find the ones that are IDisposable and restructure your code to ensure they are always freed, using the using construct.

For example, this ensures Dispose gets called even if there is an exception in the bracketed code:

using (SqlCommand cmd2 = new SqlCommand(sqlcmd, sqlconn2))
{
  cmd2.CommandTimeout = 0;
  cmd2.ExecuteNonQuery();
}

Using a new SqlConnection for every UPDATE is expensive too, this should be done outside the loop. Redundant connection establishment is likely the explanation for your timeout.

Take note of @ck's comment that for efficiency this type of piecemeal client-side operation is not as good as doing the heavy lifting server-side. You should be able to get this code working better, but that does not mean it's the ideal/fastest solution.

回复收藏 0 原文