For big data query and export, Data Query and export

For big data query and export, Data Query and export

Last weekend, I helped my friends solve a query and export problem about big data. Here I will record the issue and give it a reference.
Background:

Transformation steps:

Program,

The following section describes the processing code snippets in the program.

Export function, queue + production/consumption mode Processing

/// <Summary> /// execute the export operation /// </summary> /// <param name = "p"> </param> private void ExecExport (string fileName) {plProcessStatus. visible = true; SetControlStatus (true); IExport rpter = new ExcelExporter (); var formater = BuildExportFormater (); bool isQueryEnd = false; // whether the current query ends var templateFieName = Path. combine (Application. startupPath, "Template", "Rpt_CustomerList.xls"); // create a Queue for export <List <OrderDet Ail> exportQueue = new Queue <List <OrderDetail> (); # region Query Thread // process the backend query Thread thQuery = new Thread (() ==>{ int tempTotal = 0; int tempPgIndex = 0; int queryPageSize = 3000; // each query 3 k var tempList = ExecQuery (tempPgIndex, queryPageSize, out tempTotal ); if (tempList! = Null & tempList. count> 0) {lock (locker) {exportQueue. enqueue (tempList); Monitor. pulseAll (locker);} tempPgIndex + = 1; // loop query until query ends while (tempPgIndex * _ pageSize <tempTotal) {var temp_tempList = ExecQuery (tempPgIndex, queryPageSize, out tempTotal); if (temp_tempList! = Null & temp_tempList.Count> 0) {lock (locker) {exportQueue. enqueue (temp_tempList); // Add the query result to the queue Monitor. pulseAll (locker) ;}} tempPgIndex + = 1 ;}} isQueryEnd = true ;}); # endregion # region export the excel Thread // process and write the query result to the file. Thread thExport = new Thread () => {rpter. export (templateFieName, fileName, formater); // read the template and create a new file. int tempRowIndex = 0; while (! IsQueryEnd | exportQueue. count> 0) // if the query is not completed and the queue is not empty, execute the export {if (exportQueue. count> 0) {List <OrderDetail> tempExpotLst = null; lock (locker) {tempExpotLst = exportQueue. dequeue (); // get the queue data and export the excel operation} if (tempExpotLst! = Null & tempExpotLst. count> 0) {formater. detailRowBeginIndex + = tempRowIndex; rpter. exportByAppend (fileName, formater, tempExpotLst); // execute the export operation (append form) tempRowIndex = tempExpotLst. count ;}} else {Thread. sleep (200) ;}// export the image var imgRow = formater. detailRowBeginIndex + tempRowIndex + 8; formater. imageCellFormaters. add (new ImageCellFormater (imgRow, 2, Resources. ywz); rpter. exportByAppend (fileNa Me, formater, null); // export the end Of the recovery button available State btnExport. invoke (new Action () => {plProcessStatus. visible = false; // hide the progress bar SetControlStatus (false); if (MessageBox. show (this, "the data has been exported to [" + fileName + "]. Do you want to open the exported file now? "," Prompt ", MessageBoxButtons. yesNo, MessageBoxIcon. question) = DialogResult. yes) {Process. start (fileName) ;}}) ;}; # endregion thQuery. isBackground = true; thExport. isBackground = true; thQuery. start (); // Start the query thread thExport. start (); // Start the export thread}

Background Data Query Method (based on EF ),

/// <Summary> /// obtain the logistics details /// </summary> /// <param name = "queryParam"> query condition </param> /// <param name = "total"> return the total number of qualified records </param> // <returns> </returns> public List <OrderDetail> GetOrderDetailList (EFQueryParam <OrderDetail> queryParam, out int total) {total = 0; var lst = GetRepository <OrderDetail, Int64> (). get (queryParam, out total ). toList (); // organize other redundant data if (lst! = Null & lst. Count> 0) {// redundant company information for the front-end UI to use var companyList = GetCompanyList (); if (companyList! = Null & companyList. count> 0) {var companyDic = companyList. toDictionary (p => p. id); // convert to a dictionary to Improve the efficiency var tempbgIndex = queryParam. pageIndex * queryParam. pageSize + 1; // generates the sorting lst. forEach (t => {t. index = tempbgIndex; // if (companyDic. containsKey (t. fromCompanyId) {t. fromComoany = companyDic [t. fromCompanyId];} // if (companyDic. containsKey (t. toCompanyId) {t. toCompany = companyDic [t. toCompanyId];} // payment company if (companyDic. containsKey (t. paymentCompanyId) {t. paymentCompany = companyDic [t. paymentCompanyId];} tempbgIndex + = 1;}) ;}} return lst ;} /// <summary> /// obtain Company information /// </summary> /// <returns> </returns> public List <Company> GetCompanyList () {// obtain var lst = ApplicationRuntime from the cache. instance. currentCache. get <List <Company> (KYEConsts. cachesKey. company, () => this. getRepository <Company> (). get (). toList (); return lst ;}

 

Conclusion: after this series of transformations, the performance has been greatly improved. The query response time is <= 1 s, and the export time is <= 8 s. Each time the matching data is about 2 W.
As mentioned in this article
1. EF build a query condition expression and query the database method, see the previous article. http://www.cnblogs.com/xie-zhonglai/archive/2012/04/07/2435903.html
2. Export Excel. This article uses the NPOI component, for details, see the article: http://www.cnblogs.com/xie-zhonglai/p/3979771.html