Which option is faster? Passing a 2D Array

Suppose I was to write code that would dynamically build a 2D array. The module would contain a number of subprocedures called by the main Sub. The subprocedures would populate the output array (using Redim Preserve)

Which of the two options below would run faster/complete first?

1. Pass the array to each subprocedure as a ByRef argument
2. Create a module level variable to hold the array

I doubt there is a significant difference in execution speed between the two.

On another note; ReDim Preserve is expensive (especially with large arrays of Strings or Variants). If possible, calculate the size you need before declaring the array size, or Dimming larger than you will need and Redim Preserve smaller after populating the array.

This link is full of relevant info and tips.
Passing And Returning Arrays With Functions

Originally Posted by AlphaFrog

ReDim Preserve is expensive (especially with large arrays of Strings or Variants). If possible, calculate the size you need before declaring the array size, or Dimming larger than you will need and Redim Preserve smaller after populating the array.

Thanks for the tip AlphaFrog.

I dont think that will be possible unfortunately (I am scraping a website. I know the number of searches before I start but each search will return a varying number of records that I will want to record).

Interesting idea about dimming larger than what I would possibly need - I will have to try that.

Originally Posted by Kyle123

Your bottleneck is using redim preserve (in it's native form anyway).

Good to hear from you again Kyle. I'm not sure I understand what you mean by the native form of Redim Preserve though.

Originally Posted by Kyle123

Pretty much what AlphaFrog said you need to redim preserve larger than you need.

Got it. Thanks

I have started to change the code so that, for each search made, it adds the minimum number of rows possible for that search results. Just this change alone and I have already noticed a significant speed increase. I never knew that Redim Preserve was such a memory hog!

I am eventually going to change this code to the 'redim larger than ever would be needed at the start' but then I will have to work out the most efficient way to scrub all the empty rows.

Originally Posted by Kyle123

Though why not use a collection/dictionary if you don't know the size you need? It's what they're for

Well I guess because I am not familiar with them and also because I read somewhere that they are not a lot faster as they are, so to speak, the equivalent of two arrays (the data and the index)? I could be confused on that point though.

Originally Posted by mc84excel

I am eventually going to change this code to the 'redim larger than ever would be needed at the start' but then I will have to work out the most efficient way to scrub all the empty rows.

Just keep a running count each time you add a row to the large array. Then after all rows have been added, ReDim Preserve to the counter size. Keep in mind you can only ReDim Preserve the last dimension of an array. So your 2D array will have to be transposed e.g; MyArray(columns, rows)

Originally Posted by AlphaFrog

Just keep a running count each time you add a row to the large array. Then after all rows have been added, ReDim Preserve to the counter size.

Good idea! Thanks for that. (Not sure why I didn't think of that as I was intending to add a module level counter anyway to track which row was ready to populate next!)

Originally Posted by AlphaFrog

Keep in mind you can only ReDim Preserve the last dimension of an array. So your 2D array will have to be transposed e.g; MyArray(columns, rows)

Yes. When I populate an array of unknown size, I transpose before I start then transpose back before I output the results. (A long time ago when I was learning arrays, I used to transpose before/after each row I wanted to add. OUCH )

Hmmm. Something went wrong.

The original code used to process ~25-30 searches per minute. After it was pointed out that Redim Preserve is expensive, I reduced the use of it by adding several rows at the start of each search (and then repeated add one for each additional result needed for that search). This change improved the entire execution to ~40 searches per minute. I was so pleased I decided to bite the bullet and reduce the use of Redim Preserve as far as possible (Redim oversize at the start and then one Redim Preserve at the end to delete the unused). After I did that, the speed DROPPED to 15 searches per minute!

It is now slower than what it was originally!!! How is this possible? Is there a point where an oversized array becomes more expensive than using repeated Redim Preserve?

Hard to say without knowing more about the procedure and how you configured things. I don't know what a "search" is within your context. Also, it seems 40 searches per minute is quite slow, but again, don't know what a "search" is.

Perhaps it would be best to to describe what you are doing and to show your code.

Thanks AlphaFrog. I am scraping a website. I know the number of searches before I start but each search will return a varying number of records that I will want to record. I don't want to show my code for this question. I am just curious if there is any theory/'rule of thumb' out there that governs how to best reduce the use of Redim Preserve?

Originally Posted by Kyle123

If you're scrapin, I highly doubt that the redim preserve is the bottle neck, I'd be looking at the server response time

Well I don't have any control over the response times so there is no point getting upset over the aspect that you cant control!

1. BEFORE = ~25-30 searches per minute
2. CHANGE 1 - REDUCE FREQUENCY OF REDIM PRESERVE = ~40 searches per minute
3. CHANGE 2 - DRASTICALLY REDUCE REDIM PRESERVE (through oversized arrays at start) = ~15-20 searches per minute

Unless I am missing something, it appears to me that the Redim Preserve is the main problem. The first code change to Redim Preserve made a major improvement in running time. (I am used to scraping taking a long time so 40 RPM is a decent result for me. The most I am hoping to achieve is 50-60 RPM)

However, after I went further (change 2), everything went downhill. It ended up worse than what it was before. I am trying to figure out why.

My scraping class probably does need an expert to point out amateur mistakes I have made. However I don't see this as a contributing factor to these results as I only changed the Redim Preserve & array aspects.

Originally Posted by Kyle123

If you post your scraping code, I'm happy to take a look

You would?! Hey thanks Kyle. I would be interested in your feedback. Although you will probably laugh or cry when you see the code I wrote/am using! (I have never learnt HTML. I will be doing a course on it soon) To avoid going off this threads topic, I have opened a new thread here: http://www.excelforum.com/excel-prog...rer-class.html

Originally Posted by Kyle123

There are hundreds of reasons that websites respond at inconsistent rates, from rate limiting, to server load through to latency issues. Unless you are doing something really, really funky there is simply no possible way that the changes you propose above (over dimming the array vs redim preserve) could have that impact on performance

I assure you I have improved my scrape performance that much merely by changing the Redim Preserves. This speed improvement owes nothing to inconsistent website rates as I have a large number of measurements going back for over a year for the site I scrape most. The average has been ~25 to 30 RPM for some time now. Changing the Redim Preserves has pushed it to 38-40 RPM (I got this speed back after I reduced the oversized arrays)

So I stand by your "really really funky" escape clause!