数据库更新效率测试

有个项目记录采集点的实时数据（mysql5.7），约5000条记录，在C#的DataTable更新好后，一次性Update到表中，但速度非常慢要4分钟左右，但程序定时更新为5分钟，经常超时，也找不到好的办法，最后把这个表改为Memory引擎后速度提高到5秒左右，总算解决了问题。

因为此，在本机做了些测试，为以后的数据库选型做个准备，以下是测试结果：

  

 结论：

1. 无论插入还是更新，mssql是王者，更新5000条记录1s内解决。

2. 其他数据库的更新基本都在5s左右，差别不明显

3. 如果使用maridb或mysql等，使用Memory引擎做这种实时缓存是个很好的选择。

4. 在本机测试时，我把mysql57的内存参数调高，但效果不明显。

5. 生产机和我本机测试都是mysql5.7，但生产机的服务器明显不如我的PC机，无论InnoDB还是Memory引擎，未分析具体原因。

6. 对这些临时保存的数据，大量更新时不如全部删除，再插入，耗时只有1/10左右。

7.如果不方便使用mssql的情况下，mariadb是个较好的选择，比mysql和pgsql小巧，功能足够，并且还可使用Memony引擎做些特殊操作。

 

 

插入时代码示例：

//mssql转pgsql15.3
            //插入5000条为4997ms
            Stopwatch sw = new Stopwatch();
            sw.Start();
            string cnstr = @"server=.\sql2016;database=test;uid=sa;password=st#";
            string sql = "select * from aqgl_zy_position2023";
            SqlDataAdapter da = new SqlDataAdapter(sql, cnstr);
            DataTable dt = new DataTable();
            da.Fill(dt);

            foreach (DataRow dr in dt.Rows)
                dr.SetAdded();

            string cnstr1 = @"server=localhost;database=testdb;uid=postgres;password=st#";
             Npgsql.NpgsqlDataAdapter da2 = new Npgsql.NpgsqlDataAdapter(sql, cnstr1);
            Npgsql.NpgsqlCommandBuilder b = new Npgsql.NpgsqlCommandBuilder(da2);
            da2.Update(dt);
            long t1 = sw.ElapsedMilliseconds;
            this.textBox2.Text = t1.ToString();

 

更新时代码：

//UpdateBatchSize=0不支持
            //UpdateBatchSize=1时，513/5465
            //UpdateBatchSize=5001不支持
            Stopwatch sw = new Stopwatch();
            sw.Start();
            string cnstr = @"server=localhost;database=testdb;uid=postgres;password=st";
            string sql = "select * from aqgl_zy_position2023";
            Npgsql.NpgsqlDataAdapter da = new Npgsql.NpgsqlDataAdapter(sql, cnstr);
            DataTable dt = new DataTable();
            da.Fill(dt);
            long t1 = sw.ElapsedMilliseconds;
            foreach (DataRow dr in dt.Rows)
            {
                dr["mx"] = double.Parse(dr["mx"].ToString()) + 1;
                dr["my"] = double.Parse(dr["my"].ToString()) + 1;
            }

            Npgsql.NpgsqlCommandBuilder b = new Npgsql.NpgsqlCommandBuilder(da);
            //da.UpdateBatchSize = 0;
            this.textBox1.Text = da.UpdateBatchSize.ToString() + "/" + dt.Rows.Count;
            da.Update(dt);
            long t2 = sw.ElapsedMilliseconds;

            this.textBox2.Text = t1 + "/" + t2;