asp.net实现Postgresql快速写入/读取大量数据实例

最近因为一些项目需要大量插入数据，研究了下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，最后的时候出了个异常，就是最后一组数据长度不满足条件，无伤大雅。