這篇文章將為大家詳細(xì)講解有關(guān)asp.net如何實(shí)現(xiàn)Postgresql寫(xiě)入/讀取大量數(shù)據(jù)，小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

成都創(chuàng)新互聯(lián)秉承實(shí)現(xiàn)全網(wǎng)價(jià)值營(yíng)銷(xiāo)的理念，以專業(yè)定制企業(yè)官網(wǎng)，成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站，微信小程序定制開(kāi)發(fā)，網(wǎng)頁(yè)設(shè)計(jì)制作，成都做手機(jī)網(wǎng)站，營(yíng)銷(xiāo)型網(wǎng)站幫助傳統(tǒng)企業(yè)實(shí)現(xiàn)“互聯(lián)網(wǎng)+”轉(zhuǎn)型升級(jí)專業(yè)定制企業(yè)官網(wǎng),公司注重人才、技術(shù)和管理，匯聚了一批優(yōu)秀的互聯(lián)網(wǎng)技術(shù)人才,對(duì)客戶都以感恩的心態(tài)奉獻(xiàn)自己的專業(yè)和所長(zhǎng)。

環(huán)境及測(cè)試

使用.net驅(qū)動(dòng)npgsql連接post數(shù)據(jù)庫(kù)。配置：win10 x64, i5-4590, 16G DDR3, SSD 850EVO.

postgresql 9.6.3，數(shù)據(jù)庫(kù)與數(shù)據(jù)都安裝在SSD上，默認(rèn)配置，無(wú)擴(kuò)展。

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

1. 導(dǎo)入

使用數(shù)據(jù)備份，csv格式導(dǎo)入，文件位于機(jī)械硬盤(pán)上，480MB，數(shù)據(jù)量2500w+。

使用COPY

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

運(yùn)行時(shí)間107s

使用insert

單連接，c# release any cpu 非調(diào)試模式。

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占用率很低，但是跑了一年，程序依然不能結(jié)束，沒(méi)有耐性了...，這么插入不行。

multiline insert

使用multiline插入，一條語(yǔ)句插入約100條數(shù)據(jù)。

var bag = GetData("D:\\user.csv");
//使用時(shí)，直接執(zhí)行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%，磁盤(pán)寫(xiě)入大約45MB/S，感覺(jué)就是在干活，最后時(shí)間217.36s。

改為1000一行的話，CPU占用率提高，但是磁盤(pán)寫(xiě)入平均來(lái)看有所降低，最后時(shí)間160.58s.

prepare語(yǔ)法

prepare語(yǔ)法可以讓postgresql提前規(guī)劃sql，優(yōu)化性能。

使用單行插入 CPU占用率不到25%，磁盤(pán)寫(xiě)入63MB/S左右，但是，使用單行插入的方式，效率沒(méi)有改觀，時(shí)間太長(zhǎng)還是等不來(lái)結(jié)果。

使用多行插入 CPU占用率30%，磁盤(pán)寫(xiě)入50MB/S，最后結(jié)果163.02，最后的時(shí)候出了個(gè)異常，就是最后一組數(shù)據(jù)長(zhǎng)度不滿足條件，無(wú)傷大雅。

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

在前面的基礎(chǔ)上，使用事務(wù)改造。每條語(yǔ)句插入1000條數(shù)據(jù)，每1000條作為一個(gè)事務(wù)，CPU 30%，磁盤(pán)34MB/S，耗時(shí)170.16s。

改成100條一個(gè)事務(wù)，耗時(shí)167.78s。

使用多線程

還在前面的基礎(chǔ)上，使用多線程，每個(gè)線程建立一個(gè)連接，一個(gè)連接處理100條sql語(yǔ)句，每條sql語(yǔ)句插入1000條數(shù)據(jù)，以此種方式進(jìn)行導(dǎo)入。注意，連接字符串可以將maxpoolsize設(shè)置大一些，我機(jī)器上實(shí)測(cè)，不設(shè)置會(huì)報(bào)連接超時(shí)錯(cuò)誤。

CPU占用率上到80%， 磁盤(pán)這里需要注意，由于生成了非常多個(gè)Postgresql server進(jìn)程，不好統(tǒng)計(jì)，累積算上應(yīng)該有小100MB/S，最終時(shí)間，98.18s。

使用TPL，由于Parallel.ForEach返回的結(jié)果沒(méi)有檢查，可能導(dǎo)致時(shí)間不是很準(zhǔn)確（偏小）。

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();

寫(xiě)入方式	耗時(shí)（1000條/行）
COPY	107s
insert	N/A
多行insert	160.58s
prepare多行insert	163.02s
事務(wù)多行insert	170.16s
多連接多行insert	98.18s

