Hi,

I have an array which I am able to populate the successfully
eg Dim TestArray(900, 5)
which I populate with values of 5 data fields
The programme runs through about 8 million records in about 2-3 hours
(acceptable for me although I suspect it can run faster) and produces a
summary of the unique combinations of the 5 data fields as well as a record
count.

The problem arises when I want to expand it i.e.
I need to add dimension 6 which has about 20 products
I need to add dimension 7 which has 1000 portfolios

Now in my thinking I decide to increase the array size to say
Dim TestArray(3100 , 6)
Programme bombs out
Increase size of array again
Dim TestArray(4500 , 6).
Programme runs sucessfully!

Now to add the 7th dimension
So increase size of array eg
Dim TestArray(16100, 7)
The programme suddenly takes whole day to run and eventually bombs out!

So my clumsy approach to array processing is obviously causing this problem.
What is the approach to processing large arrays efficiently? Surely not this
hit and miss method!
Hope someone can help and shed some light on how to fix this issue since my
VB skill is fairly rudimentary.

Thanks

I understand that you are trying to find unique rows based on seven cells,
right?
Where is the data stored (8 million records)?
Is it possible to sort this data? If yes then you should read a record
compare with the next and so on... It will run very fast
How do you process all records with a 900X5 aray?
Loading all records in an array is not a good idea

"Hilton" wrote:

Hi,

I have an array which I am able to populate the successfully
eg Dim TestArray(900, 5)
which I populate with values of 5 data fields
The programme runs through about 8 million records in about 2-3 hours
(acceptable for me although I suspect it can run faster) and produces a
summary of the unique combinations of the 5 data fields as well as a record
count.

The problem arises when I want to expand it i.e.
I need to add dimension 6 which has about 20 products
I need to add dimension 7 which has 1000 portfolios

Now in my thinking I decide to increase the array size to say
Dim TestArray(3100 , 6)
Programme bombs out
Increase size of array again
Dim TestArray(4500 , 6).
Programme runs sucessfully!

Now to add the 7th dimension
So increase size of array eg
Dim TestArray(16100, 7)
The programme suddenly takes whole day to run and eventually bombs out!

So my clumsy approach to array processing is obviously causing this problem.
What is the approach to processing large arrays efficiently? Surely not this
hit and miss method!
Hope someone can help and shed some light on how to fix this issue since my
VB skill is fairly rudimentary.

Thanks

If you are trying to learn some coding techniques, then ... ok.
If you are trying to get some work done, then put your data in a database.
MS Access or (free) SQL Server Express. Down load SS Express from the
Microsoft site.

I've never tried to put millions of rows into Access and couldn't tell you
how well it'd work, but SQL Server Express will handle that volume with ease.
Instead of hours to generate your summary, think seconds, or perhaps a
minute or two.



"Hilton" wrote:

Hi,

I have an array which I am able to populate the successfully
eg Dim TestArray(900, 5)
which I populate with values of 5 data fields
The programme runs through about 8 million records in about 2-3 hours
(acceptable for me although I suspect it can run faster) and produces a
summary of the unique combinations of the 5 data fields as well as a record
count.

The problem arises when I want to expand it i.e.
I need to add dimension 6 which has about 20 products
I need to add dimension 7 which has 1000 portfolios

Now in my thinking I decide to increase the array size to say
Dim TestArray(3100 , 6)
Programme bombs out
Increase size of array again
Dim TestArray(4500 , 6).
Programme runs sucessfully!

Now to add the 7th dimension
So increase size of array eg
Dim TestArray(16100, 7)
The programme suddenly takes whole day to run and eventually bombs out!

So my clumsy approach to array processing is obviously causing this problem.
What is the approach to processing large arrays efficiently? Surely not this
hit and miss method!
Hope someone can help and shed some light on how to fix this issue since my
VB skill is fairly rudimentary.

Thanks