189 8069 5689
这篇文章将为大家详细讲解有关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> 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 listbuilder = new List();
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 listbuilder = new List();
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返回的结果没有检查，可能导致时间不是很准确（偏小）。