2. 寫(xiě)入更新

數(shù)據(jù)實(shí)時(shí)更新，數(shù)量可能繼續(xù)增長(zhǎng)，使用簡(jiǎn)單的insert或者update是不行的，操作使用postgresql 9.5以后支持的新語(yǔ)法。

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

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

在表中既有數(shù)據(jù)2500w+的前提下，重復(fù)往數(shù)據(jù)庫(kù)里面寫(xiě)這些數(shù)據(jù)。這里只做多行插入更新測(cè)試，其他的結(jié)果應(yīng)該差不多。

普通多行插入，耗時(shí)272.15s。
 多線程插入的情況，耗時(shí)362.26s，CPU占用率一度到了100%。猜測(cè)多連接的情況下，更新互鎖導(dǎo)致性能下降。

3. 讀取

Select方法

標(biāo)準(zhǔn)讀取還是用select方法，ADO.NET直接讀取。

使用adapter方式，耗時(shí)135.39s；使用dbreader方式，耗時(shí)71.62s。

Copy方法

postgresql的copy方法提供stdout binary方式，可以指定一條查詢進(jìn)行輸出，耗時(shí)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;
}

結(jié)論

總結(jié)測(cè)試結(jié)果，對(duì)于較多數(shù)據(jù)的情況下，可以得出以下結(jié)論：

1. 向空數(shù)據(jù)表導(dǎo)入或者沒(méi)有重復(fù)數(shù)據(jù)表的導(dǎo)入，優(yōu)先使用COPY語(yǔ)句（為什么有這個(gè)前提詳見(jiàn)P.S.）；

2. 使用一條語(yǔ)句插入多條數(shù)據(jù)的方式能夠大幅度改善插入性能，可以實(shí)驗(yàn)確定最優(yōu)條數(shù)；

3. 使用transaction或者prepare插入，在本場(chǎng)景中優(yōu)化效果不明顯；

4. 使用多連接/多線程操作，速度上有優(yōu)勢(shì)，但是把握不好容易造成資源占用率過(guò)高，連接數(shù)太大也容易影響其他應(yīng)用；

5. 寫(xiě)入更新是postgresql新特性，使用會(huì)造成一定的性能消耗（相對(duì)直接插入）；

6. 讀取數(shù)據(jù)時(shí)，使用COPY語(yǔ)句能夠獲得較好的性能；

7. ado.net dbreader對(duì)象由于不需要fill的過(guò)程，讀取速度也較快（雖然趕不上COPY)，也可優(yōu)先考慮。

P.S.

為什么不用mysql

沒(méi)有好的，只有最合適的，講道理我也是挺喜歡用mysql的。使用postgresql的原因主要在于：

postgresql導(dǎo)入導(dǎo)出的sql指令“copy”直接支持Binary模式到stdin和stdout，如果程序想直接集成，那么用這個(gè)是比較方便的；相比較，mysql的sql語(yǔ)法（load data infile）并不支持到stdin或者stdout，導(dǎo)出可以通過(guò)mysqldump.exe實(shí)現(xiàn)，導(dǎo)入暫時(shí)沒(méi)什么特別好的辦法（mysqlimport或許可以）。
相較于mysql缺點(diǎn)

postgresql使用copy導(dǎo)入的時(shí)候，如果目標(biāo)表已經(jīng)有數(shù)據(jù)，那么在有主鍵約束的表遇到錯(cuò)誤時(shí)，COPY自動(dòng)終止，而且可能導(dǎo)致不完全插入的情況，換言之，是不支持導(dǎo)入的過(guò)程進(jìn)行update操作；mysql的load語(yǔ)法可以顯式指定出錯(cuò)之后的動(dòng)作（IGNORE/REPLACE），不會(huì)打斷導(dǎo)入過(guò)程。

其他

如果需要使用mysql從程序?qū)霐?shù)據(jù)，可以考慮先通過(guò)程序?qū)С龅轿募缓蠼柚募M(jìn)行導(dǎo)入，據(jù)說(shuō)效率也要比insert高出不少。

關(guān)于“asp.net如何實(shí)現(xiàn)Postgresql寫(xiě)入/讀取大量數(shù)據(jù)”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。

標(biāo)題名稱：asp.net如何實(shí)現(xiàn)Postgresql寫(xiě)入/讀取大量數(shù)據(jù)-創(chuàng)新互聯(lián)
文章網(wǎng)址：http://jinyejixie.com/article36/dpojpg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、品牌網(wǎng)站設(shè)計(jì)、動(dòng)態(tài)網(wǎng)站、網(wǎng)站排名、App開(kāi)發(fā)、定制開(kāi)發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)