无为清净楼资源网

asp.net实现Postgresql快速写入/读取大量数据实例
网络编程 2024/11/16 佚名
3 2
无为清净楼资源网 Design By www.qnjia.com

最近因为一些项目需要大量插入数据，研究了下asp.net实现Postgresql快速写入/读取大量数据，所以留个笔记

环境及测试

使用.net驱动npgsql连接post数据库。配置：win10 x64, i5-4590, 16G DDR3, SSD 850EVO.

postgresql 9.6.3，数据库与数据都安装在SSD上，默认配置，无扩展。

CREATE TABLE public.mesh
(
 x integer NOT NULL,
 y integer NOT NULL,
 z integer,
 CONSTRAINT prim PRIMARY KEY (x, y)
)

1. 导入

使用数据备份，csv格式导入，文件位于机械硬盘上，480MB，数据量2500w+。

使用COPY

copy mesh from 'd:/user.csv' csv

运行时间107s

使用insert

单连接，c# release any cpu 非调试模式。

class Program
{
  static void Main(string[] args)
  {
    var list = GetData("D:\\user.csv");
    TimeCalc.LogStartTime();
    using (var sm = new SqlManipulation(@"Strings", SqlType.PostgresQL))
    {
      sm.Init();
      foreach (var n in list)
      {
        sm.ExcuteNonQuery($"insert into mesh(x,y,z) values({n.x},{n.y},{n.z})");
      }
    }
    TimeCalc.ShowTotalDuration();

    Console.ReadKey();
  }

  static List<(int x, int y, int z)> GetData(string filepath)
  {
    List<ValueTuple<int, int, int list = new List<(int, int, int)>();
    foreach (var n in File.ReadLines(filepath))
    {
      string[] x = n.Split(',');
      list.Add((Convert.ToInt32(x[0]), Convert.ToInt32(x[1]), Convert.ToInt32(x[2])));
    }
    return list;
  }
}

Postgresql CPU占用率很低，但是跑了一年，程序依然不能结束，没有耐性了...，这么插入不行。

multiline insert

使用multiline插入，一条语句插入约100条数据。

var bag = GetData("D:\\user.csv");
//使用时，直接执行stringbuilder的tostring方法。
List<StringBuilder> listbuilder = new List<StringBuilder>();
StringBuilder sb = new StringBuilder();
for (int i = 0; i < bag.Count; i++)
{
  if (i % 100 == 0)
  {
    sb = new StringBuilder();
    listbuilder.Add(sb);
    sb.Append("insert into mesh(x,y,z) values");
    sb.Append($"({bag[i].x}, {bag[i].y}, {bag[i].z})");
  }
  else
    sb.Append($",({bag[i].x}, {bag[i].y}, {bag[i].z})");
}

Postgresql CPU占用率差不多27%，磁盘写入大约45MB/S，感觉就是在干活，最后时间217.36s。

改为1000一行的话，CPU占用率提高，但是磁盘写入平均来看有所降低，最后时间160.58s.

prepare语法

prepare语法可以让postgresql提前规划sql，优化性能。

使用单行插入 CPU占用率不到25%，磁盘写入63MB/S左右，但是，使用单行插入的方式，效率没有改观，时间太长还是等不来结果。

使用多行插入 CPU占用率30%，磁盘写入50MB/S，最后结果163.02，最后的时候出了个异常，就是最后一组数据长度不满足条件，无伤大雅。

