In C #. net, multithreading and materialized views can be used to improve performance when dealing with big data and database interactions.

Recently, many of the company's project systems have reported performance problems. performance problems are an old and difficult problem. More than web systems, the amount of data that can be carried is limited. Therefore, many of them are due to complicated logic, the data structure is complex and the data size is huge. These problems are often highlighted after the system is used for a period of time. Because the data size is small at first, the performance problem cannot be seen.

The problem is that a company's web system's data export function takes 50 minutes to export data at a time, and sometimes reports problems such as memory overflow and connection timeout. My solution to this problem is multithreading + materialized view.
1. Multithreading, class-based execution reduces the big data and inefficiency caused by one hundred rows of SQL Execution.
2. Materialized View: a common view is a logical statement used in the past. It does not improve performance or create indexes, the materialized view creates snapshots of the Data queried in the view on the database. Like physical tables, the materialized view can create indexes and primary key constraints. This improves performance, however, it has some disadvantages and will be used up. You can set it to be updated automatically at regular intervals or manually. Of course, you can also set it to be updated in a timely manner, but it will slow down the addition, deletion, modification, and query operations of the Base table, here I will only talk about ideas. If you have specific ideas, you can study them on your own.

I have just been developing for two years. I have made some self-Summary and shared them with you. You are welcome to come and make a picture!
The following is some pseudocode:

C #:

/// <Summary> /// Multithreading, retrieve data by category /// </Summary> /// <Param name = "_ filestream"> </param> private void concurrentprocessdata (filestream _ filestream) {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 (Pro Cesspersonalinsure); 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:

-- Create a materialized view. 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 (. required tdate, 1, 10) startdate, min (case when. required tdate <B. required tdate then B. required tdate else n'2014-99-99 'End) as enddate, min (. adjustresult) unitid from psnadjust a left join psnadjust B on. personnelid = B. personnelid where. adjusttype = '2' and B. adjusttype = '2' group by B. personnelid,. into tdate) dt inner join psnaccessioninfo C on C. personid = DT. personid inner join orststdstruct D on D. unitid = DT. unitidorder by employeeid, unitcode, startdate;