Data export to excel optimization Summary

The system has been optimized for more than two months, and several data export functions have been optimized. According to the test results, the optimization effect is quite obvious, the execution of functions over a hundred seconds was optimized to within 10 seconds, and even a CSV file was exported to within three seconds. Therefore, some optimization experience can be used for reference, next I will talk about the optimization points I have made for the system I have optimized. I hope it will also provide some reference values for the same optimization personnel as me.

To solve the problem, you must first discover the problem. The first step is to familiarize yourself with every step of the entire function. You can use the breakpoint debugging method to repeat it, then, add the log printing method and the execution time of some cycles and database operation methods to analyze the bottleneck location that causes the overall speed to slow down and find several key analysis points that can be optimized.

The main features I optimized include the following:

1. Check the data transmitted from the client in the server cache (it takes 30 seconds for 20 thousand pieces of data to be transferred from the server to the client ).

2. Export data in a nested loop to an Excel Workbook (more than four seconds for the export of 20 thousand data records ).

3. Change the attribute name of individual fields of each data entry (it takes seven seconds to cycle 20 thousand data entries ).

4. SQL queries the database based on the condition count (it takes more than 60 seconds to query 2 million pieces of data in 20 thousand pieces ).

You can find out the optimization points to analyze these optimization points and find a way to change their implementation methods or add appropriate conditions to improve their execution efficiency, the above four optimization schemes are as follows:

1. It took most of the time for the server to return the result set to the client. Considering the server's performance, I moved the exported client file to the server, the client issues the Export command, and the server returns a client after executing the export file to a folder specified by the server. The client can download and export the file, so that the local area network download speed is very fast, it can reduce the data transmission time by dozens of seconds.

2. Reduce the instantiation of cyclic variables, and improve the performance. The export process of 20000 pieces of data is reduced by one second than that of the original method. The Code is as follows:

Original method:

for (int i = 0; i < subList.size(); i ++) {GenericDO dto = subList.get(i);if (labels != null && labels.size() > 0) {    for (int j = 0; j < labels.size(); j++) {Label2Property label2Property = (Label2Property) labels.get(j);… …

Changed:

int i, j;int lSize = labels.size();int sSize = subList.size();GenericDO dto = null;Label2Property label2Property = null;String properyName = "";for (i = 0; i < sSize; i ++) {dto = subList.get(i);    if (labels != null && lSize > 0) {    for (j = 0; j < lSize; j++) {label2Property = (Label2Property) labels.get(j);… …

3. original scheme: the names of fields in the table header are modified because the fields in the database and the table header are displayed on the interface. For example, the field during_time in the database table corresponds to the field during_time_name in the interface header, when exporting Excel files, you can read the fields in the interface header to obtain the data corresponding to the fields found in the corresponding database. To ensure that the data is obtained, the previous method is to traverse the data items found from the cache, and use setpropertynames (...) The method is changed to the same name as the field in the interface header, so that when the data volume is large, it will consume a lot of time and do a lot of repetitive work.

New Scheme: in order to improve performance, we will not traverse the data, but change the Field Values read from the interface header, because the header has only dozens of fields, which can be read and uploaded to the server in the list, set and change the field value before passing it, even if it is traversed, it will be very fast, without affecting the overall export speed.

4. Because the condition fields in the data table are not indexed, It is very slow to query the entire table based on the condition count. Add an index to the query condition field in the database table. After adding an index to the condition time field, the query time is shortened to one second (the number of indexes in a table must also be considered, it is not easy to have too many indexes in a table ).

To ensure the robustness and Stability of the program, make the implementation code as simple as possible. The nested loop should be used as little as possible, and certain principles should also be followed when using it, there is also a reduction in the number of defined objects. Following the object-oriented principle, there will be many implementation schemes for various features. We should try several schemes as much as possible to find a more suitable method to use.