How to minimize memory usage

Posted by: mikedempsey on 8 September 2017, 1:13 pm EST

  • Posted 8 September 2017, 1:13 pm EST

    We have re-written a VB6/COM application using VB.Net and everything (for the most part) is working well.

    However now that I am looking at resource usage I find that the .Net version of Spread uses about double the memory of the COM version. (Unicode version in both cases)

    We already had an open request to allow the use of larger resultsets (the data is loaded from a database and never edited by the user.) because the COM version capped out at about 2.5 million rows.

    Unless I can reduce the memory requirement the new .Net version will cap out at about half that ... and we are sure to start getting more complaints.

    Can someone provide guidelines on what we can do to minimize memory use in Spread. (Cell Types, ReadOnly, Locked, etc.) 

    Although the user can not directly edit the data we do provide functions to add an additional row to display totals, and we allow them to Sort, Filter, Group etc. - just no UI editing. 


  • Replied 8 September 2017, 1:13 pm EST

    Mike -

    There are several things you can do.  You can use the SuspendLayout and ResumeLayout methods to minimize the painting of the spread when loading the data and setting up styles, etc.  Try and set as many things as you can using the DefaultStyle of the sheet.  If you are bound are you setting up celltypes afterwards or leaving them as the default?  If the former try setting those up on the column or row level rather than the cell level.  If you are using a GeneralCellType make sure you use the Value property when setting values so the text won't have to be parsed.  If you do a search on the WindowsForms section with the keyword 'performance' you will find many posts that discuss these issues.
  • Replied 8 September 2017, 1:13 pm EST

    I think I am doing all these things already.

    I use defaultstyle, set cell types at the column level, and assign to the Value property for General cells. (No data binding at all)

    Performance does not seem to be the problem - it is memory usage that has dramatically increased from the COM version, (Doubled!)
    While that is not a proble for small spreadsheets it will be a major concern for very large data extracts. (2 million plus table rows in some cases)

    I'll take a look at 'performace' listings on the forum, but I did't find any listings refering to 'memory' when I searched before.


  • Replied 8 September 2017, 1:13 pm EST


    The HashTables used to hold the information about the Spread (DataModel, StyleModel, SelectionModel, AxisModel and SpanModel), there can be a lot of memory being used by the Spread. The only way to minimize this use is by storing less objects in these Models. Creating NamedStyles to store share amongst cells in the Style Model is the best way to decrease the amount of memory use. Could you post a small zipped project showing the memory consuption issue you are seeing versus that of the COM version?

  • Replied 8 September 2017, 1:13 pm EST

    The memory usage I am looking at doesn't really have any styles applied I am simply loading the data at this point.

    I set the font at the Default (sheet) level and set all the cell types at the column level.
    The columns are all set to either Text or General (due to problems I found with the Numeric, Currency and Date types) with an occasional Picture type sometimes.

    The user may later apply additional cell types or styles by using my 'Formatting' options but I am looking only at the initial memory usage when I first load the data from the (database) query results.

    My test is to run a query that returns about 1200 rows, 25 columns from the database and look at the additional memory allocated to display the result. (Both using COM and .Net)  The result is a dramatically larger memory increase for the .Net version.


  • Replied 8 September 2017, 1:13 pm EST


    The allocation of memory for a cell between the COM version of Spread and the .NET version will be different. The .NET cell/column/row objects hold more properties then the COM version. Do you have sample projects showing how much more memory you are seeing between the two products that you can post?

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels