Slow Performance

Dec 9, 2010 at 8:04 PM

If the content is small (say 200 records), you hardly notice the performance hit. However, even a modest content size (say 3,600 records with 12 fields) is noticeably slow.

I compared a simple HTML flush to the browser [dataset to datagrid (in memory) to rendered HTML] to either saving ClosedXML to disk or saving ClosedXML to a memory stream (and flushing through browser) for the "modest content" data. For the "simple HTML flush" case, the "Open/Save" dialog displayed in less than 2 seconds and the Excel worksheet was displayed within 10 seconds. For the "save ClosedXML to disk" case, the full process required 95 seconds. For the "save ClosedXML to memory stream" case, the "Open/Save" dialog displayed in 164 seconds and it took another 10 seconds to display in Excel.

This lag time gets worse as the content size increases. In my work, displaying 1,000 to 20,000 records in Excel is a common occurence, so this is a major concern.

Do you have any thoughts on improving performance (loading dataset data into a workbook and/or building the OpenXML file)? I love the functionality offered by ClosedXML, but the preformance hit would cause me to use it very selectively versus all the time.

Dec 9, 2010 at 8:32 PM

Sorry about that, I must have introduced something when I modified the code to support opening any Excel '07/'10 file. Before that I could load/save ~70K cells in ~15 secs which is not lightning fast, but not too shabby either.

I'll work on it.

Dec 12, 2010 at 5:52 AM
Edited Dec 13, 2010 at 3:40 AM

Alright, I took a stab at the performance and on my PC (@2.4Ghz) it's able to load a file with 30K cells (random values/datatypes and somewhat random styles) in 4.0 secs, and it saves the file in 3.5 secs.

Please run your tests with v0.35 and let me know your results.

Update: I'm going to move away from Microsoft Open XML SDK and work with the packages manually. A good 40% of the time spent loading and/or saving is related to the SDK and apparently I'm not alone in experiencing poor performance from the SDK.

Dec 13, 2010 at 4:10 PM

First of all, I believe my original benchmark data was distorted by other processes running on our network (i.e. system resource conflicts). When I re-ran the tests in a no-load situation, I got much faster times for ClosedXML and about the same times for the "simple HTML flush" case. In any event, it looks like you were able to enhance performance - about 33% based on my tests. I am able to deliver a stream to the browser in sub-30 seconds with 3,600 records (with 12 fields) - that is acceptable performance in our world. It still does not compare to the speed of the "simple HTML flush", but it is a valid trade-off when you consider the degree of control you have over the output. Thanks for your quick response to my issue!