static void Main(string[] args)
{
  var bag = GetData("D:\\user.csv");
  List<StringBuilder> listbuilder = new List<StringBuilder>();
  StringBuilder sb = new StringBuilder();
  for (int i = 0; i < bag.Count; i++)
  {
    if (i % 1000 == 0)
    {
      sb = new StringBuilder();
      listbuilder.Add(sb);
      //sb.Append("insert into mesh(x,y,z) values");
      sb.Append($"{bag[i].x}, {bag[i].y}, {bag[i].z}");
    }
    else
      sb.Append($",{bag[i].x}, {bag[i].y}, {bag[i].z}");
  }
  StringBuilder sbp = new StringBuilder();
  sbp.Append("PREPARE insertplan (");
  for (int i = 0; i < 1000; i++)
  {
    sbp.Append("int,int,int,");
  }
  sbp.Remove(sbp.Length - 1, 1);
  sbp.Append(") AS INSERT INTO mesh(x, y, z) values");
  for (int i = 0; i < 1000; i++)
  {
    sbp.Append($"(${i*3 + 1},${i* 3 + 2},${i*3+ 3}),");
  }
  sbp.Remove(sbp.Length - 1, 1);
  TimeCalc.LogStartTime();

  using (var sm = new SqlManipulation(@"string", SqlType.PostgresQL))
  {
    sm.Init();
    sm.ExcuteNonQuery(sbp.ToString());
    foreach (var n in listbuilder)
    {
      sm.ExcuteNonQuery($"EXECUTE insertplan({n.ToString()})");
    }
  }
  TimeCalc.ShowTotalDuration();

  Console.ReadKey();
}

使用Transaction

在前面的基础上，使用事务改造。每条语句插入1000条数据，每1000条作为一个事务，CPU 30%，磁盘34MB/S，耗时170.16s。

改成100条一个事务，耗时167.78s。

使用多线程

还在前面的基础上，使用多线程，每个线程建立一个连接，一个连接处理100条sql语句，每条sql语句插入1000条数据，以此种方式进行导入。注意，连接字符串可以将maxpoolsize设置大一些，我机器上实测，不设置会报连接超时错误。

CPU占用率上到80%， 磁盘这里需要注意，由于生成了非常多个Postgresql server进程，不好统计，累积算上应该有小100MB/S，最终时间，98.18s。

使用TPL，由于Parallel.ForEach返回的结果没有检查，可能导致时间不是很准确（偏小）。

var lists = new List<List<string();
var listt = new List<string>();
for (int i = 0; i < listbuilder.Count; i++)
{
  if (i % 1000 == 0)
  {
    listt = new List<string>();
    lists.Add(listt);
  }
  listt.Add(listbuilder[i].ToString());
}
TimeCalc.LogStartTime();
Parallel.ForEach(lists, (x) =>
{
  using (var sm = new SqlManipulation(@";string;MaxPoolSize=1000;", SqlType.PostgresQL))
  {
    sm.Init();
    foreach (var n in x)
    {
      sm.ExcuteNonQuery(n);
    }
  }
});
TimeCalc.ShowTotalDuration();

写入方式 耗时（1000条/行） COPY 107s insert N/A 多行insert 160.58s prepare多行insert 163.02s 事务多行insert 170.16s 多连接多行insert 98.18s

2. 写入更新

数据实时更新，数量可能继续增长，使用简单的insert或者update是不行的，操作使用postgresql 9.5以后支持的新语法。

insert into mesh on conflict (x,y) do update set z = excluded.z

吐槽postgresql这么晚才支持on conflict，mysql早有了...

在表中既有数据2500w+的前提下，重复往数据库里面写这些数据。这里只做多行插入更新测试，其他的结果应该差不多。

普通多行插入，耗时272.15s。
 多线程插入的情况，耗时362.26s，CPU占用率一度到了100%。猜测多连接的情况下，更新互锁导致性能下降。

3. 读取

Select方法

标准读取还是用select方法，ADO.NET直接读取。

使用adapter方式，耗时135.39s；使用dbreader方式，耗时71.62s。

Copy方法

postgresql的copy方法提供stdout binary方式，可以指定一条查询进行输出，耗时53.20s。
public List<(int x, int y, int z)> BulkIQueryNpg()
{
  List<(int, int, int)> dict = new List<(int, int, int)>();
  using (var reader = ((NpgsqlConnection)_conn).BeginBinaryExport("COPY (select x,y,z from mesh) TO STDOUT (FORMAT BINARY)"))
  {
    while (reader.StartRow() != -1)
    {
      var x = reader.Read<int>(NpgsqlDbType.Integer);
      var y = reader.Read<int>(NpgsqlDbType.Integer);
      var z = reader.Read<int>(NpgsqlDbType.Integer);
      dict.Add((x, y, z));
    }
  }
  return dict;
}

