How much performance improvement does NPOI 2.0 beta 2 improve?

Author: Tony Qu

Because 2.0 of NPOI 95% beta 2 does not use XmlSerializer for serialization, the experiment shows that the performance improvement is almost 10-20 times. Of course, there are many reasons to kill XmlSerializer. First, XmlSerializer has many restrictions. NPOI is the basic library of NPOI. openXmlFormats cannot generate the assembly of XmlSerializer in advance. This is a performance optimization method officially provided by Microsoft.) The generator directly reports an error saying that there is a conflict. I forgot about the conflict. n conflicts exist, therefore, this path cannot be implemented. In addition, XmlSerializer does not provide sufficient support for some scenarios, such as Xml Object Inheritance and interface support. As a result, the generated open xml file is actually bad, then, Excel or Word directly reports xxx. xml corruption and whether to fix it. In the end, performance and memory consumption naturally occur. When XmlSerilizer is used to generate code for temporary compilation, the memory consumption is terrible, even if there is an assembly cache, in many cases, the first time I was too late, I directly reported an OutOfMemory exception. I have received feedback from multiple users that I have received from both Chinese and foreign countries ).

Let's talk about the number. Let's first look at the feedback from a foreign NPOI user. This shows how much performance has been improved.

class Program{    static void Main(string[] args)    {        IWorkbook workbook = new XSSFWorkbook();        ISheet sheet1 = workbook.CreateSheet("Sheet1");        sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");        int x = 1;        Debug.WriteLine("Start at " + DateTime.Now.ToString());        for (int i = 1; i <= 30000; i++)        {            IRow row = sheet1.CreateRow(i);            for (int j = 0; j < 15; j++)            {                row.CreateCell(j).SetCellValue(x++);            }        }        Debug.WriteLine("End at " + DateTime.Now.ToString());        FileStream sw = File.Create("test.xls");        workbook.Write(sw);        sw.Close();    }}

The running result of NPOI 2.0.1 (beta) is as follows:

Start time: 5/14/2013 5:05:39
[00:00:00. 0170017] 0 rows written
[00:00:02. 7792779] 10000 rows written
[00:00:11. 1951194] 20000 rows written
[00:00:27. 7817779] 30000 rows written
[00:00:53. 5283523] 40000 rows written
[00:01:30. 1910182] 50000 rows written
[00:02:16. 2836270] 60000 rows written
[00:03:14. 6894670] 70000 rows written
[00:04:21. 9641938] 80000 rows written
[00:05:38. 7868753] 90000 rows written
[00:07:05. 4055363] 100000 rows written

As you can see, it takes 7 minutes to write 100,000 rows.

The running result of NPOI 2.0.5 (beta2) is as follows:

Start time: 21:47:55
[00:00:00. 0439453] 0 rows written
[00:00:00. 1054687] 10000 rows written
[00:00:00. 1826171] 20000 rows written
[00:00:00. 2646484] 30000 rows written
[00:00:00. 3271484] 40000 rows written
[00:00:00. 4130859] 50000 rows written
[00:00:00. 4892578] 60000 rows written
[00:00:00. 5498046] 70000 rows written
[00:00:00. 6513671] 80000 rows written
[00:00:00. 7246093] 90000 rows written
[00:00:00. 8037109] 100000 rows written

It can be seen that 2.0.5 is within 1 second even if 100,000 rows are written.

 

Let's look at another example, which is also the feedback from a foreign NPOI.

class Program{    static void Main(string[] args)    {        IWorkbook workbook = new XSSFWorkbook();        ISheet sheet1 = workbook.CreateSheet("Sheet1");        sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");        int x = 1;        Debug.WriteLine("Start at " + DateTime.Now.ToString());        for (int i = 1; i <= 30000; i++)        {            IRow row = sheet1.CreateRow(i);            for (int j = 0; j < 15; j++)            {                row.CreateCell(j).SetCellValue(x++);            }        }        Debug.WriteLine("End at " + DateTime.Now.ToString());        FileStream sw = File.Create("test.xls");        workbook.Write(sw);        sw.Close();    }}

NPOI 2.0.1 (beta) running result

Row 3: 25 seconds

Line 7: 4 minutes

30 W rows: 80 minutes

In the words of the netizen, it is really a growth of geometric series.

NPOI 2.0.5 (beta 2) running result

Row 3: 2 seconds

Line 7: 5 seconds

30 W rows: 37 seconds

You can understand the results. At present, the NPOI 2.0.5 memory is still a little high. Taking the 30 W behavior example, the peak value is about 2.0 GB memory, and the NPOI official version will optimize the memory.