I am testing an application that allows a user to export to and save a fairly large worksheet; 2-4 64K row sheets for a total of 100,000-200,000 rows, about 28 columns, all text, with not much formatting/style, only 'set column width. Memory usage when creating this seems kind of high. The application seems to gradually increase memory usage by about 300mb up the time the .Save method is called. At that time, the application consumes another 50MB, for a total of alost 350MB increase in memory usage associated with the excel conversion code . This is a problem as I am in an asp.net environment, with the excel conversion running on the web server, with a pretty high likelyhood that 2-3 users may be simultaneously (3x*350=1.05GB). This in turn can severely tax my 2GB server and cause the IIS to run out of memory. I am using datareader for the source, so the 'read' side is lightweight.
Is this a 'normal' memory usage for 150K row spreadsheet? Any suggestion how I can lower memory consumption? I am almost ready to save to either a .CSV, or switch to a component that simply writes .BIFF format, but I strongly suspect that I will need to do far more formatting at the user requests, hence aspose.cells.. Any other suggestions?
Which component you are using Aspose.Cells for .NET or Aspose.Grid.Web and which version? And, are you importing DataReader to fill the worksheet using Aspose.Cells.Cells.ImportFromDataReader() method to fill an xlsx file sheet? Could you provide more details?
Well, if you fill data up to some huge volume of rows with certain number of columns into a single worksheet, you do need to have a lot of memory to process this big operation. And, if you complete this process, it is also a time consuming job, the performance would be degraded considerably. Moreover, the resultant excel file would be a huge one. For an ASP.net program especially, this does cause problems. By default, IIS takes about 60% of your physical memory already. So, we think, as an alternative option, you can try to split your data to multiple worksheets instead of filling data to a single big sheet. Moreover, you can also try to split your data to create multiple excel files if possible. That would certainly be more efficient and optimize your performance in a great deal. Also, doing so, the out of memory issue won't occur.
We also recommend you to try out the latest version(s) of the product(s).
I am useing excel.cells for .net, tried old version (3.7, which is what the app currently uses), also tried the V4.7 (another app uses that), as 4.7 does seem to use a little less, but not significantly, perhaps 320MB. I am not importing via ImportFromDataReader, as I don't think it splits/continues data on multiple sheets, does it? I read the source database which is SQL Server, using a data reader, so memory impact is minimal on the read side, taking care of adding more sheets when crossing the 64K block boundaries while reading data.
Yes, I am aware of the 60% issue, and that is why I am concerned. Performance is not an issue, it seems to take on the order of 10 - 20 seconds or so to fill a 150K/3 sheets spreadsheet, and that is acceptable. The file is approx 70MB, so I did expect fairly high memory usage, just not 5 times the file size..I can currently ask users to split exports in multiple worksheets or files, but that involves some logistics issues requiring other IT and business groups to be aware of multiple files. It will work for now, but with more business volume we may have to revisit the issue. I am also going to make sure my code relinquishes the memory as soon as possible to minimize impact on other functionality within the web app, but in a longer run I will probably need to come up with some better solution, perhaps offload the process to com+ and serialize access to the process so memory usage would be more deterministic as well as outside of the IIS memory space. If you have any other suggestions, I'd appreciate them.
Thanks, but I am already following all of the above tips. I don;t have a problem with performance, in fact I am pleasantly surprised. 10-15 seconds to populate the 150K row spreadsheet(s) is not a problem. It does take another 10-15 seconds to save the spreadsheet, but it is a 70MB file, so I don’t expect it to be done in a second. As far as memory usage, I have further instrumented the process and I have seen memory usage go as high as 390MB, with a spike during the .save method. At this point, I am just going with the simple ‘tell users not to run export for all data’ approach
Regarding your solution of having multiple worksheets for huge data. Can i create 1 pivot table based on the number of worksheets created? How am i going to do it?
How could you create a pivot table based on multiple sheets as a data source for pivot table in MS Excel? if you can, kindly create a sample pivot table in MS Excel manually and post the excel file here, we will surely look into it.
1) Please use overloaded version of the method i.e…, PivotTables.Add(String[] sourceData, bool isAutoPage, PivotPageFields pageFields, int row , int column, String tableName) to create a pivot table with multiple consolidation ranges for your need.
2) And for the memory usage issue, please try the latest fix 4.8.0.4, I have attached it here for you.