结论

总结测试结果，对于较多数据的情况下，可以得出以下结论：

  1. 向空数据表导入或者没有重复数据表的导入，优先使用COPY语句（为什么有这个前提详见P.S.）；
  2. 使用一条语句插入多条数据的方式能够大幅度改善插入性能，可以实验确定最优条数；
  3. 使用transaction或者prepare插入，在本场景中优化效果不明显；
  4. 使用多连接/多线程操作，速度上有优势，但是把握不好容易造成资源占用率过高，连接数太大也容易影响其他应用；
  5. 写入更新是postgresql新特性，使用会造成一定的性能消耗（相对直接插入）；
  6. 读取数据时，使用COPY语句能够获得较好的性能；
  7. ado.net dbreader对象由于不需要fill的过程，读取速度也较快（虽然赶不上COPY)，也可优先考虑。

P.S.

为什么不用mysql

没有最好的，只有最合适的，讲道理我也是挺喜欢用mysql的。使用postgresql的原因主要在于：

postgresql导入导出的sql指令“copy”直接支持Binary模式到stdin和stdout，如果程序想直接集成，那么用这个是比较方便的；相比较，mysql的sql语法（load data infile）并不支持到stdin或者stdout，导出可以通过mysqldump.exe实现，导入暂时没什么特别好的办法（mysqlimport或许可以）。
相较于mysql缺点

postgresql使用copy导入的时候，如果目标表已经有数据，那么在有主键约束的表遇到错误时，COPY自动终止，而且可能导致不完全插入的情况，换言之，是不支持导入的过程进行update操作；mysql的load语法可以显式指定出错之后的动作（IGNORE/REPLACE），不会打断导入过程。

其他

如果需要使用mysql从程序导入数据，可以考虑先通过程序导出到文件，然后借助文件进行导入，据说效率也要比insert高出不少。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。

标签：
asp.net快速读取数据,Postgresql快速读写大量数据
无为清净楼资源网 Design By www.qnjia.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
无为清净楼资源网 Design By www.qnjia.com
www.qnjia.com 无为清净楼资源网
139,976互联网资源
144,792高清电影
21,817无损音乐
631,128技术资源
一句话新闻
苹果官宣WWDC 2024！预计会有大批AI功能 - 2024/11/16

3月27日消息，苹果宣布2024年全球开发者大会（WWDC）将于6月10日至6月14日举行，巧合的是，这次大会与端午假期重合。

苹果官方表示：

在线参加 Apple 每年规模最大的开发者盛会。亲眼见证 Apple 最新平台、技术和工具的发布。了解如何创建和改进你的 App 和游戏。与 Apple 设计师和工程师互动交流，与全球开发者社区建立联系。以上活动均免费在线举行。

探索各种新的工具、框架和功能，助力你打造出理想的 App 和游戏。通过视频讲座学习新技能，与 Apple 专家进行一对一会面，以推进你的项目，完善你的构思。

Swift Student Challenge 旨在支持和鼓舞下一代开发者、创作者和企业家。太平洋时间 3 月 28 日，我们将公布今年的获奖者名单。获奖者将有资格参加在 Apple Park 举办的特别活动。我们还会选出 50 名杰出获胜者，他们将受邀前往库比提诺，获得为期三天的非凡体验，包括参加 Apple Park 的特别活动。

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

三星在GTC上展示了专为下一代游戏GPU设计的GDDR7内存。

首次推出的GDDR7内存模块密度为16GB，每个模块容量为2GB。其速度预设为32 Gbps（PAM3），但也可以降至28 Gbps，以提高产量和初始阶段的整体性能和成本效益。

据三星表示，GDDR7内存的能效将提高20%，同时工作电压仅为1.1V，低于标准的1.2V。通过采用更新的封装材料和优化的电路设计，使得在高速运行时的发热量降低，GDDR7的热阻比GDDR6降低了70%。

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接