How much memory does a (large) blank array consume?

Question

I don't know enough about memory management (or related testing) or I'd just test this myself;

How much memory does it take (and what is the potential performance hit) to create and hold large amounts of unused space in an array?

Basically, I have to build an array on the fly that might be anywhere from 400 to 40,000 rows (16 columns), and I'm not sure whether I'd risk a performance hit by just dimensioning the array at 40K to begin with, and just ignore anything beyond the area I'm actually using, vs if I maybe dimension it at 1000 rows, and redim (preserve) only as needed each time I hit the next 1000th record.

Thanks!

Keith

Answer 1

There definite overhead in declaring a fixed (that is, not dynamic) array in advance. For example, if you declare a fixed array as Double, then each element you declare in the array, whether you put data in it or not, will take up 8 bytes of memory. Strings and Variants are a little different, but I don't remember the particulars off the top of my head. In any event, ReDim Preserve is a *slow* process for huge arrays, so that is probably not the way to go. If your going to be dynamically changing stuff so often, perhaps it would be bettern not to use an array that you have to keep modifying and simply refer back to the worksheet's range of data directly. I think you should wait for others to jump in here for their advice and experience with the question you raised before locking down on a method to use.

Answer 2

Rick-

Thank you for your reply.

I'll be churning through raw data to evaluate and add each data row one at a time, so I won't know up front how many total rows will be needed; hence I could use redim preserve every 1000 rows, but I don't know how much overhead it takes to redim (which is why I was thinking of doing it no more than once every 1000 rows).

For the sake of time, I was thinking that if there is no significant overhead of the larger array (e.g. if relative performance impact on a standard 2GB memory XP machine wouldn't be noticable by the casual user compared to the redim approach), I'd lean toward just using the large array for the sake of simplicity.

Thanks!

Keith

Answer 3

Why not declare your array as a "dynamic array" and set the size when you know what it will be? To declare an array as dynamic, just leave the parentheses blank when declaring the array...

Dim MyArray() As <whatever>

Then, later when you know how many rows you have data in, you can use the ReDim statement to make it the size you want. For example, if you have 12345 rows, just do this to size the MyArray array...

NumberOfColumns = 16

NumberOfRows = 12345  'this number can come from any calculation

ReDim MyArray(NumberOfColumns, NumberOfRows)

Notice that I put the array element that can vary (rows in this case) as the 2nd element in the array... I did this because ReDim allows you to change the size of the last element (and only the last element) while preserving the data already assigned to the array (using the Preserve keyword) if this should ever be necessary (although large arrays take time to ReDim while preserving existing data, so it is not something you should do very often). Check out ReDim in the help files for more information about it.