在C#.Net中，處理大資料與資料庫互動時，可以用多線程+物化視圖來提高效能

              最近公司多家項目系統都會報效能問題，效能問題是個老大難問題，多於Web系統，能承載的資料量是有限的，因此許多都是因為邏輯複雜，資料結構複雜和資料量巨大等原因造成，這些問題往往都是在系統用了一段時間後，才會凸顯，因為起初資料量較小，因此看不出效能問題。

               現在遇到的問題是一家公司web系統的一個資料匯出功能，一次匯出竟然要耗時50幾分鐘，有時還會報記憶體溢出和連線逾時等問題。這個問題我的解決方案是：多線程+物化視圖。
1、多線程：因為以前所有的邏輯都現在一條SQL查詢語句裡，這一條語句是由20幾段邏輯Union ALL起來的，多線程會將其折分，分類別執行，減少過百行SQL一次執行產生的大資料和低效率的問題。
2、物化視圖：以前用的普通的視圖，普通視圖就是一段邏輯語句，對效能沒有任何的提升，也不能建立索引，而物化視圖會把視圖裡查詢出來的資料在資料庫上建立快照，它和物理表一樣，可以建立 索引，主鍵約束等等，效能會有質的提升，但是其有缺點，會佔用，可以設定它定時自動更新一次，也可以手動更新，當然也是可以設定及時更新的，但是會拉慢基表的增刪改查操作，在這裡我只講思路，具體的話大家可以自己去研究。

            我也是剛做了兩年的開發，都是一些自我總結，分享出來與大家共勉，歡迎大家來拍磚！
 下面是部分虛擬碼：

C#：

          /// <summary>/// 運用多線程，分類別擷取資料/// </summary>/// <param name="_fileStream"></param>private void ConcurrentProcessData(FileStream _fileStream){ThreadStart threadStart=new ThreadStart(processBand);Thread threadBand = new Thread(threadStart);threadBand.Start();threadStart=new ThreadStart(processWelfare);Thread threadWelfare = new Thread(threadStart);threadWelfare.Start();threadStart=new ThreadStart(processPersonalInsure);Thread threadPsnInsure = new Thread(threadStart);threadPsnInsure.Start();threadStart=new ThreadStart(processCompanyInsure);Thread threadComInsure = new Thread(threadStart);threadComInsure.Start();threadStart=new ThreadStart(processSalary);Thread threadSalary = new Thread(threadStart);threadSalary.Start();threadStart=new ThreadStart(processPersonalTax);Thread threadPsnTax = new Thread(threadStart);threadPsnTax.Start();while(true){if(threadSalary != null){if(threadSalary.ThreadState == ThreadState.Stopped){//write filethis.IntergrateFile(this.m_basePath + this.SalaryFileName, _fileStream);this.AddLog(201206,DateTime.Now.ToLongTimeString() + "[Write " + SalaryFileName + "]");threadSalary = null;}continue;}if(threadPsnInsure != null){if(threadPsnInsure.ThreadState == ThreadState.Stopped){//write filethis.IntergrateFile(this.m_basePath + this.PsnInsureFileName, _fileStream);this.AddLog(201206,DateTime.Now.ToLongTimeString() + "[Write " + PsnInsureFileName + "]");threadPsnInsure = null;}continue;}if(threadComInsure != null){if(threadComInsure.ThreadState == ThreadState.Stopped){//write filethis.IntergrateFile(this.m_basePath + this.ComInsureFileName, _fileStream);this.AddLog(201206,DateTime.Now.ToLongTimeString() + "[Write " + ComInsureFileName + "]");threadComInsure = null;}continue;}if(threadPsnTax != null){if(threadPsnTax.ThreadState == ThreadState.Stopped){//write filethis.IntergrateFile(this.m_basePath + this.PsnTaxFileName, _fileStream);this.AddLog(201206,DateTime.Now.ToLongTimeString() + "[Write " + PsnTaxFileName + "]");threadPsnTax = null;}continue;}if(threadBand != null){if(threadBand.ThreadState == ThreadState.Stopped){//write filethis.IntergrateFile(this.m_basePath + this.BandFileName, _fileStream);this.AddLog(201206,DateTime.Now.ToLongTimeString() + "[Write " + BandFileName + "]");threadBand = null;}continue;}if(threadWelfare != null){if(threadWelfare.ThreadState == ThreadState.Stopped){//write filethis.IntergrateFile(this.m_basePath + this.WelfareFileName, _fileStream);this.AddLog(201206,DateTime.Now.ToLongTimeString() + "[Write " + WelfareFileName + "]");threadWelfare = null;}continue;}break;}}

SQL:

--建立物化視圖，每天晚上22:00:00自動更新create materialized view VM_PSNPERSONINFO refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')asSELECT dt.personid, c.employeeid,d.unitcode,d.unitname,d.label,       dt.unitid,dt.startdate,dt.enddate  FROM (         SELECT DISTINCT b.personnelid personid,SUBSTR (a.effectdate, 1, 10) startdate,         MIN (CASE WHEN a.effectdate < b.effectdate THEN b.effectdate ELSE N'9999-99-99' END) AS enddate,         MIN (a.adjustresult) unitid         FROM psnadjust a LEFT JOIN psnadjust b         ON a.personnelid = b.personnelid         WHERE a.adjusttype = '2' AND b.adjusttype = '2'         GROUP BY b.personnelid, a.effectdate        ) dt       INNER JOIN psnaccessioninfo c           ON c.personid = dt.personid       INNER JOIN orgstdstruct d           ON d.unitid = dt.unitidORDER BY employeeid, unitcode, startdate;