C# - SQL - 读取数据库中的每一行，对行进行数据挖掘，然后将结果保存在另一个数据库中 - 如何提高速度

Question

我的问题:代码运行良好，但速度对于它需要处理的行数来说太慢了。

我在做什么:我启动了一个 COUNT(*) 来提取总行数(昨晚是 ~58000)，我用它来创建循环来执行以下操作:从该行提取两列数据, 为文本模式挖掘一列。

完成后，我搜索第二个表以查看该用户名的个人是否已经存在 - 如果存在，我将更新他们的行。如果没有，我会添加一个新的。

有 44 列数据，一列是名称，另外 43 列存储我的数据挖掘结果的值。

在大约 8 小时内，它完成了刚开始时的 58000 中的 26500(同期，该表已增长到 ~100000，但我并不担心)。

有没有更好的方法提高读写速率？

我的部分代码 - 我删除了许多 int 声明和 Regex.Matches，因为它们是第一个具有更改的 Match 值的副本。

azCheck 是为了确定消息是否甚至包含我们要查找的任何内容，它仍然是“0”，那么我们就不必理会代码的最后一部分。

using (new MySqlConnection(ConnectiongString))
            {
                using (MySqlCommand cmd = new MySqlCommand("select count(*) from messages", connection))
                {
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        StringBuilder sb = new StringBuilder();
                        while (reader.Read())
                        {
                            sb.Append(reader.GetInt32(0).ToString());
                        }
                        total_messages = int.Parse(sb.ToString());
                    }
                }
            }
            Console.WriteLine(total_messages.ToString());
            connection.Close();
            for (int i = 1; i <= total_messages; i++)
            {
                connection.Open();
                using (new MySqlConnection(ConnectiongString))
                {
                    using (MySqlCommand cmd = new MySqlCommand("select * from messages WHERE id="+i+"", connection))
                    {
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            StringBuilder sb = new StringBuilder();
                            while (reader.Read())
                            {
                                username = reader["username"].ToString();
                                message = reader["message"].ToString();
                            }
                        }
                    }
                }
                connection.Close();

                Console.Write("\r{0}   ", i);

                int aiCount = 0;
                aiCount += Regex.Matches(message, "ai", RegexOptions.IgnoreCase).Count;
                azCheck += aiCount;

//There are ~42 of the regex.matches after the first one.

MySqlCommand cmd1 = connection.CreateCommand();
                connection.Open();
                cmd1.CommandText = "SELECT username FROM users";
                cmd1.CommandType = CommandType.Text;
                cmd1.Connection = connection;
                MySqlDataReader dr = cmd1.ExecuteReader();
                while (dr.Read())
                {
                    if (dr[0].ToString() == username)
                    {
                        check++;
                    }
                }
                connection.Close();

if (check == 0)
                {
                    MySqlConnection connection2 = new MySqlConnection(ConnectiongString);
                    connection2.Open();
                    try
                    {
                        MySqlCommand cmd2 = connection2.CreateCommand();
                        cmd2.CommandText = "INSERT INTO users (username,aiCount) VALUES (@username,@aiCount)";
                        cmd2.Parameters.AddWithValue("@username", username);
                        cmd2.Parameters.AddWithValue("@aiCount", aiCount);
                        cmd2.ExecuteNonQuery();
                        connection2.Close();


                    }
                    catch (Exception)
                    {
                        throw;
                    }

} else {

int aiCount_old = 0;

if (azCheck > 0)
                    {

//Here we are taking the existing values from this users row, 
//which we then add the new values from above and save.

                        MySqlConnection connection4 = new MySqlConnection(ConnectiongString);
                        connection4.Open();
                        try
                        {
                            MySqlCommand cmd2 = connection4.CreateCommand();
                            cmd2.CommandType = CommandType.Text;
                            cmd2.CommandText = "SELECT * from users WHERE username = @username";
                            cmd2.Parameters.AddWithValue("@username", username);
                            MySqlDataReader reader = cmd2.ExecuteReader();
                            while (reader.Read())
                            {
                                aiCount_old = Convert.ToInt32(reader["aiCount"].ToString());
}

                        }
                        catch (Exception)
                        {
                            throw;
                        }
                        connection4.Close();

                        aiCount += aiCount_old;

MySqlConnection connection5 = new MySqlConnection(ConnectiongString);

                        connection5.Open();
                        try
                        {
                            MySqlCommand cmd4 = connection5.CreateCommand();
                            cmd4.CommandType = CommandType.Text;
                            cmd4.CommandText = "UPDATE users SET aiCount = @aiCount WHERE LOWER(LTRIM(RTRIM(username))) = @username";
                            cmd4.Parameters.AddWithValue("@username", username.Trim().ToLower());
                            cmd4.Parameters.AddWithValue("@aiCount", aiCount.ToString());
                              cmd4.ExecuteNonQuery();
                            Console.WriteLine("User updated.");

                        }
                        catch (Exception ex)
                        {
                            throw;
                        }
                        connection5.Close();

Answer 1

我可以立即发现您的一些低效之处。

您不断地打开和关闭您的连接字符串。这可能是你最大的瓶颈。打开一次连接，然后在所有处理完成后将其关闭一次，您可能会看到性能大幅提高。

您还可以使用不同的连接对象，这将减少您打开和关闭连接的需要。

你好像也对连接对象上的“using”有误解。我看到 using (new MySqlConnection(ConnectiongString))，但该代码完全没用，因为它除了初始化一个连接对象外没有做任何事情，该对象立即丢失，因为它没有分配给对象.

由于您是按顺序处理所有内容，因此在每种情况下都使用 connection 作为您的连接对象，仅在处理开始时打开它，并在处理完成时关闭它，然后执行 Dispose 方法(using 语句的要点)。

仅此一项更改就可以将处理时间减少一个数量级。

注意:如果您需要在数据读取器打开时进行更新或其他查询，则需要为数据读取器建立单独的连接。